select case in a UDF

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
LVL 29
QPRAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
--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
 
QPRAuthor Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
QPRAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
lcohanDatabase AnalystCommented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.