T-SQL Scripting

Question

question

Sara925 asked Mar 4, '22 LiHongMSFT-3908 answered Mar 8, '22

T-SQL Scripting

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

sql-server-general sql-server-transact-sql

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 · Mar 04 at 08:34 AM

Your question is very vague and it's to less on information; please provide more details.

0 ·

Answer 1 · 2022-03-04T14:53:40.907Z

NaomiNNN answered Mar 4, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-03-04T23:01:44.63Z

ErlandSommarskog answered Mar 4, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-03-08T02:45:34.383Z

LiHongMSFT-3908 answered Mar 8, '22

Hi @Sara925
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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question

T-SQL Scripting

3 Answers

question details

question

T-SQL Scripting

3 Answers

question details

Related Questions