Perform a different select statement based on input in a function

[kxramse]

I am trying to pass a variable to a function that will tell the function which select statement to perform. I've noticed that I can do this by using a table value function but I wanted to do a scaler value function.

For simplicity, I simplified my select statement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.fnKeith (@CessionNumber varchar(255), @ReportMonth Int, @ReportYear Int)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @Result as Decimal(10,2)
	IF @CessionNumber in (Null, '')
		begin
		Select  @Result = ADDReinsure from
			SELECT	volume as ADDReinsure
                            from reports
                        where reportmonth= @ReportMonth 
                           and reportyear= @ReportYear
		END

	IF @CessionNumber not in (Null, '')
		begin
		Select  @Result = ADDReinsure from
			SELECT	volume as ADDReinsure
                        from reportPS
                        where reportmonth= @ReportMonth 
                           and reportyear= @ReportYear
		end
return @result
END
GO

I can't get that to work. I've tried variations, but it doesn't seem to get it through the complier such that I can even create the function.

I can do...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create FUNCTION [dbo].fnTableKeith (@CessionNumber varchar(255), @ReportMonth Int, @ReportYear Int)

RETURNS @Orders TABLE (LifeID INT, ADDReinsure decimal(10,2))
AS
begin
IF @CessionNumber in (Null, '')
	begin
	INSERT INTO @Orders (ADDReinsure)
		Select  @Result = ADDReinsure from
			SELECT	volume as ADDReinsure
                            from reports
                        where reportmonth= @ReportMonth 
                           and reportyear= @ReportYear
	END

IF @CessionNumber not in (Null, '')
	begin
	INSERT INTO @Orders (ADDReinsure)
		Select  @Result = ADDReinsure from
			SELECT	volume as ADDReinsure
                            from ReportPS
                        where reportmonth= @ReportMonth 
                           and reportyear= @ReportYear
	end
RETURN
end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

I suspect you can't have multiple select statements in a non table valued function.

So is it possible? Am I missing something?

 

[gmmastros]

The problem you are having is with the way you are assigning the value to the scalar @Result.

Try this...

CREATE FUNCTION dbo.fnKeith (@CessionNumber varchar(255), @ReportMonth Int, @ReportYear Int)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @Result as Decimal(10,2)
    IF IsNull(@CessionNumber, '') = ''
        begin
        Select  @Result = (
                          SELECT volume
                          from   reports
                          where  reportmonth= @ReportMonth 
                                 and reportyear= @ReportYear
                          )
        END
    Else
        begin
        Select  @Result = (
                          SELECT volume as ADDReinsure
                          from   reportPS
                          where  reportmonth= @ReportMonth 
                                 and reportyear= @ReportYear
                          )
        end
return @result
END

or...

CREATE FUNCTION dbo.fnKeith (@CessionNumber varchar(255), @ReportMonth Int, @ReportYear Int)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @Result as Decimal(10,2)
    IF IsNull(@CessionNumber, '') = ''
        begin
        Select @Result = volume
        from   reports
        where  reportmonth= @ReportMonth 
               and reportyear= @ReportYear
        END
    Else
        begin
        Select @Result = volume
        from   reportPS
        where  reportmonth= @ReportMonth 
               and reportyear= @ReportYear
        end
return @result
END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom