|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%' but It return all rows regardless of changing condition to any value. But if i don't use sp_executesql like below: SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%' It's correct! Can anyone tell me why? Thanks |
|
#2
|
|||
|
|||
|
Is this alternative approach acceptable to you? I know you miss out on the pre-compile performance advantage...
Declare @SQLString nVarChar(100), @au_lname VarChar(20) Set @au_lname = 'Cas' Set @SQLString = 'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE ''%' + @au_lname + '%''' execute sp_executesql @SQLString |
|
#3
|
|||
|
|||
|
Declaring a variable as nvarchar means that it will have a max length of 1. You can figure the rest.
Why dynamic sql at all? Code:
create procedure mc( @au_lname nvarchar(30) ) as select SELECT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE '%' + @au_lname + '%' |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Problem with sp_executesql |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|