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
