procedures with example for insertion,selection

/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* Place your Query here */
RETURN

Example 1:
Create PROCEDURE dbo.GetAllEmployees
As

Select * from Employee

Execution query :

exec GetAllEmployees

Example 2:
ALTER PROCEDURE dbo.GetEmployee
@empid int
As

Select * from Employee where Employee.EmployeeID=@empid

Execution Query :

exec dbo.GetEmployee 1

Example 3:

Create PROCEDURE dbo.UpdateEmpNationalID

(
@empid int
,@newcontactid int
,@oldcontactno int OUTPUT
)

AS

Select @oldcontactno=[ContactID]
FROM [dbo].[Employee] where [Employee].EmployeeID=@empid

if(@oldcontactno=@newcontactid)
begin

RETURN 0

end

else
begin

update Employee set ContactID=@newcontactid where Employee.EmployeeID=@empid

return 1
end

Execution Query:
Declare @oldcontactno int
Declare @returnvalue int
exec @returnvalue=UpdateEmpNationalID 1,123,@oldcontactno output

Select @oldcontactno 'Old Contact'
Select @returnvalue 'Returned value'

Premalatha
Software Engineer

CREATE PROCEDURE rsp_authuser
@uname as varchar(20),
@pword as varchar(21) OUTPUT,
@profile as varchar(21)OUTPUT
AS
SELECT @pword = password,@profile = profile FROM authentication WHERE
username = @uname AND active = 'yes'
GO


This stored procedure can then be executed from a *.aut file as follows:

SQL= {call rsp_authuser (%username!i, %password!o, %profile!o)}
[results]
; No entries should be specified in results, everything but the header should be
commented out.

Premalatha
Software Engineer

http://www.simple-talk.com/sql/learn-sql-server/working-with-the-insert-statement-in-sql-server/

Premalatha
Software Engineer