Why isn't my sql scalar function not working within my case statement.

Littleghostface
Littleghostface used Ask the Experts™
on
Hi,

Could some please tell me why doesn't the functions work within the case statement but work while outside of the case statement.  

Code Below:

SELECT
      cb1.BatchId
    ,cb1.TypeId
    ,'\\servername\e$\Letters\'+ct1.[Key]+'_'+CONVERT(VARCHAR(8), GETDATE(), 112)+'_Id'+CONVERT(varchar(5),cb1.BatchId)+'.xml' [XMLfilename]
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
    ,case      
            when cb1.TypeId = 1 then
            (select dbo.ufnGetDoctorLetterXML (cb1.BatchId) )
            
            when cb1.TypeId = 2 then
            (select dbo.ufnGetPatientLetterXML(cb1.BatchId) )
     END [Data]
FROM
      Batch cb1
      inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
      cb1.IsProcessed = 0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your CASE Statement doesn't look right.  Try this:

,case      
            when cb1.TypeId = 1 then
            (select dbo.ufnGetDoctorLetterXML (cb1.BatchId) ) as [Data]
           
            when cb1.TypeId = 2 then
            (select dbo.ufnGetPatientLetterXML(cb1.BatchId) ) as [Data]
     END
Commented:
SELECT
      cb1.BatchId
    ,cb1.TypeId
    ,'\\servername\e$\Letters\'+ct1.[Key]+'_'+CONVERT(VARCHAR(8), GETDATE(), 112)+'_Id'+CONVERT(varchar(5),cb1.BatchId)+'.xml' [XMLfilename]
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
    ,case      
            when cb1.TypeId = 1 then
            dbo.ufnGetDoctorLetterXML (cb1.BatchId)
           
            when cb1.TypeId = 2 then
             dbo.ufnGetPatientLetterXML(cb1.BatchId)
     END [Data]
FROM
      Batch cb1
      inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
      cb1.IsProcessed = 0

Author

Commented:
@Forefront_Data_Solutions - my statement is correct.  If I was substitute the cb1.BatchId with the actual integer then it works.  I want to use the current cb1.BatchId of the current row as my function variable.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I understand now.  I believe that sas13's solution is correct.  Have you tried it?

Author

Commented:
@sas13 - Nope tried that already.  The statement runs but it returns NULL for my function results.  If I was to do this then it works.  

Case
     when cb1.Type = 1 then
     dbo.ufnGetDoctorXML(225) END [DATA]

Commented:
test ;)

declare @t table (val int)

insert into @t values (1), (2), (3)

select Case
     when 1 = 1 then SUBSTRING('abcdefgh', t.val, t.val) END [DATA] from @t t

i use SUBSTRING function instead of ufnGetDoctorLetterXML
sas13's solution should be correct
can you verify that you are passing in the correct BatchID for the correct function
I built some test tables and functions and this seems to work:

SELECT
      cb1.BatchId
    ,cb1.TypeId
    ,'\\servername\e$\Letters\'+ct1.[Key]+'_'+CONVERT(VARCHAR(8), GETDATE(), 112)+'_Id'+CONVERT(varchar(5),cb1.BatchId)+'.xml' [XMLfilename]
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
,[Data]= case      
            when cb1.TypeId = 1 then
            dbo.ufnGetDoctorLetterXML (cb1.BatchId)
           
            when cb1.TypeId = 2 then
            dbo.ufnGetPatientLetterXML(cb1.BatchId)
     END
FROM
      Batch cb1
      inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
      cb1.IsProcessed = 0
I also went back and ran sas13's code and it works also.  Can you check that you are passing in the correct BatchID (per ewangoya's suggestion above)?

Author

Commented:
@Forefront_Data_Solutions - what works?  Are you receiving an XML out under the case statement or when the function is used as a regular column?  

Doing the temp variable table seems to work from @sas13.  I'm still doing some testing.
I don't have any clue as to what your Functions are supposed to return.  You had asked why the functions don't work in the case statement, so I created simple functions that return an integer.  I verified that the Case statements provided by sas13 and myself work correctly.  What we can't check is whether you are passing in the correct values, nor can we determine what your function is doing or what it is supposed to return.  If you would provide that code, we would be glad to help.  

Author

Commented:
To everyone.  Sorry for wasting everyone times.  This was my mistake.  After all my statement was correct.  If you notice I am using two different functions for each Case.  I was using the wrong function for the Case type.  I had to flip flop them.  I am now receiving my XML output.  I going to accept all multiple answers if they will let me.  Thanks Again.
I changed my test functions to return XML and they are also working correctly.  

Results from my test data:


Capture.JPG

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial