Solved

select case in a UDF

Posted on 2013-01-15
8
319 Views
Last Modified: 2013-01-16
I'm creating a UDF that will take an int as input and then (via a select case) will run a particular SELECT statement. The return will go into a variable which will then be passed back to the caller. I seem to leap from one syntax error to another so thought I'd reach out.
basically...
Function accepts 2 parameters - @id int, @recType int and returns varchar
I declare a varchar called @
In a case, I want to check the value of  @recType
if = 30 then I want @results to be the result of
select x from Y where Z = @id
If = 40 then select Q from Y where Z = @id
etc etc

return @results
0
Comment
Question by:QPR
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 38780448
--You mean like this?

CREATE FUNCTION [dbo].[dba_getSomething]
(
      @id int, @recType int
)
      RETURNS varchar(max)
AS
BEGIN
      DECLARE @results varchar(max)

      SET @results = (SELECT
      CASE
            WHEN @recType = 30 THEN (select x from Y where Z = @id)
            WHEN @recType = 40 THEN (select q from Y where Z = @id)
            WHEN @recType = 50 THEN (select r from Y where Z = @id)
            WHEN @recType = 60 THEN (select s from Y where Z = @id)
            --...
      END
            )
      RETURN @results;
END
GO
0
 
LVL 29

Author Comment

by:QPR
ID: 38780648
Almost - I made a mistake in my Q
I need the select to be
WHEN @recType = 30 THEN (select @results = 'Contract Number: ' + FieldVal from....)

But I'm getting a syntax error                         ^^  here
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38780657
I suggest the style below instead, both for ease of coding, and possibly better performance (?):
 

CREATE FUNCTION dbo.Lookup_Value (
    @id int,
    @recType int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @return varchar(100)

IF @recType = 30
    SELECT @return = 'Contract Number: ' + FieldVal
    FROM dbo.Y
    WHERE Z = @id
ELSE
IF @recType = 40
    SELECT @return = 'Contract Date: ' + CONVERT(varchar(100), Q, 120) --in case Q is, say, a datetime
    FROM dbo.Y
    WHERE Z = @id
ELSE
IF @recType = <next_value>
BEGIN
    --extra prep statement(s) needed before reading this table
    <next_SELECT>
END --IF
ELSE
...

RETURN @return
END --FUNCTION
GO
0
 
LVL 29

Author Comment

by:QPR
ID: 38780991
I've currently got it as a list of IFs but wondered if a CASE might be quicker.
If not, or it's minimal then I'm happy to leave it as it is.
It's definately going to be a slow process given the the number of choices (and the fact that the function itself is part of a larger outer select) and the fact it's 500,000+ records.
That said, it's a one off process migrating data from one app to another so performance is not the #1 concern
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Expert Comment

by:lcohan
ID: 38782839
CASE vs. IF's

I think there there wopuld be a diference but that may be minimal - with IF statments the function would need to compare as many IFs you have in the function where with CASE is done in one statement where it supposedly runs once the proper statement. besides that the function plan may be smaller and better optimized however...there may not be major perfromance diffs - I personaly prefer CASE as is much cleaner smaller and easier to read/maintain.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38782952
CASE must evaluate the conditions one by one as well, so no real difference with IFs, as long as the IFs are ELSEd together and not all done separately.

The CASE limits you to ONE SELECT statement.  I like IFs because, if a specific, or new, @recType requires a bit of additional logic, it's very easy to add, but impossible to do that within a CASE.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 38783004
don't want to start any contest here - I respect ScottPletcher and all members here - however there are different opinions as said and some more opinions/details are at links below where someone put the dot on the I in my view in particular for this speciffic Scalar SQL UDF:

"CASE and IF/ELSE are completely different operators meant for completely different things. IF is meant for control flow. You can use it to set a value or conditionally SELECT something, but that's not it's primary purpose. CASE, on the other hand, is definitely meant for conditionally returning a value. The SQL CASE operator is almost like a ternary operator and switch statement rolled into one. You can pick from a series of conditions to output a single value. "

http://forums.xkcd.com/viewtopic.php?f=12&t=86655
http://www.techrepublic.com/article/in-t-sql-use-casewhen-in-place-of-ifthen/5078041
http://stackoverflow.com/questions/2429226/case-statements-versus-coded-if-statements

Regards to ALL.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38783042
I go by what SQL itself tells me is best from a performance standpoint.  CASE should not be used vs an IF to conditionally execute a query from a set of queries.

Paste the simple code below in any query window, select "Include Actual Execution Plan" and compare the two plans.  You can easily verify that the CASE forces SQL to compile EVERY possible plan, while the IFs only compile the actual plan used.  The more conditions you have, the greater the savings from IF.

CASE is very flexible, but SQL cannot and does not pre-evaluate the CASE condition(s), so ALL plans must be prepared.

For a set of IF and ELSEs, SQL "knows" that only one will ever need to be run, so that's all it compiles.

Particularly for a function or called proc, the overhead of all those additional compiles can make a big difference.



DECLARE @check_value int
DECLARE @object_id int
DECLARE @result sysname

SET @check_value = 10
SET @object_id = OBJECT_ID('sys.objects')

SELECT @result = CASE
    WHEN @check_value = 10 THEN (SELECT TOP (1) name FROM sys.objects WHERE object_id = @object_id)
    WHEN @check_value = 20 THEN (SELECT TOP (1) name FROM sys.indexes WHERE object_id = @object_id)
    WHEN @check_value = 30 THEN (SELECT TOP (1) name FROM sys.columns WHERE object_id = @object_id)
    END
   
GO

DECLARE @check_value int
DECLARE @object_id int
DECLARE @result sysname

SET @check_value = 10
SET @object_id = OBJECT_ID('sys.objects')

IF @check_value = 10
    SELECT TOP (1) @result = name FROM sys.objects WHERE object_id = @object_id
ELSE
IF @check_value = 20
    SELECT TOP (1) @result = name FROM sys.indexes WHERE object_id = @object_id
ELSE
IF @check_value = 30
    SELECT TOP (1) @result = name FROM sys.columns WHERE object_id = @object_id
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now