Apostrophes and Dynamic SQL

28 Dec 2005 8:56 PM

Scott Heffron

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

28 Dec 2005 9:18 PM

JT

Try replacing occurrances of the single apostrophe ' with double apostrophe
''

Show quote

"Scott Heffron" <ScottHeff***@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
>
>
>

28 Dec 2005 9:39 PM

Scott Heffron

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

28 Dec 2005 9:47 PM

Bob Barrows [MVP]

Scott Heffron wrote:
Show quote

> 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"

28 Dec 2005 10:16 PM

Scott Heffron

Thanks, it worked.

28 Dec 2005 10:06 PM

Aaron Bertrand [SQL Server MVP]

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

Show quote

"Scott Heffron" <ScottHeff***@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
>
>
>

28 Dec 2005 10:21 PM

Scott Heffron

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

28 Dec 2005 10:31 PM

Aaron Bertrand [SQL Server MVP]

> 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

28 Dec 2005 11:21 PM

Erland Sommarskog

Scott Heffron (ScottHeff***@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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

29 Dec 2005 11:11 AM

Nik Hatcher

"Erland Sommarskog" <esq***@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 user
with friendly interface to create queries and generate SQL statement in result).

We prefer EasyQuery (http://devtools.korzh.com/eq/) but I think there are some
similar products even free ones.

--
With the best regards, Nik.