[RESOLVED] could u tell me how to write a dynamic query which can hold a varchar type?

[raghunadhs]

Hi All,
This is regarding SQL server 2000.

How can i write a Dynamic query so that i can hold a varchar type variable also?
please see the below sample example what i have written..

Declare @varSql varchar(2000)
Declare @varName varchar(30)
set @varName= 'Harvinder'
set @varSql= 'select * from employee where empname = ' + ' ' varName ' '
Execute (@varSql)

I cam to know that i have to use two single quotes to represent a varchar type variable.. still i am getting error.
could u please say how to resolve it?

Thanks:
regards:
raghunadhs.v

[brucevde]

Declare @varSql varchar(2000)
Declare @varName varchar(30)
set @varName= 'Harvinder'
set @varSql= 'select * from employee where empname = ' + '''' + @varName + ''''

or to make life easier

Set Quoted_Identifier off

Declare @varSql varchar(2000)
Declare @varName varchar(30)
set @varName= "Harvinder"

set @varSql= "select * from Customers where CustomerId = '" + @varName + "'"

exec (@varSQL)

[raghunadhs]

Hi brucevde,
Thank you very much.it is fine and working well. could you pls explain what is the need of two single quotes and how it will be treated those two singel quotes.
Thanks & Regards:
raghunadhs.

]

Declare @varSql varchar(2000)
Declare @varName varchar(30)
set @varName= 'Harvinder'
set @varSql= 'select * from employee where empname = ' + '''' + @varName + ''''

or to make life easier

Set Quoted_Identifier off

Declare @varSql varchar(2000)
Declare @varName varchar(30)
set @varName= "Harvinder"

set @varSql= "select * from Customers where CustomerId = '" + @varName + "'"

exec (@varSQL)

[/QUOTE]

[Foxer]

This statement

Set Quoted_Identifier off

Lets you use double quotes (") to mark the start and stop of your string and use single quotes (') to mark your variables.

http://doc.ddart.net/mssql/sql70/set-set_30.htm

It's really just a readability thing and in your case - readability can make a big difference.