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+'')

Answer 1

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

Answer 2

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 twitter

Answer 3

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.

Answer 4

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