Can I SELECT*FROM a parameter (e.g. @tableName)-CodePudding

I’m trying to optimize a stored procedure and cut down any redundant code. Right now there's a lot code that is duplicated based on the @schema parameter. @Schema can be the test environment or the prod environment.

So very simple example:

IF @schema = ’test’ BEGIN
SELECT*FROM test.dbo.test_results

END ELSE IF @schema = ‘prod’ BEGIN
SELECT*FROM final.dbo.final_results END;

See, same code, the only difference is the table name. Except the code on my project has hundreds of duplicated lines of code with the only difference being the table name.

Therefore, can I set a parameter as @tableName where I can create the following case statement?:

IF @schema = ‘test’ BEGIN
SET @tableName = ‘test.dbo.test_results’


END ELSE IF @schema = ‘prod’ BEGIN
SET @tableName = ‘final.dbo.final_results’

Then have the following code:

SELECT*FROM @tableName

If not, then any other recommendations?

CodePudding user response：

SQL Server does not support macro substitution, but it is possible without Dynamic SQL.

The tables MUST have identical structures, and to be clear... I'm not sure this would be a wise thing to do.

Declare @Schema varchar(50) = 'Prod'

Select * From [test].[dbo].[test_results] Where @Schema = 'Test'
Union All
Select * From [final].[dbo].[final_results] Where @Schema = 'Prod'

CodePudding user response：

I think I would use a different database, especially if there are lots (hundreds you say) of this.

And then switch the connection string.