Understanding Stored procedure

Understanding Stored procedure

Writing Stored Procedures doesn't have to be hard Normally, you would call a database with a query like:
Select column1, column2 From Table1

To make this into a stored procedure, you simple execute this code:

CREATE PROCEDURE sp_myStoredProcedure
AS
Select column1, column2 From Table1
Go

To execute the stored procedure. You can simply call it by name like this

Exec sp_myStoredProcedure


Let me go for little complex things now

Select column1, column2 From Table1 Where column1 = ‘1’ and column2 = ‘secondthing’
The stored procedure we can write is

CREATE PROCEDURE sp_myStoredProcedure @myInput int , @mysecondInput Varchar(1000)ASSelect column1, column2 From Table1Where column1 = @myInput and column2 = @ mysecondInput
Go

Exec sp_myStoredProcedure ‘1’ , ‘secondthing’

Stored procedure To check whether record exist or not If doesnot exist go for Insertion

CREATE procedure Sp_InsertLogin
@username varchar(100) ,
@password varchar(100)

As
declare @RollbackRequired int
set @RollbackRequired = 0

BEGIN TRANSACTION INSERTLOGIN
declare @count int
select @count = Count(username) from users where username = @username and password = @password
--If record doesnot exist go for inserting it.
if (@count = 0)
Insert into users (username , password , Active ,IsAdmin)values (@username , @password , '0','0')
--Check for error, If error occurred go for rollback
if @@Error <> 0
begin
set @RollbackRequired = 1
goto End_Transaction
end
End_Transaction:
IF @RollbackRequired = 1
BEGIN
ROLLBACK TRAN INSERTLOGIN
RETURN -1
END
ELSE
BEGIN
COMMIT TRAN INSERTLOGIN
RETURN 0
END

To execure this call

Exec InsertLogin ‘raj’ , ‘password’



About CURSORS

CURSORS are very similar to an ADO recordset

I chose the FAST_FORWARD option to optimize performance because it creates a forward only, read only cursor
Be aware that CURSORS are somewhat resource intensive. So use it when necessary but always look for a better way.
Also remember to CLOSE and DEALLOCATE your CURSORS as soon as possible to free up resources. Here's the code sample:

To display Course detail and course id records from coursedetail tabel

Create procedure Sp_CourseDetail
As
declare @cname varchar(50) , @cid varchar(50)
declare @mycursor cursor
set @mycursor = CURSOR FAST_FORWARD

for
select coursename , courseId from coursedetail

Open @mycursor
fetch next from @mycursor into
@cname , @cid

while @@fetch_status = 0
begin
print @cname
print @cid
fetch next from @mycursor into
@cname , @cid
end

close @mycursor
deallocate @mycursor

Go

exec Sp_CourseDetail


C-Sharp Code sample to execute Storred procedure


conn.ConnectionString = GetConnection() ;
conn.Open() ;
strSql = "Exec InsertLogin ‘raj’ , ‘password’ " ;
SqlCommand cmd = new SqlCommand(strSql ,conn) ;
cmd.ExecuteNonQuery() ;