Group of SQL statements that have been previously created and stored in the server database is called Stored Procedure (SP). Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance..
Here I provide some samples of sp where different sql function used. Every sample is following specific case and situation.
This is the table structure which is used in stored procedure.
combopackid -- comboid --- productid
1 ------------ 2000 ----------- 2
2 ----------- 2000 ---------- 94
3 ----------- 523129745 ---- 2
4 ----------- 523129745 --- 523129679
5 ----------- 523129745 --- 94
Requirement – On the basis of productid took all of the combination (comboid) where that productid is belonging but not display. For example according to previous table data display data on the basis of productid 2, so the result should be like
combopackid -- comboid ---- productid
2 ------------ 2000 ----------- 94
4 ---------- 523129745 ----- 523129679
5 ---------- 523129745 --- 94
Two comboid is belonging to productid 2, one is 2000 and another is 523129745. now here shown all rows which comboid is 523129745 or 2000 but that rows whose productis is 2, is not shown.
SP for that case –
Create procedure proc_mpcombo
(
@pid int
)
as
declare @fullQ varchar(500)
set @fullQ = 'select * from mpcombopack where('
declare @strQ varchar(200)
set @strQ = ''
declare cur_combo cursor
for
select comboid from mpcombopack where productid=@pid
open cur_combo
declare @comboid int
fetch next from cur_combo into @comboid
while @@fetch_status=0
begin
set @strQ = @strQ + ' comboid=' + convert(varchar(10),@comboid) + ' or'
fetch next from cur_combo into @comboid
end
set @strQ = stuff(@strQ,len(@strQ)+1-charindex(' ',ltrim(reverse(@strQ))),4,'')
--print @strQ
--print @fullQ+@strQ+') and productid != ' + convert(varchar(10),@pid)
declare @finalQ varchar(500)
set @finalQ=@fullQ+@strQ+') and productid != ' + convert(varchar(10),@pid)
execute(@finalQ)
close cur_combo
deallocate cur_combo
Here I use STUFF function for replace group of characters like ‘ or’ with the help of function LEN and CHARINDEX.
This SP need one input parameter and return result set on the basis of that parameter.
Suggestions are always welcome.
Thanks.
