String value in SQL Server stored procedure?

Question

I am working on a SQL Server stored procedure to export a file in .csv format, in which there is conditional statement given below:

xp_cmdshell  ' bcp 
"Select ''Status'' union all Select (CASE WHEN (foo.new_value != '' ) THEN  ''Start'' ELSE ''Stop'' END)
from table_name as foo"
queryout c:/test.csv -T -c -t'

When I run the above query it's not exporting any file on executing the procedure. I think the problem occurs due to string Start and Stop values because when I execute a query mentioned below, it's executing perfectly fine and successfully export file with name test.csv.

xp_cmdshell  ' bcp 
"Select ''Status'' union all Select foo.new_value from table_name as foo" 
queryout c:/test.csv -T -c -t'

So here is my question how can I insert string value which replace new_value column value with "Start" or "Stop" values.

Appreciate any help.

James Z · Accepted Answer · 2015-11-01 06:23:08Z

1

Isn't the problem just the case statement where you haven't escaped ' properly, since the result of your string is:

... CASE WHEN (foo.new_value != ' ) THEN

So you should add 2 more 's:

... CASE WHEN (foo.new_value != '''') THEN

answered Nov 1, 2015 at 6:23

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String value in SQL Server stored procedure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related