CODEWITHSUNDEEP
sql-servert-sql
GurdeepS
GurdeepS

Reputation: 67283

Dynamic SQL (passing table name as parameter)

I want to write a stored proc which will use a parameter, which will be the table name.

E.g:

@tablename << Parameter

SELECT * FROM @tablename

How is this possible?

I wrote this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetAllInterviewQuestions]
@Alias varchar = null
AS
BEGIN
Exec('Select * FROM Table as ' @Alias) 
END

But it says incorrect syntax near @Alias.

Upvotes: 13

Views: 46824

Answers (5)

MartW
MartW

Reputation: 12538

Well, firstly you've omitted the '+' from your string. This way of doing things is far from ideal, but you can do

DECLARE @SQL nvarchar(max)
SELECT @SQL = 'SELECT * FROM ' + QuoteName(@Alias)
Exec(@SQL)

I'd strongly suggest rethinking how you do this, however. Generating Dynamic SQL often leads to SQL Injection vulnerabilities as well as making it harder for SQL Server (and other DBs) to work out the best way to process your query. If you have a stored procedure that can return any table, you're really getting virtually no benefit from it being a stored procedure in the first place as it won't be able to do much in the way of optimizations, and you're largely emasculating the security benefits too.

Upvotes: 21

Greg Miller
Greg Miller

Reputation: 479

You'll have to do it like this: exec('select * from '+@tablename+' where...')

But make sure you fully understand the risks, like SQL injection attacks. In general, you shouldn't ever have to use something like this if the DB is well designed.

Upvotes: 3

Jonathan Leffler
Jonathan Leffler

Reputation: 755010

Most implementations of SQL do not allow you to specify structural elements - table names, column names, order by columns, etc. - via parameters; you have to use dynamic SQL to parameterize those aspects of a query.

However, looking at the SQL, you have:

Exec('SELECT * FROM Table AS ' @Alias)

Surely, this would mean that the code will only ever select from a table called 'Table', and you would need to concatenate the @Alias with it -- and in many SQL dialects, concatenation is indicated by '||':

Exec('SELECT * FROM Table AS ' || @Alias)

This still probably doesn't do what you want - but it might not generate a syntax error when the procedure is created (but it would probably generate an error at runtime).

Upvotes: 0

Ruben
Ruben

Reputation: 15555

Don't you mean

Exec('SELECT * FROM ' + @tableName)

Also, the error you get is because you've forgotten a + before @Alias.

Upvotes: 2

Ned Batchelder
Ned Batchelder

Reputation: 375992

Often, having to parameterize the table name indicates you should re-think your database schema. If you are pulling interview questions from many different tables, it is probably better to create one table with a column distinguishing between the questions in whatever way the different tables would have.

Upvotes: 1

Related Questions