Script to create stored procdures based on @@servername

• Joseph Turney

  Grasshopper

  Points: 22

  More actions

  December 4, 2014 at 1:25 pm

  #295653

  Hi all,

  I am attempting to create a simple script that will create different stored procedures in different environments (DEV,TEST, PROD) based on the server name as in below. This is how they want the code deployed (in one script). The reason: The tables that they derive from have differing field names per environment.

  IF @@SERVERNAME = 'xyz'

  BEGIN

  CREATE PROC dbo.AAA

  AS

  SELECT TOP 10 * FROM Sometable

  END

  IF @@SERVERNAME = 'cde'

  BEGIN

  CREATE PROC dbo.AAA

  AS

  SELECT TOP 10 * FROM Sometable

  END

  getting "Incorrect syntax near the keyword 'PROC'." I guess you can't do this?

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323547

  More actions

  December 4, 2014 at 2:00 pm

  #1762820

  since CREATE PROC/VIEW/FUNCTION/TRIGGER has to be the first statemtn in the batch, you have to go with dynamic SQL

  IF @@SERVERNAME = 'xyz'

  BEGIN

  EXEC('CREATE PROC dbo.AAA

  AS

  SELECT TOP 10 *

  FROM Sometable');

  END

  IF @@SERVERNAME = 'cde'

  BEGIN

  EXEC('CREATE PROC dbo.AAA

  AS

  SELECT TOP 10 *

  FROM Sometable');

  END

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!