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.


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

Open in new window

LVL 1
digitalwavIT Infrastructure ManagerAsked:
Who is Participating?
 
jfmadorConnect With a Mentor Commented:
you could use a left join and a CASE to determined if the Time sheet is submitted

The Case statement will return 0 (false) when date submitted is null, and the column value from tTimeSheet will be null since you are using a left join


SELECT  tUser.UserID, tUser.Email, tUser.UserKey, tUser.FirstName, tUser.LastName, 
CASE WHEN tTimeSheet.DateSubmitted is null THEN 0 ELSE 1 END as Submitted, 
tTimeSheet.Status, tTimeSheet.StartDate, tTimeSheet.EndDate, tTimeSheet.DateCreated, tTimeSheet.DateSubmitted, tTimeSheet.DateApproved,
FROM tUser LEFT JOIN tTimeSheet ON tUser.UserKey = tTimeSheet.UserKey AND tTimeSheet.StartDate = @StartDate

Open in new window

0
 
jamesguCommented:
you cannot print out a column name

you may have to declare a variable for the column value, and set the value in your select statement

then print out
0
 
openshacConnect With a Mentor Commented:
Try something like this
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
 
DECLARE @FirstName varchar(100),
		@LastName varchar(100),
		....
 
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)
 
        
		SELECT  @FirstName = tUser.FirstName, @LastName = tUser.LastName, ...
        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 @FirstName, @LastName, ...
 
FETCH NEXT FROM UserRecords INTO @UserKey
 
END
 
CLOSE UserRecords
 
DEALLOCATE UserRecords

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jfmadorCommented:
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)

0
 
digitalwavIT Infrastructure ManagerAuthor Commented:
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?
0
 
jamesguConnect With a Mentor Commented:
use this query


 SELECT  tUser.columns ... --put other columns here
              , case then (tUser.UserKey Is Null) then '1' else '0' end case --1- not submit, 0 submitted but date is null
FROM         tUser left JOIN tTimeSheet ON tUser.UserKey = tTimeSheet.UserKey
                  and tTimeSheet.DateSubmitted Is Null  and tTimeSheet.StartDate = @StartDate
0
 
jamesguCommented:
jfmador was right

should be tTimeSheet.DateSubmitted is null instead of tUser.UserKey Is Null in my query
0
 
digitalwavIT Infrastructure ManagerAuthor Commented:
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!
0
 
jfmadorCommented:
what a team! it seems that we look at the sames questions James :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.