Issue with executing dynamic sql with inbound paramter as varchar striong

Hi All:

I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.

When I try to execute it fails, because it does not plavce the inbound paramter in quotes.

Any help would be appreciated.

In the bound search as an eaxmple can be" 'NY'

@.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL

Here is the v_sql out put:

SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY

As you can see the sql will fail because the NY is not in quotes.

I tried using '@.P_SEARCH_VALUE''' and other forms but could not get it work.

There are a couple of ways (and variations) to approach this issue.

First, and the most 'robust', is to use the capability of sp_executesql to handle parameters. For the best explanition and demonstration, see Erland's article here.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Otherwise, you have to devise some scheme to manage quotes. For example, in you code above you are not managing the quotes inside your string. To embed a single quote inside a string, you have to double it up. So your filter would be more like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

Handling the quotes can seem to get out of hand, and very confusing. For that reason, the first option is the best.

|||

Since you are using the sp_executesql, itself supports the parameterized quires. You need not to concatenate those values (contaminating values may cause sql injection).

You can achieve the same result using the following query,

Code Snippet

SET @.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = @.P_SEARCH_VALUE'

SET @.V_SQL = @.V_BASE_SQL+@.V_SQL_FILTER

SET @.V_PARAM = N'@.P_SEARCH_VALUE VARCHAR(100)'

EXEC sp_executesql @.V_SQL, @.V_PARAM , @.P_SEARCH_VALUE

Sample,

Code Snippet

Declare @.SQL as Nvarchar(1000);

Declare @.Value as varchar(100);

Declare @.Param as Nvarchar(100);

Set @.SQL = N'Select * from sysobjects where name=@.value'

Set @.Param = N'@.value varchar(100)'

Set @.Value = 'sysobjects'

exec sp_executesql @.SQL, @.Param, @.value

|||

For something like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

You can use:

declare @.p_search_value nvarchar(20)

set @.p_search_value = 'This is a quote '''

select N' WHERE STATE = ' + quoteName(@.P_SEARCH_VALUE ,'''')

It is best to use sp_executeSQL, but if you have to work with quotes, this is the best tool for the job.|||try the following use three single quotes

@.P_SEARCH_VALUE='''NY'''

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL