sql stored procedure single quotes

Question

I have a stored procedure as below

create procedure TestTry

BEGIN
declare @query varchar(max)
SET NOCOUNT ON;   

set @query = 'select * from table1 when RECORD_FLAG <> 't' 
then Convert(Decimal(10,4),ISNULL(T.HISTORY_PCR ,0))  else '0'';

exec (@query)
end

Here after RECORD_FLAG <> I want to give it in single quotes and also give 0 in single quotes how to give that

The above shown is a sample in real time my query is big I have to give the query in @query only

http://stackoverflow.com/a/1586588/2998271 – har07 Dec 26 '13 at 11:54 — har07, Dec 26 '13 at 11:54

score 4 · Accepted Answer · answered Dec 26 '13 at 11:53

4

Use '' (double) for ' (single)

set @query = 'select * from table1 when RECORD_FLAG <> ''t'' 
then Convert(Decimal(10,4),ISNULL(T.HISTORY_PCR ,0))  else ''0''';

answered Dec 26 '13 at 11:53

Priyank

1,353
9
13

M.Ali · Answer 2 · 2013-12-26T12:09:36.293

create procedure TestTry
AS       --<-- you'er also missing AS key word here
BEGIN
  SET NOCOUNT ON;
 declare @query varchar(max)


 set @query = 'select * from table1 when RECORD_FLAG <> ''t'' 
              then Convert(Decimal(10,4),ISNULL(T.HISTORY_PCR ,0))  else ''0''';

   EXECUTE sp_executesql @query      --<-- use this syntax to execute dynamic sql 
end

Also you do not need to use dynamic sql for such a simple query, dynamic sql is the devil of sql and you want to avoid using it whenever possible.

Your Query

Also your query syntax isnt right you are trying to execute a query as follows

select * 
from table1 
when RECORD_FLAG <> 't'   --<-- cant use WHEN without the CASE statement also you
                             --cannot use a Case statement here right after your table name

sql stored procedure single quotes

2 Answers2