June 6, 2008 at 4:57 am
Hi,
I have a processlogtable. I want to show this table in several formats, depending on a parameter.
The code will be something like this (this example isn't working):
create function udf_processlog returns table as
return ( case @mode
when 1 then select cola,colb,cold from processlog where cola < 100
when 2 then select colb,cold,colf from processlog where cold > 10
when 3 then select cola,colf,colg from processlog where colf = @@USERID
end )
I couldn't find a working example. Is this construction possible? As an alternative, I could create 3 views, but that's
not my intention.
Wilfred
The best things in life are the simple things
June 6, 2008 at 6:10 am
A table valued function does require you to define the structure of the table. So, no, this won't work the way you want. I'm not at all sure, but I think you might be able to do what you're attempting in a CLR function.
However, it sure sounds like you're just looking at a classic select statement. Why push it into multi-statement table valued functions which perform badly most of the time? Why not simply use four stored procedures, one a wrapper proc to call the other three based on the input and the other three each selecting the columns you want in the order you want. It's a nicely shapped result set then and you can properly tune each procedure.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 6, 2008 at 6:20 am
You only have to define the tablestructure when the resultset is a Multistatement Table-valued Functions, which is not the case. Or do I have to change the resultset in your opinion?
Wilfred
The best things in life are the simple things
June 6, 2008 at 7:06 am
Problem is, you can't do the CASE statement like that. You have to use an IF and this becomes a multi-statement query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 6, 2008 at 12:32 pm
I'd create one function for each select. Use those instead of a parameter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2008 at 12:18 pm
You just need to have multiple CASE functions:
create function udf_processlog( @mode int ) returns table as
return (
SELECT
case @mode When 2 then colb Else cola End,
case @mode When 1 then colb, When 2 then cold Else colf End,
case @mode When 1 then cold, When 2 then colf Else colg End
From processlog
Where (@mode=1 AND cola < 100)
OR (@mode=2 AND cold > 100
OR (@mode=3 AND colf = @@USERID)
)
This works so long as the data types match.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply