jeudi 6 août 2015

SQL variables and the IF statement

I am using T-SQL in SQLCMD mode in SSMS to run various subfile from one file, and the principle file (princ.sql) is where I need to declared and set a variable @MyInteger and run a subfile:

DECLARE @MyInteger INT
SET @MyInteger = 20

:r $(path)\"query.sql"
:r $(path)\"uses Myingteger too.sql" 

This works fine; as a minimal example, consider query.sql and 'uses MyInteger too.sql' to have the same one line

SELECT @MyInteger

and i do get back two instances of 20.

My trouble is that I would like to be able to run query.sql on its own using a locally defined variable @SubMyInteger. My ideal situation for query.sql is

IF @MyInteger is declared 
    SET @SubMyInteger = @MyInteger
ELSE 
    SET @SubMyInteger = 90

SELECT @SubMyInteger

Then when I run query.sql, @MyInteger would not have been declared, so @SubMyInteger would be assigned 90, and 90 would be returned. I would also get back two 20s as before if i ran princ.sql

For the IF statement, I have so far tried two things:

IF OBJECT_ID('@MyInteger') IS NOT NULL

and

IF @MyInteger IS NOT NULL

I get the error

Must declare scalar variable "@MyInteger".

If I try declaring @MyInteger and then run princ.sql, I get the error

The variable '@MyInteger' has already been declared.

even if the declare statement is conditioned with an IF.

Is there a way to achieve what I want here?

Thanks

Aucun commentaire:

Enregistrer un commentaire