Need Help~SQL string within a SQL string problem

[leonwoo]

Hi
I am writing a stored procedure in Microsoft SQL server 2000 as shown below:

DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * FROM myTable WHERE name LIKE ' + '%[%]'
EXEC sp_executesql @sql


The SQL analyser gave me the error
" Invalid column name '%[%]' ". I think there is something to do with the string quote but i am not sure how to put another string quote within that line of statement. Please help.

[mongoose0211]

leonwoo,

Give this a try:
SET @sql = 'SELECT * FROM authors WHERE au_lname LIKE ''' + 'c%'''

[leonwoo]

Hi mongoose0211 thanks for the post,
The solution given seems don't work. Any other solution? I have already tried out the quote by using " but SQL does not recognize it as a string quote.

for example the following does not work in stored procedure.

'SELECT * FROM Customer where name = "James"'

[mongoose0211]

The quotes in my string are all single quotes:
SET @sql = 'SELECT * FROM authors WHERE au_lname LIKE ''' + 'c%'''


The first part of the string:
'SELECT * FROM authors WHERE au_lname LIKE '''
ends with 3 single quotes. In order for your string to contain a single quote you need to specify the quote twice, the 3rd single quote ends the first string.

If you cut and paste this string into Query Analyser:
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * FROM authors WHERE au_lname LIKE ''' + 'c%'''
print @sql


You should get this result:
SELECT * FROM authors WHERE au_lname LIKE 'c%'