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