Stored Procedure instances

1. Select all contents in the table and return a dataset.

Create procedure mysp_all
As
Select * From ajaxcity
Go

2. query based on input parameters and return a dataset

Create procedure mysp_para
@ Cityname varchar (255 ),

@ Short varchar (255)
As
Select * From ajaxcity where cityname = @ cityname and short = @ short
Go

3. Stored Procedure with output parameters (returns the sum of the IDs of the first two Records)

Create procedure mysp_output
@ Sum int output
As
Select @ sum = sum ([ID]) from (select Top 2 * From ajaxcity) as tmptable
Go

4. Use a cursor in the Stored Procedure

Create procedure mysp_cursor
@ Result varchar (255) Output // declare the output variable
As
Declare city_cursor cursor for // declare the cursor variable
Select [ID] From ajaxcity

set @ result = ''
declare @ field int // declare the variable temporarily storing cityid
open city_cursor // open the cursor
fetch next from city_cursor into @ field // assign the actual ID to the variable
while (@ fetch_status = 0) // loop start
begin
If @ result = ''
select @ result = convert (nvarchar (2), count (*)) from ajaxcounty where cityid = @ field
else
select @ result = @ result + ',' + convert (nvarchar (2), count (*)) from ajaxcounty where cityid = @ field
fetch next from city_cursor into @ field // The next cityid
end
close city_cursor // close the cursor
deallocate city_cursor // release cursor reference
go