Creating Temp table by using Stored procedure

Posted by Nagababu under Sql Server on 11/22/2010 | Points: 10 | Views : 5707 | Status : [Member] | Replies : 2
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,




Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response