(TSQL) stored procedure- add single quotes?

[MrPeds]

Hi,

I am working on a simple stored procedure that takes in a string argument , which is the name of a person e.g. Bob.

I would like to know if it is possible to set the name as 'Bob' instead of Bob, at the moment i have:

CREATE PROCEDURE myProc
@myname nvarchar(20)

DECLARE @cmdSQL nvarchar(1000)

SET @cmdSQL = 'SELECT col1, col2, col3,
(SELECT col4 WHERE name = ' + @myname + ')....etc

EXECUTE(@cmdSQL)

at the moment I am getting the subquery as
(SELECT col4 WHERE name = Bob) but I actually want
(SELECT col4 WHERE name = 'Bob')

How do i insert the '' ?

Thanks in advance,

MrPeds

 

[JohnDTampaBay]

SET @cmdSQL = 'SELECT col1, col2, col3,
(SELECT col4 WHERE name = ''' + @myname + ''')'


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[pgrant99]

Hi Angel,

Try something like this:

SET @cmdSQL = "SELECT col1, col2, col3, col4 WHERE name = '" + @myname + "'"

Using double quotes to enclose the single quotes, and make them part of the actual string you're building.

Hope that helps,
Phillip

 

[JohnDTampaBay]

If you use double quotes, then you'll have to deal with the QUOTED_IDENTIFIER settings. It is safer to stick with single quotes to define character strings.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[MrPeds]

The solution offered by AngelWPB works exactly as i need it!

Thank you!

 

[JamesLean]

Just another point, you should avoid using dynamic sql wherever possible. In your example you are using the variable in place of an expression so you don't actually need to use it:

CREATE PROCEDURE myProc
  @myname nvarchar(20)
AS

SELECT col1, col2, col3,
  (SELECT col4 FROM t2 WHERE name = @myname)
FROM t1
...

--James