CODEWITHSUNDEEP
sql-serverstored-procedures
Catatron 5
Catatron 5

Reputation: 80

Creating a Stored Procedure despite errors (to transfer to different server)

I've got a database that is replicated on two servers, a live server and a test server, so that whenever it's needed the 'test' database gets overwritten by the 'live' database (so that I can reset everything if I've made a mess.) I want an Stored Procedure in the 'test' database, that will only run in the 'test' database, but to do this I need to have it in the 'live' database as well, so that it can be copied over when 'test' is overwritten. The procedure starts:

if @@SERVERNAME<>'TEST'
begin
    raiserror ('NOT ON TEST! This SP must only be run on TEST.',16,1)
    return 
end

So that if it runs in live, it immediately exits.

Unfortunately the "Live" database server uses an older version of SQL, and doesn't seem to understand the lead/lag/over statements in the script, and refuses to create the procedure because of these "Incorrect syntax" errors.

The SP definitely works in the test server. Is there a way to disregard the error messages when creating a stored procedure?

I've found a prior question that explained how to make a stored procedure with the same name, but I need the stored procedure to contain the script that the server thinks is incorrect.

Upvotes: 1

Views: 505

Answers (1)

Raul
Raul

Reputation: 3131

The only way to not get the stored-procedure validated when created, is to run a dynamic-sql query within.

Example:

CREATE PROCEDURE dbo.YourStoredProcedure AS 
BEGIN 

IF @@SERVERNAME<>'TEST'
BEGIN
    RAISERROR ('NOT ON TEST! This SP must only be run on TEST.',16,1)
    RETURN
END

DECLARE @SQL NVARCHAR = N'
SELECT rowOne
, rowTwo
, valueOne
, LEAD(valueOne) OVER (PARTITION BY rowOne ORDER BY rowTwo DESC) AS preValue
FROM dbo.YourTable
'

EXEC(@SQL)

END

Notes:

  • On the long term, try to find a better strategy than DB replication for different systems. Check Continuous Deployment
  • Make sure to check concatenated dynamic-sql for potential issues (sql injection). Check QUOTENAME()

Upvotes: 3

Related Questions