April 19, 2016 at 9:39 pm
I'm trying to create a user-defined function that will count the number of stores in each state and display the count for the one with the highest number. I tested out the SELECT statement in a separate query and received the results that I'm looking for.
However, when attempting to insert that in a CREATE FUNCTION, I get the error code "MSG 444 - Select statements included within a function cannot return data to a client". I have no experience with creating functions, and the little coding I have is the result of numerous Google searches, so I'm at an absolute loss as to how to fix this. Any help would be much appreciated.
My code is as follows:
CREATE FUNCTION hstore_num ()
RETURNS int
BEGIN
DECLARE @Result int
SELECT 'Highest Store Count' = MAX(in_view.temp)
FROM (SELECT temp = COUNT(state)
FROM stores
GROUP BY state) AS in_view
RETURN @Result
END;
April 19, 2016 at 10:06 pm
This little test function works for me.
CREATE FUNCTION test()
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = 123
RETURN @Result
END;
select dbo.test()
I noticed that you are not assigning anything to @Result and you were missing the 'AS' keyword. Make these changes and see what happens.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 19, 2016 at 10:59 pm
I appreciate the assistance.
Am I to assume the "123" is where I insert the
MAX(in_view.temp)
FROM (SELECT temp = COUNT(state)
FROM stores
GROUP BY state) AS in_view
On a side note, I did manage to get the function to compile correctly by converting to a table-value function as opposed to the scalar I was originally using.
April 19, 2016 at 11:03 pm
You arent actually populating your return variable
Try this.
CREATE FUNCTION hstore_num ()
RETURNS int
BEGIN
DECLARE @Result int
SELECT @Result = MAX(in_view.temp)
FROM (SELECT temp = COUNT(state)
FROM stores
GROUP BY state) AS in_view
RETURN @Result
END;
April 20, 2016 at 8:42 am
ajwilliams504 (4/19/2016)
Am I to assume the "123" is where I insert the
MAX(in_view.temp)FROM (SELECT temp = COUNT(state)
FROM stores
GROUP BY state) AS in_view
Of course!
I don't have any of your tables and you did not provide test ddl and data to test against. So I just put in a constant.
The Select @Result = . . . is just to show you that what you are trying to do can work. You just need to make the proper assignment.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 20, 2016 at 8:57 am
A word of caution. Scalar functions can be horrible for query performance. If you're planning on using that function within another query, you're likely to end up with a really badly performing query as a result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2016 at 9:01 am
Scalar functions are usually a performance problem because they execute row by row. The best option for performance when you need parameters is an inline table valued function which is written like thisÑ
CREATE FUNCTION hstore_num ()
RETURNS TABLE
AS
RETURN
SELECT TOP 1 'Highest Store Count' = COUNT(state)
FROM stores
GROUP BY state
ORDER BY 'Highest Store Count' DESC;
GO
Of course, with no parameters, you could use a view instead
CREATE VIEW hstore_num
AS
SELECT TOP 1 'Highest Store Count' = COUNT(state)
FROM stores
GROUP BY state
ORDER BY 'Highest Store Count' DESC;
GO
Both of them would be called as a table, but the function needs to use parenthesis.
SELECT *
FROM hstore_num() --
Hope this is clear.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply