July 2, 2008 at 11:47 am
How can I handle single quotes that are passed in as part of input parameters to SQL stored procedure.
For example if I have a stored proc to insert employee data and column value for name may or may not have single quote in it.
INput parameter value:
Sample 1) @name = 'sam'
Sample 2) @name = 'sam's'
July 2, 2008 at 12:11 pm
Make it two single-quotes in a row.
'O''Donnal'
'Sam''s'
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2008 at 7:07 am
so, why is this failing?
cn_userlist 'public' , 'strsearch','','o''brien'
error:
Line 4: Incorrect syntax near 'brien'.
here's the sp.....
ALTER proc [dbo].[cn_userlist] @restrict varchar(100) , @view nvarchar(10),
@ffilter nvarchar(255), @sfilter nvarchar(255) as
declare @sqlstring nvarchar(2000),@flag int
declare @filteron nvarchar(7), @filteroff nvarchar(4)
declare @concatflag bit
set @concatflag = 0
set @flag = 0
set @sqlstring = N''
set @sqlstring = @sqlstring + 'select rtrim(user_sname), rtrim(user_fname),
[id], rtrim(user_fname)+space(1)+rtrim(user_sname)
from cn_users'
if @view = 'namerange'
begin
set @sqlstring = @sqlstring + ' where substring(user_sname,1,1) = ''' + @sfilter +''' '
set @concatflag = 1
end
if @view = 'strsearch'
begin
-- if either name not blank
if @sfilter != '' and @ffilter != ''
begin
set @sqlstring = @sqlstring + ' where
(user_sname like ''%'+@sfilter+'%'' and user_fname like ''%'+@ffilter + '%'')
or (userid like ''%'+@sfilter+'%'') or (userid like ''%'+@ffilter+'%'') '
set @concatflag = 1
end
if @sfilter != '' and @ffilter = ''
begin
set @sqlstring = @sqlstring + ' where
(user_sname like ''%'+@sfilter + '%'') or (userid like ''%'+@sfilter+'%'') '
set @concatflag = 1
end
if @ffilter != '' and @sfilter = ''
begin
set @sqlstring = @sqlstring + ' where
(user_fname like ''%'+@ffilter + '%'') or (userid like ''%'+@ffilter+'%'') '
set @concatflag = 1
end
end
if @restrict <> 'public'
begin
if @concatflag = 1
begin
set @sqlstring = @sqlstring + ' and '
end
set @sqlstring = @sqlstring + ' [id] in
(select userid from restrict_to_members
where userid = [id] and restrict_to_group = @restrict)
order by user_sname, user_fname '
end
--select @concatflag
if @concatflag = 1
begin
set @sqlstring = @sqlstring + ' and '
end
if @concatflag = 0
begin
set @sqlstring = @sqlstring + ' where '
end
set @sqlstring = @sqlstring + ' (rtrim(user_sname) <> ''''
or rtrim(user_fname) <> '''') '
set @sqlstring = @sqlstring + ' order by user_sname, user_fname '
--print @sqlstring
exec sp_executesql @sqlstring-- , @forename
July 10, 2008 at 7:15 am
HI,
This is because when you concatenate your variable to your dynmaic string you are adding this:
o'Brain
You want to be adding o''Brain
This means that once you have your variable you should do a REPLACE(@sfilter,'''','''''').
This way your string inside you main string still has the '' instead of just the single '
So at the top of your proc this should solve it:
SET @sfilter = REPLACE(@sfilter,'''','''''')
does that make sense?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 10, 2008 at 7:31 am
I get your point, but doing the set doesn't work.
Sending the string from the code with '''' in does the trick though.
thanks
July 10, 2008 at 7:32 am
Very strange, it seemed to work for me I think he he he
well good to see you got it working thats the most important thing 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 10, 2008 at 7:54 am
Correct me if I am wrong.
1) we need to handle double quotes before passing it into stored proc / executing stored proc?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply