June 27, 2003 at 11:17 am
hi All,
does anyone no how to get data from a tablename to be used by a SP @ runtime.
conceptually what i want to do is:
create proc myproc (@temptable varchar(50))
select * from @temptable
i've tried a number of variations on:
create proc myproc (@temptable varchar(50))
declare @sql varchar(8000)
declare @result varchar(8000)
set @sql = 'select @result=column1 from '+@temptable
exec (@sql)
What i'm up against is that i can pass the name of my table to the SP but i can't seem to get the data out of my table.
any ideas?
what i will ultimately do if necessary is to consolidate these individual tables and distinguish the different elements by additional column entries.
But i remain perplexed as to how i can get the data out of a table which is passed as a parameter.
thanx in advance of any ideas or suggestions.
June 27, 2003 at 11:45 am
If the @result datatype is known, you can do something like:
SET @sql = N'SELECT @result = column1 from ' + @temptable,
@paramDef = N'@result INT OUTPUT'
exec sp_executesql @sql, @paramDef, @result = @result OUTPUT
You might have to play with it - this is not tested. I have used it for dynamic stored procedures, but not select statements.
Guarddata-
June 27, 2003 at 2:38 pm
The above doesn't quite work as published, thanx for your effort.
June 27, 2003 at 2:59 pm
OK - in your example it appears that you know what column1 is...for the sake of this example, we will call it VARCHAR(50)
CREATE PROCEDURE myProc ( @tempTable VARCHAR(50) ) AS
DECLARE @colReslt VARCHAR(50), @sql NVARCHAR(1000), @paramDef NVARCHAR(1000)
SELECT @sql = N'SELECT @colReslt = Column1 FROM ' + @tempTable,
@paramDef = N'@colReslt VARCHAR(50) OUTPUT'
EXEC sp_executesql @sql, @paramDef, @colReslt = @colReslt OUTPUT
SELECT @colReslt 'Column1'
GO
At least this works in my test unless I am missing something obvious.
Guarddata-
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply