June 16, 2015 at 10:20 am
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?
June 16, 2015 at 10:25 am
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
June 16, 2015 at 10:29 am
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.
June 16, 2015 at 10:31 am
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
June 16, 2015 at 10:39 am
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?
June 16, 2015 at 11:05 am
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
June 16, 2015 at 11:05 am
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
June 16, 2015 at 11:06 am
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
June 16, 2015 at 11:15 am
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.
June 16, 2015 at 11:36 am
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
June 16, 2015 at 11:43 am
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.
June 17, 2015 at 12:01 pm
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".
June 17, 2015 at 12:38 pm
Thanks.
June 17, 2015 at 1:17 pm
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]
-- Itzik Ben-Gan 2001
June 17, 2015 at 2:34 pm
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