Convert Code to a Function 1

[Auguy]

Looking for some help in converting this to a function that I can call from another procedure.
OrderException table contains exception codes for each order and there may be none, one, or more than one exception.

Obviously this code performs 2 selects.
Is there anyway to only do this with only one select?

What would be really nice if it could return the @ExceptStr as well as a comma delimited string of all of the exceptions.
But maybe a future project.

DECLARE @ExceptStr varchar(15)
DECLARE @RowCnt int

SELECT ExceptionID from OrderException

SET @RowCnt = @@ROWCOUNT

If @RowCnt =  0 SET @ExceptStr = 'NONE'
If @RowCnt =  1 SET @ExceptStr = (SELECT TOP 1 ExceptionID FROM OrderException ORDER BY ExceptionID)
If @RowCnt >  1 SET @ExceptStr = '** MULTIPLE **'

SELECT @ExceptStr

Auguy
Sylvania/Toledo Ohio

 

[bborissov]

DECLARE @ExceptStr varchar(15)
DECLARE @RowCnt int
SELECT @RowCnt = ExceptionID FROM OrderException ORDER BY ExceptionID
SELECT @ExceptStr = CASE WHEN @@ROWCOUNT = 0 THEN 'NONE'
                         WHEN @@ROWCOUNT = 1 THEN @RowCnt
                    ELSE '** MULTIPLE **' END
SELECT @ExceptStr

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server

 

[Auguy]

Thanks Borislav, I will check it out.

I remember seeing your name on a many a post from my old VFP days.
I still have an app or two still running.

Auguy
Sylvania/Toledo Ohio

 

[fredericofonseca]

example below
function created with a parameter to allow demonstrating usage and the 3 return types.

if objecT_id('OrderException_test') is not null
drop table OrderException_test

select *
into OrderException_test
from (values (1, 1)
            ,(1, 2)
            ,(1, 3)
            ,(2, 1)
            ) t (ExceptionType, ExceptionID)

select *
from OrderException_test

go
create function [dbo].[GetExceptions]
(@ExceptionType smallint
)
returns table
as
return
    select case 
           when cnt = 1 then  'one' -- convert(varchar(10), ExceptionID) this will be returned on exceptionlist
           when cnt > 2 then '** MULTIPLE **'
           else 'None'
           end as result
         , stuff((select ',' + convert(varchar(10), ExceptionID)
            from OrderException_test
            where ExceptionType = @ExceptionType
            order by ExceptionID desc 
            for xml path(''), type).value('.','nvarchar(max)'),1,1,'') as exceptionlist
     from (select count(*) as cnt
                , max(ExceptionID) as ExceptionID 
           from OrderException_test
           where ExceptionType = @ExceptionType
           ) t


select *
from GetExceptions(1)
select *
from GetExceptions(2)
select *
from GetExceptions(3)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Auguy]

Thanks, Looks good, I will test it out.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

Finally got back to this and this is what I ended up with, many thanks to Frederico!

ALTER FUNCTION [dbo].[Udf_LoadMaster_Exceptions] 
(
	-- Add the parameters for the function here
	@LoadMasterPK int
)
RETURNS @ExceptionList TABLE 
(
	-- Add the column definitions for the TABLE variable here
	ExceptID varchar(10) 
	,ExceptList varchar(200)
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	INSERT INTO @ExceptionList

	SELECT CASE 
		WHEN cnt = 1 THEN CAST(ExceptionID AS varchar(10))
		WHEN cnt > 1 THEN '** MULT **'
		ELSE ''
		END AS ExceptID

		,STUFF((SELECT ',' + CAST(ExceptionID AS varchar(10))
			FROM dbo.LoadException
			WHERE LoadMasterFK = @LoadMasterPK
			ORDER BY ExceptionID DESC 
			FOR xml PATH(''), TYPE).value('.','nvarchar(max)'),1,1,'') AS ExceptList

	FROM (SELECT COUNT(*) as cnt
			,MAX(ExceptionID) as ExceptionID 
		FROM dbo.LoadException LE
		INNER JOIN ExceptionID EID On LE.exceptionfk = EID.exceptionpk
		WHERE LoadMasterFK = @LoadMasterPK
		) T
	
	RETURN 
END

Auguy
Sylvania/Toledo Ohio

 

[fredericofonseca]

NO!!! do not do it that way.

2 types of table functions
ITVF - faster - no table declaration
MSTVF - slower - table explicitly defined and populated within the body.

what I had supplied was a ITVF - and you converted it to MSTVF without any need to do it.

do change it to be

ALTER FUNCTION [dbo].[Udf_LoadMaster_Exceptions] 
(
	-- Add the parameters for the function here
	@LoadMasterPK int
)
RETURNS TABLE 
AS
return
	SELECT CASE 
		WHEN cnt = 1 THEN CAST(ExceptionID AS varchar(10))
		WHEN cnt > 1 THEN '** MULT **'
		ELSE ''
		END AS ExceptID

		,STUFF((SELECT ',' + CAST(ExceptionID AS varchar(10))
			FROM dbo.LoadException
			WHERE LoadMasterFK = @LoadMasterPK
			ORDER BY ExceptionID DESC 
			FOR xml PATH(''), TYPE).value('.','nvarchar(max)'),1,1,'') AS ExceptList

	FROM (SELECT COUNT(*) as cnt
			,MAX(ExceptionID) as ExceptionID 
		FROM dbo.LoadException LE
		INNER JOIN ExceptionID EID On LE.exceptionfk = EID.exceptionpk
		WHERE LoadMasterFK = @LoadMasterPK
		) T

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Auguy]

Thanks again Frederico. I think I ran into some issues trying to do it with your code the first time. Not sure whet that was now, probably me doing something stupid. Just corrected to you version and it works perfectly. Thanks for your expertise and following up.


Auguy
Sylvania/Toledo Ohio