SQL Function for SumIf 2012

Question

I have the following sql function, but not running correctly, the intended returned value is the total

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create FUNCTION [dbo].[SumIf](@ColumnName [varchar](100), @Criteria [varchar](500))
RETURNS [decimal] AS 
BEGIN
    -- Declare the return variable here
    DECLARE @Total Decimal
    DECLARE @TableName Decimal
    Select @Total = SUM(@ColumnName) from @TableName where  @Criteria
    RETURN @Total

END

the use syntax would be something like

Select dbo.sumif(fees.fee_amount, Fees.Fee_Code ='B01')

So the tablename would also need to be extracted from the columnname variable passed.

score 0 · Answer 1 · answered May 14 '15 at 23:35

I don't think you will be able to implement your function that way. You're essentially trying to pass an expression to the function which I don't think is possible in SQL Server. You may be able to do it with dynamic SQL passing in strings but not as cleanly as you're hoping.

John Smith · Answer 2 · 2015-05-15T00:03:07.720

0

you need dynamic SQL to do this, but unfortunately you can't use dynamic SQL inside a UDF. try this-

DECLARE @ColumnName [nvarchar](100) = '' --fill in values here
DECLARE @TableName [nvarchar](100) = ''
DECLARE @Criteria [nvarchar](500) = ''

DECLARE @s nvarchar(max)
DECLARE @res bigint

set @s = 'SELECT @result = SUM(' + @ColumnName + ') from ' + @TableName + ' where ' + @Criteria
exec sp_executesql @s, N'@result OUTPUT', @result = @res OUTPUT
select @res

edited May 15 '15 at 00:03

answered May 14 '15 at 23:45

John Smith

7,243
6
49
61

Note that this is vulnerable to SQL Injection (And cant be parameterized) – D Stanley May 14 '15 at 23:47
@DStanley yeah, but if he's just using it himself that isn't much of a concern. – John Smith May 14 '15 at 23:48
Thanks, tried it and ran Select dbo.sumif(Fees.fee_amount, Fees ,Fees.Fee_Code ='B01'), but got an error : Incorrect syntax near '='. – Bill May 14 '15 at 23:51
@Bill try this. Select dbo.sumif('Fees.fee_amount', 'Fees' ,'Fees.Fee_Code =''B01''') – John Smith May 14 '15 at 23:54
thanks. Now got this error: Only functions and some extended stored procedures can be executed from within a function. – Bill May 14 '15 at 23:56
Turns out you can't use dynamic SQL inside a function. I would try saving the script outside of a function and just updating the variables if this is the route you want to go. You could also go with the solution to the last post you made about this, which isn't bad.. http://stackoverflow.com/questions/15180561/getting-an-error-when-executing-a-dynamic-sql-within-a-function-sql-server – John Smith May 15 '15 at 00:00

score 0 · Answer 3 · answered May 15 '15 at 00:33

You are way on the wrong track. If you wanted to put sumif() in a select statement, it would need to be a user-defined aggregation function, rather than just a user-defined function. The fact that you've declared @TableName to be decimal and then use it in a from clause points to other issues.

So, my suggestion is that you just do this in-line:

select sum(case when <condition> then <columnname> else 0 end) as sumval
from <tablename>

If you wanted a programming block to put the data together, then use a stored procedure. Something like:

Create FUNCTION [dbo].SumIf(@ColumnName varchar(100),
                            @TableName varchar(255)
                            @Criteria varchar(500),
                            @Total Decimal OUTPUT)
                           ) AS 
BEGIN
    DECLARE @sql nvarchar(max) = 'Select @Total = SUM(@ColumnName) from @TableName where @Criteria';
    set @sql = replace(@sql, '@ColumnName', @ColumnName);
    set @sql = replace(@sql, '@TableName', @TableName);
    set @sql = replace(@sql, '@Criteria', @Criteria);
    sp_execute_sql @sql, N'@total decimal output', @total = @total output;
END;

SQL Function for SumIf 2012

3 Answers3