Apostrophes and Dynamic SQL

I am trying to Ad-hoc query from a user input(internal app), where the user
enters in a name just as "Al's Game Crazy". Each time I do this, the
Apostrophe gets in the way. I have tried using the "char(39)" setup for wit
h
the like statement. But I keep getting and error.
Error returned:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Code:
set nocount on
Declare @.SQL_Where varchar(1000)
Declare @.Brand varchar(50)
Declare @.pos int
Declare @.SQL varchar(2000)
--Set @.Brand = 'Al' + char(39) +'s Game Crazy'
Set @.Brand = 'Al''s Game Crazy'
SET @.SQL_Where = ''
-- Check Brand
if Len(rtrim(ltrim(@.Brand))) > 0
BEGIN
SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
if @.pos > 0
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
Substring(@.Brand, @.pos + 1, len(@.Brand))
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand +
'%' + char(39) + ' and'
END
-- Remove the last part of the where clause filter " and"
SET @.SQL_Where = rtrim(@.SQL_Where)
SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
select @.Brand
select @.SQL_Where
SET @.SQL = 'select top 3000 * from view_ContactLocation where' + @.SQL_Where
select @.SQL
exec (@.SQL)
set nocount off
Any help or direction would be appreciated.
Thanks,
ScottTry replacing occurrances of the single apostrophe ' with double apostrophe
''
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||JT, I tried the modifiying the following:
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) + Substring(@.Brand,
@.pos + 1, len(@.Brand))
to
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' + Substring(@.Brand, @.pos
+ 1, len(@.Brand))
There appears to be no difference. I get the same errors. I believe that
is where you were thinking about doing the double "'".
Thanks,
Scott|||Scott Heffron wrote:
> JT, I tried the modifiying the following:
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> to
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> There appears to be no difference. I get the same errors. I believe
> that is where you were thinking about doing the double "'".
> Thanks,
> Scott
No. He's talking about using the REPLACE function to replace the apostrophe
with two apostrophes:
SET @.Brand=REPLACE(@.Brand,'''',''')
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You need to double-up the apostrophe to pass it into the stored procedure.
So from the app, the call need to look like this:
EXEC dbo.MyProc @.Brand = 'Al''s Game Crazy'
This doubling-up allows the stored procedure to be called successfully, as
the double apostrophe acts as an escape mechanism instead of ending the
string early. Now when you get the string into the stored procedure, it's
back to a single apostrophe only. So, if you are trying to put this string
into a string inside the stored procedure, you need to double them up again.
Of course, inside of SQL, you need to escape all instances of a quote. So,
try:
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' +
REPLACE(@.Brand, '''',''') +
'%' + char(39) + ' and' -- shudder... why add an AND unless you know there
is more WHERE?
Please see the following.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
I think with some work you could streamline this process quite a bit and
make it far less hairy-looking. I'm not going to address the @.sql and
@.sql_where, because I have no idea if and why end users are able to write
your sql statements for you. But for the wildcard search alone, you could
do this:
CREATE PROCEDURE dbo.SearchContacts
@.Brand VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 3000 <column list! don't use *>
FROM View_ContactLocation
WHERE Brand LIKE '%'+@.Brand+'%';
END
GO
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||Thanks, it worked.|||There are several columns that can be used in the where clause that is the
reason for the "and" at the end. Not all the columns are used. I did not
want to do a search on column1 = '%%' if nothing was in the @.column1
variable. I am expecting that is wasting query time and not needed. Sorry,
I used the "*" to save space. You are absolutely right on using the column
names.
What the variable will look like from the users application is "Al's Game
Crazy"
There are 9 different variables. Is it better to allow for a search on '%%'
or leave it out and create the where clause dynamically?
Thanks,
Scott|||> There are 9 different variables. Is it better to allow for a search on
> '%%'
> or leave it out and create the where clause dynamically?
Have you read the following article? I think it applies quite nicely to
your situation. It looks like a long read, but I think you'll be glad you
did it.
http://www.sommarskog.se/dyn-search.html|||Scott Heffron (ScottHeffron@.discussions.microsoft.com) writes:
> I am trying to Ad-hoc query from a user input(internal app), where the
> user enters in a name just as "Al's Game Crazy". Each time I do this,
> the Apostrophe gets in the way. I have tried using the "char(39)" setup
> for with the like statement. But I keep getting and error.
Rather than building the entire SQL string, use sp_executesql instead.
Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
an example. (Further below there is also an example that uses EXEC()
and deals with strings in a structured way.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973B3B229ACAYazorman@.127.0.0.1...

> Rather than building the entire SQL string, use sp_executesql instead.
> Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
> an example. (Further below there is also an example that uses EXEC()
> and deals with strings in a structured way.)
Other way is to use some tool that will do all job for you (provide your use
r
with friendly interface to create queries and generate SQL statement in resu
lt).
We prefer EasyQuery (http://devtools.korzh.com/eq/) but I think there are so
me
similar products even free ones.
With the best regards, Nik.