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.
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)
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;
