Exec variable functions inside SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Exec variable functions inside SP

Author

Topic

saesler
Starting Member

2 Posts

Posted - 2011-08-06 : 11:10:20

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.

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:


ALTER PROCEDURE [dbo].[usp_StatisticDetail_S_byProgramSection]
	@ProgramSectionID SMALLINT,
	@RecordID INT = NULL OUTPUT
AS
	DECLARE @FunctionValue VARCHAR(20)
	DECLARE @Function VARCHAR(100)
	DECLARE @StatisticName VARCHAR(100)
	
	DECLARE StatisticCursor CURSOR FOR
		SELECT tbStatisticDetail.Name, tbStatisticDetail.FunctionName
		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, @Function
WHILE @@FETCH_STATUS = 0
BEGIN
	SET NOCOUNT ON;
	IF @RecordID IS NULL 
		BEGIN
			SET @Function = @Function 
		END
	ELSE
		BEGIN
			SET @Function = @Function + ' ' + @RecordID
		END 
	EXEC @FunctionValue =  @Function 
	SELECT @StatisticName AS 'Statistic', @FunctionValue AS 'Value'
	FETCH NEXT FROM StatisticCursor 
		INTO @StatisticName, @Function
	
END
DEALLOCATE StatisticCursor

For now, all functions are designed to return an INT.

First, Am I heading down the right path and not turning this into a complete performance disaster?
Second, this actually runs fine as is, but when I attempt to run in my VB code, I get a conversion failed error when attempting to fill a table adapter.

Thanks In advance! Any help is greatly appreciated!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-06 : 12:03:07

What is this supposed to be returning to the client? If you could provide an example of the expected output - we can show you how to do this without using a cursor.

And yes, using a cursor is not the way you want to do this - because that will definitley end up being a performance disaster.

As this is written now, one of the problems you are going to have is the issue with multiple resultsets being returned from the stored procedure. Each loop is a different resultset - where you probably only need a single resultset with all of the values.

Jeff

saesler
Starting Member

2 Posts

Posted - 2011-08-06 : 12:55:21

Getting rid of the cursor would be great. Here is the structure of tbStatistics:
ID INT
Name VARCHAR
FunctionName VARCHAR --Contains the UDF name to retrieve the function value
ProgramSectionID INT --Contains id relating to section of the program where the statistic is used

 
ID      Name                    FunctionName                   ProgramSectionID
1 	Active User Count 	uf_UserDetail_CountActive 	2
2 	Total Users in System 	uf_UserDetail_CountAll 		2
3 	Active Properties 	uf_PropertyDetail_CountActive 	5
4 	Average Property Rent 	uf_PropertyDetail_AverageRent 	5

At this point when I execute the SP in SSMS:


EXEC usp_StatisticsDetail_S_byProgramID 2

I get the Results I expect:


FunctionName 	Value
Active User Count 	2
Total Users in System 	10

However, when I attempt to fill the Table Adapter in my VB.NET (VS 2010) Application


Me.Usp_StatisticDetail_S_byProgramSectionTableAdapter.GetData(c_intSectionID, vbNull)

I am returned with the error:

quote:

Conversion failed when converting the varchar value 'uf_UserDetail_CountActive ' to data type int.

Thanks for the help, this one is really stumping me.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources