August 29, 2008 at 1:49 am
if normal CASE-WHEN-THEN statement, i can do this,
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN (Select column1 from table 1)
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
but what if I have more than one query in CASE-WHEN-THEN statement?
I found I cannot do these: "BEGIN Select ... END" or "goto label AAA":
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN (goto label AAA)
WHEN price >= 10 and price < 20 THEN (BEGIN Select ... END)
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
August 29, 2008 at 2:33 am
Well, you have to keep in mind, that you are inside of a select... so you can hardly jump somewhere else (GOTO). You can do that between two SELECTs, but not while you are halfway through one (and not only that, you also would be jumping out of the CASE statement before it was ended properly). And the BEGIN..END block has similar limitations.
Could you please explain, why exactly you need GOTO and BEGIN..END inside CASE? Is that just a theoretical question, or do you have some practical example where you think you need it?
August 29, 2008 at 2:42 am
The original SP is something like this and need to be fine-tuned:
if @FieldName='var1'
select @FValue = Col1 from Table1
else if @FieldName = 'var2'
select @FValue = col1 from Table2
else if @FieldName = 'var3'
select @FValue = col1 from Table3
else if @FieldName = 'var4'
select @FValue = col1 from Table4
...
else if @FieldName = 'var5'
begin -
if @bIsToTester = 1
begin
exec @ret = dbo.uspXXX ...
insert @BBB ( ...)
select ...
August 29, 2008 at 3:16 am
Well, now this is very different from the original post... the original post contained GOTO inside of a SELECT (that is, between SELECT and FROM, actually you were trying to jump somewhere else while calculating the value for resultset). Here, it is a set of IF..THEN constructs, which is perfectly OK. So where in this code would be the need to use something like you posted before - GOTO label?
August 29, 2008 at 3:35 am
:DHi there,
I agree with vladan, I was also a bit confused on the two examples you gave... But don't worry, I think I found what you need... Use a SCALAR Function which returns only one value.
Heres an example, I hope I got it right...
And I hope it helps...
CREATE TABLE #TABLE1
(
IDINT IDENTITY,
ItemVARCHAR(MAX)
)
CREATE TABLE TABLE2
(
IDINT IDENTITY,
ItemVARCHAR(MAX)
)
INSERT INTO #TABLE1 VALUES('A')
INSERT INTO #TABLE1 VALUES('B')
INSERT INTO #TABLE1 VALUES('C')
INSERT INTO TABLE2 VALUES('D')
INSERT INTO TABLE2 VALUES('E')
INSERT INTO TABLE2 VALUES('F')
GO
CREATE FUNCTION dbo.Table2Value()
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Val2 VARCHAR(MAX)
SELECT @Val2=(SELECT TOP 1 Item FROM TABLE2)
SET @VAL2=@VAL2+'(o^_'')'
RETURN @Val2
END
GO
CREATE TABLE #TABLE3
(
IDINT IDENTITY,
ValINT
)
INSERT INTO #TABLE3 VALUES(5)
INSERT INTO #TABLE3 VALUES(15)
INSERT INTO #TABLE3 VALUES(25)
SELECT * FROM #TABLE1
SELECT * FROM TABLE2
SELECT * FROM #TABLE3
DECLARE @val INT
SET @val=25
SELECT NewItem = CASE
WHEN Val<10 THEN (SELECT TOP 1 Item FROM #TABLE1)
WHEN Val>=10 and VAL<20 THEN dbo.Table2Value()
ELSE 'test'
END
FROM #TABLE3
GO
DROP TABLE #TABLE1
DROP TABLE TABLE2
DROP TABLE #TABLE3
DROP FUNCTION dbo.Table2Value
Please tell me if this post was helpful ^__^ Thanks
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 29, 2008 at 3:35 am
I intend to do something like this:
SELECT @FValue =
CASE @FieldName
WHEN 'var1'THEN (select Col1 from Table1)
WHEN 'var2'THEN (select Col2 from Table1)
WHEN 'var3'THEN (select Col3 from Table1)
WHEN 'var4'THEN (select Col4 from Table1)
...
WHEN 'var5'THEN (BEGIN
if @bIsToTester = 1
... END)
END
HI, Quatrei.X, thanks for suggesting UDF, but what if I have output paratemer need to be captured in 'var5'?
August 30, 2008 at 2:42 am
anyone?
August 30, 2008 at 5:57 am
it looks like you are just trying to make a single procedure return a variety of different values...sort of a do-all proc.
I would suggest getting rid of the case statment and just build a dynamic SQL
If @Fieldname = 'Col1'
BEGIN
SET @sql = @sql + ',COL1 FROM SOMETABLE
END
If @Fieldname = 'Col2'
BEGIN
SET @sql = @sql + ',COL2FROM SOMETABLE
END
PRINT @sql
EXEC @sql
Lowell
August 30, 2008 at 6:23 am
I intend to do something like this:
SELECT @FValue =
CASE @FieldName
WHEN 'var1' THEN (select Col1 from Table1)
WHEN 'var2' THEN (select Col2 from Table1)
WHEN 'var3' THEN (select Col3 from Table1)
WHEN 'var4' THEN (select Col4 from Table1)
...
WHEN 'var5' THEN (BEGIN
if @bIsToTester = 1
... END)
END
An alternative solution is to place Table1 in the FROM clause and then use a CASE.
SELECT @FValue =
CASE @FieldName
WHEN 'var1' THEN Table1.Col1
WHEN 'var2' THEN Table1.Col2
WHEN 'var3' THEN Table1.Col3
WHEN 'var4' THEN Table1.Col4
ELSE '?' -- do something intelligent ?
END
FROM ?
? LEFT OUTER JOIN? Table1
on Table1.somecolumn? = ?.someothercolumn?
As far as this statement:
WHEN 'var5' THEN (BEGIN
if @bIsToTester = 1
... END)
More details are needed to provide a solution.
SQL = Scarcely Qualifies as a Language
August 30, 2008 at 7:46 am
hi Lowell, Carl Federl, thanks for your suggestion. Althought the actual case is like similar to what Lowell think of ( and could be only choice), but using dynamic queries will against the purpose of my post, since I am trying to improve the performance of the query, maybe I need to analyze the execution plan more for other work-around. Thanks anyway.
August 30, 2008 at 5:47 pm
What do you get when you do the following, Gan?
[font="Courier New"]DECLARE @SomeNumber INT
SELECT @SomeNumber = Number
FROM Master.dbo.spt_Values
SELECT @SomeNumber[/font]
How many rows were processed? If you said "one", then you're very wrong and that's a possible source of your performance problem. You must correlated your sub-queries the way you have it. Better choice would be to properly join all the tables on properly indexed keys to maximize performance.
By the way, the first SELECT above processes 2346 and uses the last row of the non-clustered index that was used by the execution plan as the final value.
If you're processing as many or more rows to return just one value for each correlated sub-query, that would constitute a major performance problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2008 at 10:54 pm
I get 1469283328 for that. Sorry I do not quite understand what you are trying to explain. Can elaborate more? 😀
August 31, 2008 at 10:57 pm
:DHi there again,
Sorry GAN, I'm always offline during Sturdays and Sundays...
About the output parameter you where talking about... My exmaple already has one, except that its not actually an output parameter but a return value. Have you tried exectuing my code and checked the sample output?
oh yeah, by the way, heres a note, there should only be 1 return value, since the select statement looks for only one value...
uhmmm... did I get it right? just reply if you need some modifications
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 31, 2008 at 11:03 pm
Hi there,
hey, wait a minute, are you using this to get values of NEW table column"S" or a SINGLE variable?
cause some of one code says its for table columns and another say that its for a variable
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 31, 2008 at 11:16 pm
Hi Quatrei.X, thanks for your reply.
Sorry, maybe i should mention earlier. Actually inside the 'var5', it could be more that one output values returned. It makes "CASE" statement nearly impossible, right?
if @FieldName='var1'
select @FValue = Col1 from Table1
else if @FieldName = 'var2'
select @FValue = col1 from Table2
else if @FieldName = 'var3'
select @FValue = col1 from Table3
else if @FieldName = 'var4'
select @FValue = col1 from Table4
...
else if @FieldName = 'var5'
begin -
if @bIsToTester = 1
begin
exec @ret = dbo.uspXXX ...
insert @BBB ( ...)
select ...
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply