How can I use a variable within a stored procedure

Question

I am just wondering if it is possible to use a variable within a stored procedure. For Example:

    CASE    @productType
    WHEN 1 THEN
    DECLARE @table = ‘products1’
    WHEN 2 THEN
    DECLARE @table = ‘products2’
END

SELECT * FROM @table;

Obviously this does not work, just wondering if there is a way to accomplish this.

score 6 · Accepted Answer · answered Dec 22 '15 at 01:17

Yes, there is a way. You can use dynamic sql.

DECLARE @productType INT = 1
DECLARE @table  NVARCHAR(MAX)
DECLARE @sql    NVARCHAR(MAX)

SELECT @table = 
    CASE @productType
        WHEN 1 THEN 'products1'
        WHEN 2 THEN 'products2'
    END

SELECT @sql = 'SELECT * FROM ' + QUOTENAME(@table);

EXEC(@sql)

Thomas · Answer 2 · 2015-12-22T19:26:47.537

2

You can simply use a if else if statement :

DECLARE @productType INT = 2
IF @productType = 1
    SELECT * FROM products1
ELSE IF @productType = 2
    SELECT * FROM products2

Or Using sp_executesql:

DECLARE @productType INT = 1;
DECLARE @table  NVARCHAR(MAX);
DECLARE @sql    NVARCHAR(MAX);


SELECT @table = 
    CASE @productType
        WHEN 1 THEN 'products1'
        WHEN 2 THEN 'products2'
    END;

SELECT @sql = 'SELECT * FROM ' + QUOTENAME(@table);

EXEC sp_executesql @sql;

edited Dec 22 '15 at 19:26

answered Dec 22 '15 at 01:21

Thomas

24,234
6
81
125

Yes, This was a simple example but I am wanting to use this on much more complex procedures where using this method would be unwieldily – user3839756 Dec 22 '15 at 01:28
Can you provide an explantion of your scenario, much of the time it's better to avoid dynamic SQL – Thomas Dec 22 '15 at 01:35
I see, Is this for performance reasons? the scenario is basically because I have a very long statement where the table name must be changed throughout. there are many scenarios so i would have to have 20+ if statements and copy paste a lot of code – user3839756 Dec 22 '15 at 03:48
No this is for security reasons, in your case you should use EXEC sp_executesql @sql rather than EXEC (@sql). http://stackoverflow.com/questions/548090/dynamic-sql-execsql-versus-exec-sp-executesqlsql – Thomas Dec 22 '15 at 04:05

How can I use a variable within a stored procedure

2 Answers2