scalar function returning zero when it shouldn't

  • I have this code:

    -------------------------------------------

    Declare @sql as varchar(4000)

    declare @tbl as varchar(100)

    declare @exists as bit

    select @tbl = 'ACA_RSF'

    select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''

    print @sql

    exec (@sql)

    --------------------------------------------

    and it returns 18 million for a record count.

    I have this scalar returning function, which models the above, and it returns zero:

    select dbo.fnGet_Rec_Count('ACA_RSF') as cnt

    here is the code:

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')

    -- Return the result of the function

    RETURN @count

    END

    GO

    --------------------------------------------------------------------------

    I get zero regardless of where @count is declared as in or bigint.

    Any ideas?

  • you don't put quotes around the parameter; it's already the right datatype.

    your real function probably raises an error, sicne you have two variables:@tbl and @source_tbl, but only one is declared.

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = (select count(*) from [member_score] where source_tbl = @source_tbl )

    -- Return the result of the function

    RETURN @count

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (6/16/2015)


    you don't put quotes around the parameter; it's already the right datatype.

    your real function probably raises an error, sicne you have two variables:@tbl and @source_tbl, but only one is declared.

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = (select count(*) from [member_score] where source_tbl = @source_tbl )

    -- Return the result of the function

    RETURN @count

    END

    Same thing I came up with as well. Why do people think they need to use dynamic SQL when the variable is in the WHERE clause? That's where you can use the variable directly.

  • Lynn Pettis (6/16/2015)


    Lowell (6/16/2015)


    you don't put quotes around the parameter; it's already the right datatype.

    your real function probably raises an error, sicne you have two variables:@tbl and @source_tbl, but only one is declared.

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = (select count(*) from [member_score] where source_tbl = @source_tbl )

    -- Return the result of the function

    RETURN @count

    END

    Same thing I came up with as well. Why do people think they need to use dynamic SQL when the variable is in the WHERE clause? That's where you can use the variable directly.

    Actually, can be simplified a bit:

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = count(*) from [member_score] where source_tbl = @source_tbl;

    -- Return the result of the function

    RETURN @count

    END

  • Thanks everyone. I didn't catch those mistakes. However, I'm still getting zero.

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = count(*) from [member_score] where source_tbl = @source_tbl

    -- Return the result of the function

    RETURN @count

    END

    GO

    -------------------------------------------------------------

    and I pulled that line out of the function and ran it alone, along with declaring and populating the variables, and it gives me the right number. It's not liking something about the function return, maybe?

  • DSNOSPAM (6/16/2015)


    Thanks everyone. I didn't catch those mistakes. However, I'm still getting zero.

    alter FUNCTION spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = count(*) from [member_score] where source_tbl = @source_tbl

    -- Return the result of the function

    RETURN @count

    END

    GO

    -------------------------------------------------------------

    and I pulled that line out of the function and ran it alone, along with declaring and populating the variables, and it gives me the right number. It's not liking something about the function return, maybe?

    Whatever member_score is, you should schema-qualify it (dbo.member_score, results.member_score etc).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • well here's a fully tested prototype that returns two for the value

    any issues you have woudl be details...the data doesn't exist, usin the wrong data types, wrong column, etc.

    IF OBJECT_ID('[dbo].[member_score]') IS NOT NULL

    DROP TABLE [dbo].[member_score]

    GO

    CREATE TABLE [dbo].[member_score] (

    [member_scoreID] INT IDENTITY(1,1) NOT NULL,

    [OtherColumns] VARCHAR(30) NULL,

    [source_tbl] VARCHAR(30) NULL,

    CONSTRAINT [PK__member_s__5F219F5C6C390A4C] PRIMARY KEY CLUSTERED ([member_scoreID] asc))

    GO

    INSERT INTO [member_score](source_tbl)

    SELECT 'ACA_RSF' UNION ALL SELECT 'ACA_RSF' UNION ALL SELECT 'BDB_RSF' UNION ALL SELECT 'BDB_RSF' UNION ALL SELECT 'BDB_RSF' UNION ALL SELECT 'CEC_RSF'

    GO

    IF OBJECT_ID('[dbo].[spGet_Rec_Count]') IS NOT NULL

    DROP FUNCTION [dbo].[spGet_Rec_Count]

    GO

    CREATE FUNCTION dbo.spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @count bigint

    -- Add the T-SQL statements to compute the return value here

    select @count = count(*) from [member_score] where source_tbl = @source_tbl;

    -- Return the result of the function

    RETURN @count

    END

    GO

    select dbo.spGet_Rec_Count('ACA_RSF') As TheResults

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Your function should be schema-qualified too.

    And prefixing it with sp is very confusing!

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I caught that 'sp' instead of 'fn' and changed that in my code. Oh crap, the problem was that I had two different versions because of that 'sp' vs 'fn', and kept calling the one not updated.

    Thanks for that pointer. Sorry for the bother. Thanks for the other corrections.

  • DSNOSPAM (6/16/2015)


    I caught that 'sp' instead of 'fn' and changed that in my code. Oh crap, the problem was that I had two different versions because of that 'sp' vs 'fn', and kept calling the one not updated.

    Thanks for that pointer. Sorry for the bother. Thanks for the other corrections.

    No problem. Glad to help.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I think the problem was that @tbl vs @source_tbl. I was copying code from somewhere else (where I was forced to use dynamic sql for a table name in a from statement ) and didn't check my details.

  • For best performance, get rid of all unnecessary variables in functions.

    Edit: Changed COUNT to COUNT_BIG based on return data type.

    CREATE FUNCTION dbo.spGet_Rec_Count

    (

    @source_tbl varchar(100)

    )

    RETURNS bigint

    AS

    BEGIN

    RETURN (

    SELECT COUNT_BIG(*)

    FROM [member_score]

    WHERE source_tbl = @source_tbl

    )

    END

    GO

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks.

  • For even better performance I strongly suggest that you consider turning it into an Inline Table Valued Function and use SCHEMABINDING.

    There are many issues with scalar udfs including the fact that they eliminate the possibility of a parallel query plan. Some of the best advice I have received on SSC is this technique. It's not 100% guaranteed that the function will be faster but my experience has been that 90% of the functions I have changed from scalar to inline table value functions have become faster; 100's of times faster in some cases. You will need to get comfortable with APPLY (see the link below).

    Here's the code.

    CREATE FUNCTION dbo.spGet_Rec_Count(@source_tbl varchar(100))

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    (

    SELECT COUNT_BIG(*)

    FROM [member_score]

    WHERE source_tbl = @source_tbl

    )

    GO

    Here's some good reading on this topic:

    How to Make Scalar UDFs Run Faster (SQL Spackle)[/url]

    Understand Cross Apply (Part 1)[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • I'll check into that.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply