SQL Training - 07

Create function

CREATE FUNCTION GetAge
(
-- Add the parameters for the function here
@DoB as datetime
)
RETURNS int
AS
begin

declare @Age int
select @Age = DATEDIFF(day, @DoB, getdate())
Return @Age
end

to use the function do as the following
select [dbo].[GetAge](convert(date,'19870516'))


Create Stored Procedure

why to use?

• Compiled 
• parsed 
• Security (You don't see the table)

CREATE PROCEDURE getPersonData

-- Add the parameters for the stored procedure here

@id as int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for procedure here

select * from Person

where id=@id

END

GO

[dbo].[getPersonData] 360

or exec [dbo].[getPersonData] 360 

Create delete trigger 

CREATE TRIGGER logDeleteTransact

   ON [dbo].[Person]

  for delete

AS 

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for trigger here

insert into deleted_person(ID, fName, LName)

select deleted.ID, deleted.fName, deleted.LName from deleted

END

GO