sql server - Dynamic SQL (passing table name as parameter)

Question

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.

Answer 1

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

DECLARE @SQL varchar(250)
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.