Cursor vs Loops - SQL Server Forums

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 2005 Forums

Other SQL Server Topics (2005)

Cursor vs Loops

Author

Topic

rwiethorn
Starting Member

2 Posts

Posted - 2008-02-21 : 08:54:07

Hello all.
I'm mostly a VB.Net developer. I've been working on a intranet app that allows poeple in our company to self regisiter for access to Tools/Systems.

The data stucture: 1 Request with many Users requesting access.. Also on the same request, many applications requested for those same people.

After the application routes some 'Manager Reviews' and Updated the tblRequest, approving the request, I combine the Information into a tblRegistrations.

So there is a Matrix created.
The Users requested are (User1, User2, UserX....)
The Apps requested are (App1, App2, Appx....)

I created a Stored Proc that reads the List of USers, and inserts a record into tblRegistered for each App that was requested.
User1, App1
User1, App2
User1, App3
User1, Appx
User2, App1
User2, App2
User2, App3
User2, AppX
UserX, AppX
...., ....

I user 2 cursors, Nested.
The outer Cursor is the List of Users, and the inner Cusros is the list apps.

I appreciate if somone can show me how to do this with some other looping structure, or if not, examine the Decalrative statement of the Cursor, and define the propteries to make it most efficient, ie Static Local Forward Only, ect.

As I said, I don't DB Developer Experience.

Below is the copy of the Working Stored Proc:


ALTER PROCEDURE [dbo].[sp_Insert_Registration] 
	-- Add the parameters for the stored procedure here
			@Request_ID		INT
			,@SessionID		nvarchar(150)

AS

DECLARE @Request_user_FullName  nvarchar(150)
DECLARE @Request_User_IonName   nvarchar(150)
DECLARE @Request_App_ID         INT
DECLARE @Request_App_Role       nvarchar(50)
DECLARE @Reg_Date               DateTime

BEGIN TRY


--Local Scalar Values
Select @Reg_Date=Request_Date From dbo.tblRequest Where Request_ID=@Request_ID


--First Cursor

DECLARE curRequest_Users CURSOR LOCAL STATIC FOR
SELECT Request_User_FullName, Request_User_IonName
       From dbo.tblRequest_Users
       Where Request_ID =@Request_ID AND request_User_Session_ID=@SessionID

Open curRequest_Users


		--Second Cursor

            DECLARE curRequest_Applications CURSOR LOCAL Static FOR
            SELECT Request_App_ID, Request_App_Role
		       From dbo.tblRequest_Applications
                   Where Request_ID =@Request_ID
                       AND Request_Apps_SessionID=@SessionID

FETCH curRequest_Users INTO @Request_user_FullName, @Request_User_IonName
 WHILE @@FETCH_STATUS = 0
	BEGIN
		--Insert the Row Into tblRegistrations
		--Need to get the Application ID's the User(s) Has Been Approved For

		Open curRequest_Applications


		FETCH curRequest_Applications INTO @Request_App_ID, @Request_App_Role
			WHILE @@FETCH_STATUS = 0
				BEGIN
					Insert Into dbo.tblRegistrations
						(Request_ID
						,FUllName
						,IonName
						,Application_ID
						,Application_Role
						,Reg_Date
						,Approved
						,Approval_Date
						)
					Values (		
						@Request_ID
						,@Request_user_FullName
						,@Request_User_IonName
						,@Request_App_ID
						,@Request_App_Role
						,@Reg_Date
						,'True'
						,getdate()
						)
				FETCH curRequest_Applications INTO @Request_App_ID, @Request_App_Role
			
				END
				--Close the Inner Cursor
				CLOSE curRequest_Applications 

	FETCH curRequest_Users INTO @Request_user_FullName, @Request_User_IonName
		
	END




DEALLOCATE curRequest_Applications 

CLOSE curRequest_Users 
DEALLOCATE curRequest_Users 

	
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

END CATCH;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-21 : 08:58:07

INSERT tblTarget (UserID, AppID)
SELECT u.UserID, a.AppID
FROM tblUsers AS u
CROSS JOIN tblApplications AS a

E 12°55'05.25"
N 56°04'39.16"

rwiethorn
Starting Member

2 Posts

Posted - 2008-02-21 : 13:15:11

Peso,

Thanks for the info, I found it very helpfull. I applied the new select statement, and wrote a new stored Proc:

ALTER PROCEDURE [dbo].[sp_Insert_Registration_Cross_Join]
-- Add the parameters for the stored procedure here
@Request_ID INT
,@SessionID nvarchar(150)

AS

DECLARE @Request_user_FullName nvarchar(150)
DECLARE @Request_User_IonName nvarchar(150)
DECLARE @Request_App_ID INT
DECLARE @Request_App_Role nvarchar(50)
DECLARE @Reg_Date DateTime

declare @startproc datetime
declare @endproc datetime
declare @time integer

select @startproc = getdate()

BEGIN TRY

--Local Scalar Values
Select @Reg_Date=Request_Date
From dbo.tblRequest Where Request_ID=@Request_ID

BEGIN
Insert Into dbo.tblRegistrations
(Request_ID
,FUllName
,IonName
,Application_ID
,Application_Role
,Reg_Date
,Approved
,Approval_Date
)
SELECT
@Request_ID AS Request_ID
,RU.Request_User_FullName
,RU.Request_User_IonName
,RA.Request_App_ID
,RA.Request_App_Role
,@Reg_Date
,'True' AS Approved
,getdate()AS Approval_Date
FROM dbo.tblRequest_Users RU CROSS JOIN dbo.tblRequest_Applications RA
Where RU.Request_ID =2 AND RU.Request_User_Session_ID='TestSession'
AND RA.Request_ID =2 AND RA.Request_Apps_SessionID='TestSession'
ORDER by 1
END
select @endproc = getdate()

select @time = DATEDIFF(millisecond, @startproc, @endproc)

print 'Time Dif: ' + str(@time)

END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

END CATCH;

I added in Time tests and it runs much faster sometimes 3x than the previous stored proc.

Thanks again
rwiethonr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources