Solved

Stored Procedure to iterate through a dataset

Posted on 2008-10-29
9
760 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:digitalwav
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22832045
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
 
LVL 6

Assisted Solution

by:openshac
openshac earned 100 total points
ID: 22832131
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
 
LVL 5

Expert Comment

by:jfmador
ID: 22832332
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
 
LVL 1

Author Comment

by:digitalwav
ID: 22832353
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 100 total points
ID: 22832424
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
 
LVL 5

Accepted Solution

by:
jfmador earned 300 total points
ID: 22832432
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
 
LVL 9

Expert Comment

by:jamesgu
ID: 22832461
jfmador was right

should be tTimeSheet.DateSubmitted is null instead of tUser.UserKey Is Null in my query
0
 
LVL 1

Author Closing Comment

by:digitalwav
ID: 31511212
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
 
LVL 5

Expert Comment

by:jfmador
ID: 22832540
what a team! it seems that we look at the sames questions James :)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now