How to get total rows return by Stored Procedure using Userdefined Function in SQL SERVER 2005

Question

My function

ALTER FUNCTION GetProductCount 
(@CatID int)
RETURNS int
AS
BEGIN

DECLARE @return_value int

EXEC    @return_value = [dbo].[USP_Products_GetList]
        @ProductName = NULL,
        @ProductID = NULL,
        @Description = NULL,
        @CatID = @CatID,
        @CatName = NULL,
        @Price1 = NULL,
        @Price2 = NULL,
        @SizeID = NULL,
        @IsNew = NULL,
        @InActive = NULL,
        @SortBy = NULL,
        @SortType = NULL

return @return_value
end

Function Execution

GetProductCount 1

Msg 557, Level 16, State 2, Procedure GetProductCount, Line 9 Only functions and some extended stored procedures can be executed from within a function.

Question

I want to find total no. of rows result by that stored procedure and return by this function. What should i do now? Solution please.........

Answer 1

You can't call user defined stored procedures from a function at all.

Maybe you could refactor the stored procedure logic into a TVF (preferably an inline one) then do a SELECT COUNT(*) FROM that?

Edit: Following your update this looks quite a viable option. You could create a new TVF as below with the contents of the stored proc but minus the order by stuff

CREATE FUNCTION dbo.fn_Products_GetList 
(   
@ProductName varchar(50),
@ProductID varchar(50),
....
@InActive bit
)
RETURNS TABLE 
AS
RETURN 
(
SELECT ProductID, ProductName,... 
                    or Price between @Price1 AND @Price2)

)

Change your stored procedure to select from that

SELECT ProductID, ProductName,... 
FROM dbo.fn_Products_GetList(@ProductName, @ProductID ... @InActive) 
ORDER BY 
CASE @SortType 
    WHEN 'desc' THEN  ...

Then in your scalar UDF you could do a

 SELECT COUNT(*) FROM 
 dbo.fn_Products_GetList(@ProductName, @ProductID ... @InActive) 

By the way off topic to this question but if your tables get at all big I can imagine your current all in one stored procedure will likely end up getting pretty slow as it contains lots of non sargable constructs.

Answer 2

you can't do it from a UDF unless you use a loopback linked server query hack, which is not recommended

Answer 3

My Stored Procedure which return rows according to category id = 1

USE [StoreDB]
GO
/****** Object:  StoredProcedure [dbo].[USP_Products_GetList]    Script Date: 08/21/2010 03:01:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[USP_Products_GetList]

@ProductName varchar(50),
@ProductID varchar(50),
@Description varchar(50),
@CatID varchar(50),
@CatName varchar(50),
@Price1 int,
@Price2 int,
@SizeID int,
@IsNew bit,
@InActive bit,
@SortBy varchar(50),
@SortType varchar(50)

AS

SELECT ProductID, ProductName, Price Price, PriceID, [Description], Size, SizeID, IsNew, InActive, ISNULL(ImageName,'Nophoto.png') AS ImageName, ImageTitle
FROM (   
        SELECT DISTINCT Products.ProductID, ProductName, MAX(Price) Price, PriceID, [Description], Size, Sizes.SizeID, IsNew, InActive, ImageName, ImageTitle FROM (SELECT * FROM Products WHERE (@InActive is null or @InActive = InActive  ) AND ( @IsNew is null or @IsNew = IsNew )) Products
            INNER JOIN  ProductCategory
                on Products.ProductID = ProductCategory.ProductID 
            LEFT OUTER JOIN (SELECT * FROM ProductImages 
                            WHERE ( @ProductID is null or ProductID like '%' + @ProductID + '%' ) AND Isthumb = 'true'
                        ) ProductImages
                ON Products.ProductID = ProductImages.ProductID                 
            INNER JOIN  (
                            SELECT CatID FROM Categories 
                                WHERE 
                                    ( (@CatID is null or @CatID = 0) or @CatID = CatID ) and
                                    ( @CatName is null or CatName  like '%' +  @CatName + '%' )
                        ) Categories 
                on ProductCategory.CatID = Categories.CatID         
            INNER JOIN ( 
                            SELECT Prices.ProductID, Prices.Price, Prices.PriceID, Prices.SizeID  FROM Prices 
                                INNER JOIN (
                                    SELECT ProductID, MAX(Price) Price from Prices WHERE PriceID IN 
                                            ( SELECT MAX(PriceID) FROM Prices 
                                            GROUP BY ProductID , SizeID)
                                    GROUP BY ProductID )  Prices_
                                ON Prices.ProductID = Prices_.ProductID AND Prices.Price = Prices_.Price                     
                        ) as Prices 
                on Prices.ProductID = Products.ProductID 
                inner join  Sizes 
                on Sizes.SizeID = Prices.SizeID 
        GROUP BY ProductName, Products.ProductID, Price, PriceID, [Description] ,Size, Sizes.SizeID, IsNew, InActive, ImageName, ImageTitle 
) AS OrderProduct WHERE 
                    ( @ProductName is null or ProductName like '%' + @ProductName + '%' ) and
                    ( @ProductID is null or ProductID like '%' + @ProductID + '%' ) and
                    ( @Description is null or [Description] like '%' + @Description + '%' ) and
                    ( (@SizeID is null or @SizeID = 0)or SizeID = @SizeID ) and
                    ( @Price1 is null or Price between @Price1 AND @Price2)
ORDER BY 
CASE @SortType 
    WHEN 'desc' THEN  
    CASE @SortBy         
        WHEN 'ProductName' THEN ProductName        
        END  
    END 
    DESC, 
CASE @SortType 
    WHEN 'desc' THEN  
    CASE @SortBy         
        WHEN 'ProductID' THEN ProductID
        WHEN 'Price' THEN Price          
        END  
    END 
    DESC,     
CASE @SortType 
    WHEN 'asc' THEN  
    CASE @SortBy         
        WHEN 'ProductName' THEN ProductName        
        END  
    END 
    ASC, 
CASE @SortType 
    WHEN 'asc' THEN  
    CASE @SortBy         
        WHEN 'ProductID' THEN ProductID
        WHEN 'Price' THEN Price          
        END  
    END 
    ASC