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/


Comments

Guest Author: Unhappy Reader03 Oct 2012

You shall also explain the code....



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: