RSS

How to Create Temp Table in StoreProcedure

07 Jun

 

create PROCEDURE [dbo].[stp_ShowHierarchy_WithTempTable]

@documentid as int

AS

BEGIN

SET NOCOUNT ON;

 

 

declare   @testCat TABLE (

ID INT,

SubCategoryOf INT,

Name VARCHAR(50),

Level INT,

PathText VARCHAR(100),

Path VARCHAR(100)

)

 

INSERT INTO @testCat

SELECT sectionID,case parentSectionID when 0 then Null else parentSectionID end , sectionTitle, NULL, NULL, NULL  FROM Test

WHERE documentid=@documentid

 

–select * from @testcat

DECLARE @cnt INT

DECLARE @ctr INT

 

SELECT @cnt = COUNT(*) FROM @testcat

UPDATE @testCat

SET Level = 1, PathText = Name, Path = CAST(ID AS VARCHAR(10))

WHERE SubCategoryOf IS NULL

SET @ctr = 2

WHILE @ctr <= @cnt BEGIN

UPDATE @testCat

SET PathText = REPLICATE(‘-‘, @ctr) + Name,

Level = @ctr WHERE SubCategoryOf IN (

SELECT ID from @testCat

WHERE Level = @ctr – 1)

 

SET @ctr = @ctr + 1

END

 

SET @ctr = 1

SELECT @cnt = COUNT(*) FROM @testcat

DECLARE @subcatof int

WHILE @ctr <= @cnt BEGIN

SELECT @subcatof = SubCategoryOf

FROM @testCat WHERE ID = @ctr

UPDATE @testCat

SET Path = (SELECT COALESCE(Path, CAST(@subcatof AS VARCHAR(10))) FROM @testCat WHERE ID = @subcatof) + ‘.’ + CAST(ID as VARCHAR(10))

WHERE ID = @ctr AND Level > 1

SET @ctr = @ctr + 1

END

–select * from @testcat

–ORDER BY level,path

———

select * from @testcat

ORDER BY ID

–ORDER BY level,SubCategoryOf,pathtext

 

END

 

 

 

 

Advertisements
 
1 Comment

Posted by on June 7, 2011 in SQL Query

 

Tags: ,

One response to “How to Create Temp Table in StoreProcedure

  1. sunny kumar rana

    June 27, 2011 at 5:44 am

    example:-
    Create table #testCat TABLE ( )

    Just using #front of the table name and procced same as mention above.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: