Answered by:
Passing Variable in SQL Query

Question
-
Hi,
I get this error in my code... Incorrect syntax near '+'.
How do I correctly pass @Name as a varchar? Thanks.
select * from myTable where (name = ''+@Name+'')
Tuesday, March 9, 2010 1:18 PM
Answers
-
with openquery you will need to use dynamic sql in order to put the variables in there.
What is the linked server type? DB2, Oracle etc..?
example
Exec('Select * from OPENQUERY([SERVERB\DATABASEName], ''select * from report.Export(' + @Start + ',' + @End + ')''')
Note that this is not highly recommended and poor on performance
Ted Krueger Blog on lessthandot.com @onpnt on twitter- Marked as answer by Zongqing Li Monday, March 15, 2010 7:42 AM
Tuesday, March 9, 2010 7:47 PM
All replies
-
Hi,
Try this
DECLARE @NAME VARCHAR(50)
SET @name = 'NameValue'
SELECT * FROM MyTable WHERE [name] = @Name
OR
SELECT * FROM MyTable WHERE [name] LIKE '%' + @Name + '%'
OR
SELECT * FROM MyTable WHERE [name] LIKE @Name + '%'
Rajesh Jonnalagadda http://www.ggktech.com- Proposed as answer by Naomi N Tuesday, March 9, 2010 2:45 PM
Tuesday, March 9, 2010 1:22 PM -
select * from myTable where name = @Name try to use column names and not * you do not need to enclose the variable in quotes but make sure you are protected from injection from the source
Ted Krueger Blog on lessthandot.com @onpnt on twitterTuesday, March 9, 2010 1:22 PM -
Thanks!
I am now trying to use OPENQUERY below..How do I ensure the variables are passed...
Both variables are datetime vars.
from OPENQUERY([SERVERB\DATABASEName], 'select * from report.Export(@Start, @End)')
Thanks again.Tuesday, March 9, 2010 3:53 PM -
with openquery you will need to use dynamic sql in order to put the variables in there.
What is the linked server type? DB2, Oracle etc..?
example
Exec('Select * from OPENQUERY([SERVERB\DATABASEName], ''select * from report.Export(' + @Start + ',' + @End + ')''')
Note that this is not highly recommended and poor on performance
Ted Krueger Blog on lessthandot.com @onpnt on twitter- Marked as answer by Zongqing Li Monday, March 15, 2010 7:42 AM
Tuesday, March 9, 2010 7:47 PM