Stored Proc Query 2

[phzero]

Hi All,

I've written a stored proc that accepts an argument. When I reference the argument in the WHERE section it works. Now, I've tried to write another proc that accepts the name of the table to query, but it does not want to work. How can I pass a string variable to a stored proc and use the argument in the FROM clause. Here is my procedure:

CREATE PROCEDURE sp_GetVolumesPerAccount (@TableName as char(20))
AS
SELECT * from @TableName

The error I get is "Incorrect Syntax near '@TableName'"
Any help will be appreciated. Thanks a lot.

 

[sguslan]

CREATE PROCEDURE sp_GetVolumesPerAccount (@TableName as char(20))
AS
declare @SqlCommand varchar(100)
set @SqlCommand = 'SELECT * from ' + @TableName

exec (@SqlCommand)

go
________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[phzero]

Hi sguslan,

Thanks a stack for the solution. Your prompt response is much appreciated. Thanks again and have a great day.
Cheers,

 

[GrSw]

Try this:

CREATE PROCEDURE sp_GetVolumesPerAccount (@TableName as char(20))
AS
EXEC ('SELECT * from ' + @TableName)

Hope this helps
Grzegorz

 

[phzero]

What can I say, with so many usefull and helpfull people around, I think the politians of this world should take tips from this site, The Perfect Community, in my opinion, and for that, I'm giving each of you a Star, because, simply put, that's what you guys are.

Thanks a million times.