Being able to change table name in select query

[lidds]

I have a stored procedure that I need to be able to change the table name that the select statement in the stored procedure uses depending on a user input, however I am having a problem with the code:

CREATE PROCEDURE [dbo].[spQryParentComments] @projTable as varchar(50)
AS
SELECT FROM @newProjTable WHERE (commCommited = '1' or commCommited = '2') 
GO

@projTable is passed from a form in my application

Thanks in advance

Simon

 

[jmcilhinney]

You can't pass identifiers as parameters. Only values can be passed as parameters. It's quite logical because VB works the same way. If you call a method you cannot pass the name of an object into the method and have it become the object.

The way around this is, within your sproc, you build a string and concatenate the table name into it. You then pass that string to the appropriate system stored procedure and it will execute it as SQL code. In SQL Server 2005 that sproc is sp_sqlexec.

 

[lidds]

So would I do something like the following:

declare @strSQL as string
@strSQL = "SELECT FROM '" + @newProjTable + '"WHERE (commCommited = '1' or commCommited = '2') "
exec(@strSQL)

Thanks in advance

Simon

 

[jmcilhinney]

That seems to be a mongrel hybrid of SQL and VB. You need to write a stored procedure that takes a table name as a parameter. In that stored procedure you need to insert that table name into a string containing a SQL statement and then pass that string to the sp_sqlexec procedure to be executed.

You must be very careful doing this this way though, because you're opening yourself up to SQL injection attacks. You should probably validate the table name first to make sure it IS the name of a table.

 

[jmcilhinney]

Actually, I think sp_sqlexec may be old. Try sp_executesql.

sp_executesql (Transact-SQL)

 

[cjard]

So would I do something like the following:

Can I ask.. Why you would do something like that?