Printable View
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.
leonwoo,
Give this a try:
SET @sql = 'SELECT * FROM authors WHERE au_lname LIKE ''' + 'c%'''
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"'
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%'