T-SQL Scripting

Question

I have a SQL script to select and collect some info into temp table and populate data, And now I have a scenario where the table columns are different for different app versions and we use linked server to execute the script to multiple sql instances. so, it fails for specific sql instances with the latest application version which does not have that column.

Any idea on how to update the script to run for, i.e., if it is v1 it the stored procedure should run for that version and for V2 it should run the same procedure but the different script.

For ex: I want this script to run for V1 and I want line 2 for v2 in the same procedure

CREATE PROCEDURE [dbo].[sp_BoardServerTotals]

DELETE FROM BoardServer_Report - v1

DELETE FROM BoardServer - v2

Answer 1

Hi @Sara
How about this:

CREATE PROC Test_Proc  
@app_version varchar(10)  
AS  
IF @app_version = 'v1'   
BEGIN  
DELETE FROM BoardServer_Report   
END  
ELSE IF @app_version = 'v2'   
BEGIN  
DELETE FROM BoardServer  
END  
  
EXEC emp_department @app_version='v1'  

Best regards,
LiHong

Answer 2

You would have to use dynamic SQL for your alternates, or else the procedure will not compile, at least as I understand it.

So:

IF @version = 1
   EXEC('DELETE FROM BoardServer_Report - v1')
ELSE IF @version = 2
  EXEC('DELETE FROM BoardServer - v2')

I fear that this can become quite messy.

Answer 3

You may try to use condition based on the @@SERVERNAME. As a side note, don't use sp_ for your procedures naming convention as many system stored procedures start with sp_. Anything except sp_ should be OK, you may use usp as the prefix, for example, to identify your stored procedures.