Must delare the variable?
-
I'm simplifying an example of a problem I'm having. I'm getting the error, must declare the variable. This is probably a stupid looking example but if I can get this to work, I'm thinking I'll be able to get my real query to work. Thanks!!
DECLARE @UseIt1 as varchar(100)
EXEC ('SELECT @UseIt1 = A.AcctNo ' +
'FROM QueueOrderExcessDol A ' +
' INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
INNER JOIN QueueComboBox D
ON B.WorkQueue = D.ProgramKeyField
WHERE B.Tagged = 0
AND D.ProgramKeyField = @WorkQueue ' +
' ORDER BY RiskId')
-
try like this
DECLARE @UseIt1 varchar(100),
@WorkQueue varchar(100)
EXEC ('SELECT A.AcctNo ' +
'FROM QueueOrderExcessDol A ' +
' INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
INNER JOIN QueueComboBox D
ON B.WorkQueue = D.ProgramKeyField
WHERE B.Tagged = 0
AND D.ProgramKeyField ='''+ @WorkQueue +''' ORDER BY RiskId')
-
Well in the context of your EXEC(), the variable @WorkQueue is not declared. If you've declared it earlier you'll need to either parse it in or pass it in via sp_executesql (I'd recommend you use the latter as it's the proper way to do this).
-
Yikes, I'm getting tired. I made a simple example but posted the wrong one. Here is the simple example.
DECLARE @UseIt1 as varchar(100)
EXEC ('SELECT @UseIt1 = 1')
-
You'd have to use dynamic sql for this...also, your select should be a "set"..
DECLARE @UseIt1 as varchar(100) EXEC ('SET ' + @UseIt1 + ' = 1')
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila,
You're having one of those days like I did on the trigger thing...
DECLARE @UseIt1 as varchar(100)
EXEC ('SET ' + @UseIt1 + ' = 1')
PRINT @UseIt1
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SwTrans,
I guess I don't see what your problem is or what you're actually trying to do... why wouldn't the following work for you?
DECLARE @UseIt1 as varchar(100)
DECLARE @WorkQueue AS VARCHAR(100)
SET @WorkQueue = somestringvalue
SELECT @UseIt1 = A.AcctNo
FROM QueueOrderExcessDol A
INNER JOIN _FinalFlat B
ON A.AcctNo = B.AcctNo
INNER JOIN QueueComboBox D
ON B.WorkQueue = D.ProgramKeyField
WHERE B.Tagged = 0
AND D.ProgramKeyField = @WorkQueue
ORDER BY RiskId
You have no variable column names, no variable table names... the above should work just fine... or.... perhaps you're building the query in pieces from a GUI? More info, please...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
At least your "trigger moments" are very few and far between - mine're increasing at an alarming frequency..
However, I do not think you can use "PRINT @UseIt1" for testing since I believe the dynamic sql is in its' own scope...
**ASCII stupid question, get a stupid ANSI !!!** -
Heh, heh.... mine are getting worse... old brain.
You hit the nail on the head about the scope thing... I was curious as to why you declared the variable external to the dynamic SQL and then tried to set it's value within the dynamic SQL. The only place I've seen that work is with sp_ExecuteSQL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply