February 24, 2003 at 2:07 pm
My procedure is more complicated than the one below. I'm trying to pass a table name to a procedure, any idea on how I can do that.
-- This doesn't work
CREATE PROCEDURE dbo.test
@tableName varchar(20)
AS
SELECT dbo.@tableName.*
FROM dbo.@tableName
GO
February 24, 2003 at 2:16 pm
Try this out:
create PROCEDURE dbo.test
@tableName varchar(20)
AS
declare @CMD varchar(1000)
set @CMD = 'SELECT dbo. '+ rtrim(@tableName) + '.*' +
' FROM dbo.' + rtrim(@tableName)
exec(@cmd)
GO
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 24, 2003 at 2:23 pm
Thanks Greg, it worked perfect.
February 24, 2003 at 2:26 pm
Greg Larson is right. However, for more information, check out BOL. Find "sp_executesql" in Index, then open the "Using sp_executesql" topic. It explains how to use parameters in your dynamic SQL statement, and why sp_executesql is generally better than the EXECUTE statement.
February 24, 2003 at 2:45 pm
Old habits are hard to break. Here is the same thing using sp_executesql:
create PROCEDURE dbo.test
@tableName varchar(20)
AS
declare @CMD nvarchar(1000)
set @CMD = 'SELECT dbo. '+ rtrim(@tableName) + '.*' +
' FROM dbo.' + rtrim(@tableName)
exec sp_executesql @cmd
GO
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply