Menu
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.
543
The best way to pass the dynamic values to a SQL query is by using parameters. In order to use this option, click on "Edit query" in "Execute Query" or "Execute nonquery" activity. Click on the Parameters property in the Input section and pass the parameters.
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL. In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL.
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.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
