sp_executesql like comparison

[iminore]

I'm getting all records returned with this:

declare @strexec nvarchar(800), @strsearch nvarchar(80)
set @strsearch = N'%law%'
set @strexec = N'SELECT TitleID,title FROM Titles where title like @strsearch$'
execute sp_executesql @strexec, N'@strsearch$ nvarchar' , @strsearch$ = @strsearch

any ideas?

 

[hmckillop]

Try

declare @strexec nvarchar(800), @strsearch nvarchar(80)
set @strsearch = N'%law%'
set @strexec = N'SELECT TitleID,title FROM Titles where title like ''' + @strsearch + ''''
execute sp_executesql @strexec, N'@strsearch$ nvarchar' , @strsearch$ = @strsearch


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[iminore]

yes that works, although that way it isn't actually using the parameter replacement in sp_executesql - the replacement has been done before sp_executesql runs.

equivalent to:

declare @strexec varchar(800), @strsearch varchar(80)
set @strsearch = '%lawyer%'
execute('SELECT TitleID,title FROM LCTTitles where title like ''' + @strsearch + '''')

which works

many thanks

be nice to know if it is possible using parameter replacement in sp_executesql ...

 

[hmckillop]

sorry, this is what you are looking for then, (mainly taken from BOL)

DECLARE @strVariable varchar(20)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
     N'SELECT TitleID,title FROM Titles where title like @strsearch'
SET @ParmDefinition = N'@strsearch nvarchar(80)'
/* Execute the string with the first parameter value. */
SET @strVariable = '%law%'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @strsearch = @strVariable

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[iminore]

which is what I had in the first place - and doesn't work

 

[hmckillop]

Read it again, it isnt exactly what you had, notice the differences in the parameter definition in which it defines the a string containing the variable @strsearch as nvarchar(80) all in a string.

You had

declare @strexec nvarchar(800), @strsearch nvarchar(80)
set @strsearch = N'%law%'
set @strexec = N'SELECT TitleID,title FROM Titles where title like @strsearch$'
execute sp_executesql @strexec, N'@strsearch$ nvarchar' , @strsearch$ = @strsearch

I have:

DECLARE @strVariable varchar(20)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
     N'SELECT TitleID,title FROM Titles where title like @strsearch'
SET @ParmDefinition = N'@strsearch nvarchar(80)'
/* Execute the string with the first parameter value. */
SET @strVariable = '%law%'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @strsearch = @strVariable

try it again with above.


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[iminore]

I've just realised an original error - I didn't specify the length of the parameter. My original code now works.

 

[ESquared]

Hmmm you don't need quotes?

set @strexec = N'SELECT TitleID,title FROM Titles where title like [b][red]''[/red][/b]@strsearch$[b][red]''[/red][/b]'

The parameter replacement is not how I expected, then.

 

[hmckillop]

You're welcome

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.