locked
Passing Variable in SQL Query RRS feed

  • 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 twitter
    Tuesday, 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