Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select case in a UDF

Posted on 2013-01-15
8
Medium Priority
?
329 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 1000 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 70

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 70

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 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