Solved

select case in a UDF

Posted on 2013-01-15
8
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 40

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:Scott Pletcher
Scott Pletcher 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
LVL 40

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:Scott Pletcher
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 40

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:Scott Pletcher
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Master DB with Masterkey 1 40
Negative isnull? 3 30
Using this function 4 51
read Json fields and insert all the content into a table by Store Procedure 4 31
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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