CODEWITHSUNDEEP
sql-servercsvstored-procedures
Zeb
Zeb

Reputation: 2777

String value in SQL Server stored procedure?

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.

Upvotes: 0

Views: 74

Answers (1)

James Z
James Z

Reputation: 12317

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

Upvotes: 1

Related Questions