January 20, 2003 at 11:30 am
This is a two part question concerning the use of CASE in a SELECT statement. I
am trying to write my procedure so that with a simple flag change it can output
either a resultset or xml. I was trying to do it very simply, but I don't
think the simple approach is going to work with this one.
1) Does anyone know how to use / implement a CASE in the FOR clause without
dynamic sql? Or if it can be done at all? The code below gives me a syntax error
leading me to believe the CASE statement cannot be used from the FOR Clause.
CREATE PROC spTest(@forXml bit = 0)
AS SELECT col1, col2
FROM TABLE1
CASE
WHEN @forXml = 1
THEN FOR XML AUTO, XMLDATA
ELSE THEN FOR BROWSE
END
2) Of the below portions of a select statement, which can you use a CASE
with, and is there any online documentation to that fact? I did a cursory search,
but found nothing. I have marked the ones that AFAIK allow the use of CASE. Did
I miss any of them? (Probably so...)
*SELECT Clause
INTO Clause
*FROM Clause
*WHERE Clause
*GROUP BY Clause
*HAVING Clause
*UNION Operator
*ORDER BY Clause
COMPUTE Clause
FOR Clause
OPTION Clause
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 20, 2003 at 11:54 am
I know you can use CASE in SELECT, WHERE, ORDER BY and possibly HAVING clauses. The rule is the same as with other operators like +, -, etc. I think syntactically it is just another operator. You cannot, for example, sum in the FROM clause.
January 21, 2003 at 4:27 am
Here is an example of using the Case statment in a stored procedure.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE NEWINSTRUMENT @QTY int AS
DECLARE @InsertCounter int, @RndNum Int
SET @InsertCounter = 1
WHILE @InsertCounter <= @QTY
BEGIN
SET @RndNum = CAST(RAND() * 11 AS INT) + 1
INSERT INTO INSTRUMENTS (INSTRUMENT, INST_VALUE)
SELECT CASE @RndNum
WHEN 1 THEN 'Flute'
WHEN 2 THEN 'Bassoon'
WHEN 3 THEN 'French Horn'
WHEN 4 THEN 'Oboe'
WHEN 5 THEN 'Tuba'
WHEN 6 THEN 'Triangle'
WHEN 7 THEN 'Harp'
WHEN 8 THEN 'Double Bass'
WHEN 9 THEN 'Kettle Drums'
WHEN 10 THEN 'Cymbals'
WHEN 11 THEN 'Maracas'
WHEN 12 THEN 'xylophone'
ELSE 'WashBoard'
END,
RAND()*3000 + 50
SET @InsertCounter = @InsertCounter + 1
END
RAISERROR('This Orchestra is Just too big..!',10,1) with log
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Regards
Pete
January 21, 2003 at 8:23 am
Thx Pete, What I was asking for was a way to use the CASE in the FOR clause, which I don't think is allowed. I am aware of out to use CASE in the field list of the SELECT, INSERT, UPDATE, DELETE statements, but what I wanted was an easy way to output either xml, or a normal resultset from the same query without having to dupe the query and wrap it with an IF statement. Or go dynamic. LOL, the last is not an option.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 21, 2003 at 8:41 am
Duplicating the statement and a if clause would be my choice if that is the oonly difference in the statement.
Also dynamic is easy as you already have the set sql statement, just add the part you want to change
Select CASE WHEN @forXml = 1 THEN @sql = @sql + ' FOR XML AUTO, XMLDATA 'ELSE THEN @sql = @sql + ' FOR BROWSE 'END
January 21, 2003 at 8:44 am
Sorry Tim, got the wrong end of the stick!
Pete
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply