Dynamic sql statements in stored procedures

[Former Member]

Hi all,

I'm not sure that this is the right forum for this question, but I'll try.

I want to build stored procedure that will receive parameter of varchar type and use it in select statement as table name.

This one doesn't work:

CREATE PROCEDURE [XXX].[GetAllFromTable]

@TableName varchar(30)

AS

BEGIN

SELECT * from @TableName

END

The error is

Must declare the table variable "@TableName".

How can I do it? Is it possible?

Thanks,

Ola

[Former Member · Accepted solution]

create procedure test_sp

@table_name varchar(30)

as

set nocount on

exec('set transaction isolation level read uncommitted select * from '+@table_name)

[Former Member · Accepted solution]

Oh, and FYI .. that's command I just posted it wide open for SQL injection attacks. You may want to write a verification around the variable @table_name before running the query, inside the stored procedure.

[Former Member · Accepted solution]

Thanks, it's work!