Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure to iterate through a dataset

Posted on 2008-10-29
9
Medium Priority
?
769 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 
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
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 400 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 1200 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

730 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