May 30, 2006 at 1:08 pm
Hello,
I would like to create a stored procedure in which I can give a tablename as a variable.
For example I would like to run the following command:
CREATE PROCEDURE [dbo].[sp_CreateOutTable]
@TableName varchar(50)
AS
BEGIN
SELECT * FROM @TableName
END
However this will give an error. Any ideas??
Thanks,
Erik
May 30, 2006 at 1:13 pm
Hi Erik,
You would need to run that as dynamic SQL as follows:
Dim @CmdStr as varchar(200)
SET @CmdStr = 'select * from ' + @TableName
exec sp_executesq @CmdStr
By the way, you'll lose the security benefits that you get with using stored procedures and the stored procedure execution plan won't count for anything because of the nature of the execution statement being dynamic.
Hope that helps.
May 30, 2006 at 2:04 pm
Hello Karl,
Thanks for your reply. I don't get you completely. Is sp_executesq an existing stored procedure? I think not. How do I 'run' the command in @CmdStr?
Erik
May 30, 2006 at 2:10 pm
"How do I 'run' the command in @CmdStr"
You run the SQL code in @CmdStr just as Karl has shown. sp_executeSQL is an extended stored procedure in the Master DB. Look up sp_executeSQL in BOL.
May 31, 2006 at 6:08 am
I use this quite alot...
-----------------------------------------
CREATE PROCEDURE [dbo].[sp_CreateOutTable]
@TableName varchar(50)
AS
DECLARE @SQL varchar (255)
SET @SQL = 'SELECT * FROM '+ @TableName
EXEC (@SQL)
------------------------------------------
Works everytime...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply