0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ana Yanez
  • 1
  • 1
    You need dynamic SQL for that. Note your funny single quotes aren't valid syntax. – Dale K Mar 16 '22 at 22:02
  • 1
    Does this answer your question? [A table name as a variable](https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable) – GSerg Mar 16 '22 at 23:18
  • 1
    I'll point out that your variable name `@schema` is VERY misleading as you switch databases (not schemas) based on the value. Think very carefully about your approach. In general it is better to avoid running a test database in the same server instance as a production database. And why would application connected to one database need to "dynamically" select rows from a very different database? Three-part names are a bad idea - multiple three-part names are even worse. – SMor Mar 16 '22 at 23:23

2 Answers2

2

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'
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1

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

And then switch the connection string.

JohnnyJP
  • 1,216
  • 9
  • 18