Creating Temp table by using Stored procedure

Posted by Nagababu under Sql Server on 11/22/2010 | Points: 10 | Views : 7566 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

I need a dataset dynamically with dynamic columns in my SSRS report.
I am passing table dynamically.How to create a temp table.

Please see the below SP .i am getting invalid object name '#temp'.Please help me on this

ALTER PROCEDURE [xyz]
@tablename Varchar(50)
AS
declare @txtsql varchar(8000)
BEGIN

SET NOCOUNT ON;

set @txtsql ='select * from ' + @tablename

INSERT #temp exec(@txtsql)

select * from #temp
drop table #temp

END

Thanks,

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: T.saravanan on: 11/22/2010 [Member] [MVP] Silver | Points: 25

0	Hi NagaBabu, why are you use temp table just small changes in your procedure... ALTER PROCEDURE [xyz] @tablename Varchar(50) AS declare @txtsql nvarchar (8000) BEGIN SET NOCOUNT ON; SET @txtsql ='select * from ' + @tablename exec sp_executesql @txtsql END Try this procedure..... Cheers :) Thanks, T.Saravanan Nagababu, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: PandianS on: 11/23/2010 [Member] [MVP] Silver | Points: 25

0	Hi If you really needed the Temp table then CREATE PROCEDURE [xyz] ( @tablename VARCHAR(50) ) AS BEGIN DECLARE @txtsql VARCHAR(8000) SET NOCOUNT ON; SELECT @txtsql ='SELECT * INTO ##Temp FROM ' + @tablename EXEC (@txtsql) SELECT * FROM ##Temp DROP TABLE ##temp END Or, If you need only dynamic result then CREATE PROCEDURE [xyz] ( @tablename VARCHAR(50) ) AS BEGIN DECLARE @txtsql VARCHAR(8000) SET NOCOUNT ON; SELECT @txtsql ='SELECT * FROM ' + @tablename EXEC(@txtsql) END Cheers Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Nagababu, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

If you really needed the Temp table then

CREATE PROCEDURE [xyz] 

(

@tablename VARCHAR(50) 

)

AS 

BEGIN 

	DECLARE @txtsql VARCHAR(8000) 

	SET NOCOUNT ON; 

	SELECT @txtsql ='SELECT * INTO ##Temp FROM ' + @tablename 

	EXEC (@txtsql)

	SELECT * FROM ##Temp

	DROP TABLE ##temp 

END

Or, If you need only dynamic result then

CREATE PROCEDURE [xyz] 

(

@tablename VARCHAR(50) 

)

AS 

BEGIN 

	DECLARE @txtsql VARCHAR(8000) 

	SET NOCOUNT ON; 

	SELECT @txtsql ='SELECT * FROM ' + @tablename 

	EXEC(@txtsql)

END

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Nagababu, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts