Stored Procedure to iterate through a dataset
I have the start of my procedure attached. The idea is to get all the user ids and then loop through another table using that id to find records. I kind of have an outline written but it's telling me that I can't have column names in the BEGIN/END area.
Any ideas on how I can do this? I need all the columns listed in the select statement in the loop. Ultimately I'd like to do something when a record is found/not found in the loop but I need to be able to get the records first.
Any ideas on how I can do this? I need all the columns listed in the select statement in the loop. Ultimately I'd like to do something when a record is found/not found in the loop but I need to be able to get the records first.
USE [CMDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sptAMITimeSheetIncompleteNotify]
(@StartDate date)
AS
DECLARE @UserKey int
DECLARE UserRecords CURSOR FOR (SELECT tUser.UserKey FROM tUser (NOLOCK))
OPEN UserRecords
FETCH NEXT FROM UserRecords INTO @UserKey
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT tUser.FirstName, tUser.LastName, tTimeSheet.Status, tTimeSheet.StartDate,
tTimeSheet.EndDate, tTimeSheet.DateCreated, tTimeSheet.DateSubmitted, tTimeSheet.DateApproved,
tUser.UserID, tUser.Email, tUser.UserKey
FROM tUser with (nolock) INNER JOIN tTimeSheet ON tUser.UserKey = tTimeSheet.UserKey
WHERE (tUser.UserKey = @UserKey) AND (tTimeSheet.DateSubmitted Is Null) and (tTimeSheet.StartDate = @StartDate)
Print tUser.FirstName, tUser.LastName, tTimeSheet.Status, tTimeSheet.StartDate,
tTimeSheet.EndDate, tTimeSheet.DateCreated, tTimeSheet.DateSubmitted, tTimeSheet.DateApproved,
tUser.UserID, tUser.Email, tUser.UserKey
FETCH NEXT FROM UserRecords INTO @UserKey
END
CLOSE UserRecords
DEALLOCATE UserRecords
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
I don't really understand what you are trying to do with your cursor, Using a cursor that loop on each tUser to pass the UserKey in a query using the table tUser will result the same than just running the query once without using the where (tUser.UserKey = @UserKey) and will be more performant because you won't run the query several times, if you need a cursor to print out these information add a cursor on this query instead
SELECT tUser.FirstName, tUser.LastName, tTimeSheet.Status, tTimeSheet.StartDate,
tTimeSheet.EndDate, tTimeSheet.DateCreated, tTimeSheet.DateSubmitted, tTimeSheet.DateApproved,
tUser.UserID, tUser.Email, tUser.UserKey
FROM tUser INNER JOIN tTimeSheet ON tUser.UserKey = tTimeSheet.UserKey
WHERE (tTimeSheet.DateSubmitted Is Null) and (tTimeSheet.StartDate = @StartDate)
I don't really understand what you are trying to do with your cursor, Using a cursor that loop on each tUser to pass the UserKey in a query using the table tUser will result the same than just running the query once without using the where (tUser.UserKey = @UserKey) and will be more performant because you won't run the query several times, if you need a cursor to print out these information add a cursor on this query instead
SELECT tUser.FirstName, tUser.LastName, tTimeSheet.Status, tTimeSheet.StartDate,
tTimeSheet.EndDate, tTimeSheet.DateCreated, tTimeSheet.DateSubmitted, tTimeSheet.DateApproved,
tUser.UserID, tUser.Email, tUser.UserKey
FROM tUser INNER JOIN tTimeSheet ON tUser.UserKey = tTimeSheet.UserKey
WHERE (tTimeSheet.DateSubmitted Is Null) and (tTimeSheet.StartDate = @StartDate)
ASKER
The trouble is I need to know which user has no timesheet record and which ones have one but the datesubmitted is null. Will the query you just suggested do that?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jfmador was right
should be tTimeSheet.DateSubmitted is null instead of tUser.UserKey Is Null in my query
should be tTimeSheet.DateSubmitted is null instead of tUser.UserKey Is Null in my query
ASKER
Excellent! Between all of you I got the record set I needed. I think I can figure out the rest of the logic. this is much easier than the old fashion loop method. Thanks!
what a team! it seems that we look at the sames questions James :)
you may have to declare a variable for the column value, and set the value in your select statement
then print out