Problem using EXEC within SELECT CASE statement

Question

Problem using EXEC within SELECT CASE statement

donne4real

Old Hand

Points: 386
More actions
December 30, 2009 at 11:10 am

#217926

Here is an idea of what I actually intend to do:
I intend to check if each of about 20 or so files exist. And if a file exists, call the executable to run a job for each. My initial plan was to use the select case statement.
Something like this:
SELECT
CASE filename
WHEN 'filename1' THEN exec master.dbo.xp_cmdshell "filename1.bat"
WHEN 'filename2' THEN exec master.dbo.xp_cmdshell "filename2.bat"
and so on
But it seems that I cannot use the EXEC command within the SELECT CASE statement.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21829 More actions · Answer 1

Why not use SSIS for this? Why would you want to use those old batch files? And also from within SQL. That is just bad news waiting to happen. Use SSIS to read the contents of the directory, if the file exists, then do something.

Andrew SQLDBA

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

AndrewSQLDBA (12/30/2009)
That is just bad news waiting to happen.

Heh... that's funny... I was just going to say the same thing about using SSIS. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AndrewSQLDBA SSC-Insane Points: 21829 More actions · Answer 3

I have never had any issues with SSIS. Maybe it is time for a new server.

Andrew SQLDBA

Lowell SSC Guru Points: 323547 More actions · Answer 4

just so you know what the issue is:

in TSQL, a CASE statement is used to decide which data to return; it can only return one of the SQL datatypes. you cannot use a case statement to control a logic flow.

an IF statement can be used to control logic flow, so it should look something like this:

IF @filename = 'filename1'

exec master.dbo.xp_cmdshell "filename1.bat"

IF @filename = 'filename2'

exec master.dbo.xp_cmdshell "filename2.bat"

Everyone else already gave you great advice on a better way to handle this, I just wanted to point out the syntax issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AndrewSQLDBA SSC-Insane Points: 21829 More actions · Answer 5

Great catch

I looked at that and did not even see the error.

I think that I will be going home now, it has been a very long week already

Andrew SQLDBA

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

AndrewSQLDBA (12/30/2009)
I have never had any issues with SSIS. Maybe it is time for a new server.
Andrew SQLDBA

Heh... unlike most, I've never had an issue with SSIS because the other way works just fine and don't need SSIS. Contrary to popular belief, the other way is not "bad news waiting to happen". For the record, I don't believe SSIS is either. Maybe it's time to revisit what works.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)