Incorrect Syntax Near ''+''
-
Can anyone tell me why an error is being thrown for the following:
...AND time_task_attribs.numeric_value <> 99
AND MONTH(record_date) = '
+ CAST(@intMonth AS VARCHAR) + '
AND YEAR(record_date) = '
+ CAST(@intYear AS VARCHAR) + '
AND time_user_attribs.string_value IN (SELECT fvchJxGroupID
If I insert values instead of variables the whole script executes?! The error text is
Msg 102, Level 15, State 1, Procedure rpMySP, Line 78
Incorrect syntax near '+'.
Line 78 is the first line to contain the CAST statement
-
I'm getting the same error with this syntax:
Select
@ListNo, BulkColumn from Openrowset( Bulk 'C:\Program Files\SComp\Photos\' + convert(varchar(10),@ListNo) + '_2.jpg', Single_Blob) as Photo2
Like David's, this script also works with values, but not with the variable concatenation.
I hope you don't mind me piggy-backing.
-
Guess we'll need to see the rest... no errors there except can't see where the starting and ending quotes are at the beginning and end of your snippet.
--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) -
I'm pretty sure that OpenRowSet wants constants, not caclulations, as it's operands. You'll probably need to shift the whole thing to dynamic SQL.
--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) -
Thanks, Jeff. I'm green enough that I'm now googling "Dynamic SQL."
Thanks for your help!
-
Jeff, thanks for the advice
-
Hi CJ,
Dynamic SQL sorted the problem. In case you haven't had a chance to look yet the basics are as follows:
1. Create a variable to hold the statement (I used a NVARCHAR(4000))
2. Set the variable to the statement
3. Use EXEC SP_EXECUTESQL @statement_variable to run the statement
-
Thanks Jeff and David!
-
You bet, folks... thanks for the feedback.
--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) -
Hi,
when You store query in the variable single quote(') is used for
string terminater. Thatswhy, value of @intMonth variable is not concatenate into query. You can use query with following changes I thinks it helps u.
..AND time_task_attribs.numeric_value 99
AND MONTH(record_date) = ''' + CAST(@intMonth AS VARCHAR) + '''
AND YEAR(record_date) = ''' + CAST(@intYear AS VARCHAR) + '''
AND time_user_attribs.string_value IN (SELECT fvchJxGroupID
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply