Solved

select case in a UDF

Posted on 2013-01-15
8
320 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
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.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Maintenance Plan 3 30
sql query Help 12 53
Insert Salary Period that has 2 months 11 23
Writing SQL Select Query result to a text file 12 27
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 …
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.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

17 Experts available now in Live!

Get 1:1 Help Now