Stored procedure with CTE
in this article i will go to explain how to use cte in a stored procedure.i referred lot of website.but i don't get it in my training period.see the below example.i tried to use cte in storedprocedure.here i used cte in storedprocedure to split up the given number in a table.
in this article i will go to explain how to use cte in a stored procedure.i referred lot of website.but i dont get it in my training period.see the below example.
I thing recursive cte is very useful other than while loop.
First Create splitnumber table.create table splitnumber(id int,value int)
insert the values
insert into splitnumber values(1,128)
Now i created the stored procedure to split the number using cte.create proc CteWithStoredprocedure
@id int
as
begin
;WITH CTE AS
(
SELECT ID,VALUE,LOWNUMBER=(V.NUMBER-1)*10+1,HIGHNUMBER=V.NUMBER*10,dates=dateadd(d,NUMBER,getdate()) FROM SPLITNUMBER INNER JOIN MASTER..SPT_VALUES V ON V.TYPE='P' AND V.NUMBER BETWEEN 1 AND VALUE/10+1 where splitnumber.id=@id
)
SELECT RANGE=CAST(CASE WHEN HIGHNUMBER=1 THEN 0 ELSE LOWNUMBER END AS VARCHAR)+'-'+
CAST(CASE WHEN LOWNUMBER< value
THEN HIGHNUMBER
ELSE
VALUE
END AS VARCHAR)FROM CTE
end
output is:
1-10
11-20
.
.
.
121-130
Thank you For reading my articles.
if you have any queries please contact me in below mail and say your valuable comments here also.
Thank you.
Reference: http://rathin813.blogspot.com/
You shall also explain the code....