Help Writing SP to call multiple UDFs

[xiong]

First off, I am relatively new to SQL Development and fairly unfamiliar with all that MS SQL server can offer. I'll try to explain as best I can what I'm attempting to do, and how I've proceeded thus far.

What I'm going to attempt to produce in my application is a page that shows current statistics for the object shown, and possibly for the specific record shown. For example, if the USERS section is opened, one of the statistics show may be a count of all active users on the system, and/or the number of new records created by a specific user.

Current Table Structure:

tbStatisticsDetail
tbStatisticsDetail.ID
tbStatisticsDetail.Name
tbStatisticsDetail.ProgramSectionID
tbStatisticsDetail.StoredProcedure -- the name of the FUNCTION (not SP)
tbStatisticsDetail.IsInactive

I've created a Stored Procedure that accepts two parameters as input (ProgramSection, RecordID)

Now, I need to loop through the tbStatisticsDetail table, select only those records that relate to the ProgramSection, and are not Inactive.

This is what I've got so far:

PROCEDURE [dbo].[usp_StatisticDetail_S_byProgramSection]
@ProgramSectionID SMALLINT,
@RecordID INT = NULL OUTPUT
AS
DECLARE @StatisticStoredProcValue VARCHAR(20)
DECLARE @StatisticStoredProc VARCHAR(100)
DECLARE @StatisticName VARCHAR(100)
DECLARE StatisticCursor CURSOR FOR
SELECT tbStatisticDetail.Name, tbStatisticDetail.StoredProcedure, 0
FROM tbStatisticDetail
INNER JOIN tbProgramSections
ON ProgramSectionID = tbProgramSections.ID
WHERE tbStatisticDetail.IsInactive = 0
AND ProgramSectionID = @ProgramSectionID
ORDER BY Name ;

OPEN StatisticCursor
FETCH NEXT FROM StatisticCursor INTO @StatisticName, @StatisticStoredProc, @StatisticStoredProcValue
WHILE @@FETCH_STATUS = 0

BEGIN
EXEC @StatisticStoredProc @RecordID = @StatisticStoredProcValue OUTPUT
SET NOCOUNT ON;

FETCH NEXT FROM StatisticCursor
INTO @StatisticName, @StatisticStoredProc, @StatisticStoredProcValue
END

DEALLOCATE StatisticCursor

First, Am I heading down the right path and not turning this into a complete performance disaster?
Second, How do I handle cases where my UDFs may not contain the @RecordID parameter?

Thanks In advance! Any help is greatly appreciated!



"Only the educated are free.

 

[xiong]

Turns out, that the problem was that I was trying to pass vbNull as a parameter for @RecordID. Passing Nothing resolved the issue.


"Only the educated are free.

 

[markros]

Yes, complete discussion is here

http://social.msdn.microsoft.com/Fo...l/thread/e75341be-b04a-41ca-851a-07a5737a137d

PluralSight Learning Library