charakter ' in storeprocedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

charakter ' in storeprocedure

Author

Topic

joshherman
Starting Member

13 Posts

Posted - 2011-05-19 : 23:13:44

i have sql sintax :


select col1, case when a = '12' or b = 'A' then x.quantity else 0 end as tot
from table1 as x

and than i want to create that to storeprocedure with condition

if col1 <> '' then
' where col1 = 'ABC'
end if

i use this sintax in procedure :


declare @sql varchar(1000);

select @sql = 'select col1, case when a = '12' or b = 'A' then x.quantity else 0 end as tot
from table1 as x';

if (@sql) 
	begin 
		select @sql = @sql + ' where col1 = 'ABC';
	end

exec (@sql)

but, the problem at


case when a = '12' or b = 'A'

because i use ' character

how to fix this problem ?

thank you :)

programming

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 23:17:50

You can escape single quotes using another single quote, so your query would be like this:


select @sql = 'select col1, case when a = ''12'' or b = ''A'' then x.quantity else 0 end as tot
from table1 as x';

As an aside, if you don't need to use dynamic SQL, it is preferable to avoid it. Take a look at this page, especially the sections about SQL injection and related security issues: http://www.sommarskog.se/dynamic_sql.html

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 23:21:07

Couple of additional changes to your script:


declare @sql varchar(1000);

select @sql = 'select col1, case when a = ''12'' or b = ''A'' then x.quantity else 0 end as tot
from table1 as x';

if (@sql is not null) 
	begin 
		select @sql = @sql + ' where col1 = ''ABC''';
	end
exec (@sql);

joshherman
Starting Member

13 Posts

Posted - 2011-05-19 : 23:49:41

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources