CODEWITHSUNDEEP
sqlsql-server
user3839756
user3839756

Reputation: 813

How can I use a variable within a stored procedure

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.

Upvotes: 2

Views: 93

Answers (2)

Thomas
Thomas

Reputation: 29491

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;

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

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)

Upvotes: 6

Related Questions