[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

How do I execute a stored procedure for a list of selected values?

How do I write and SQL statement to pull a list of ID's and then execute a stored procedure against each of those IDs? Specifically I need the Scope_Identity() ID for each record after it get's inserted. However, the insert is done, based on a list of different IDs. So the order of events would be. 1. Select a list of IDs, 2. Do an insert into a table using those ID's (and some other information), 3. Execute a stored procedure using the Scope_Identity generated after each insert was performed. So i need a loop and then an inner loop. How do I do this in T-SQL?
0
Michael Sterling
Asked:
Michael Sterling
  • 7
  • 3
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
Stored the list of IDs pulled into a table variable.
Write a while loop which should loop through the maximum ID in the loop. (In table variable, if you have a column with IDENTITY, it would be easy)
Inside the loop, you can execute the remaining two queries - to insert into another table and execute a stored procedure

Raj
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thanks...I will try that...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
i'm attaching my T-SQL. this is what it looks like in my test environment. so the select into my temp table adds 32 records, based on the data in the utathleteinfo table. then after that my T-SQL is crap as it adds roughly 16,000 records to the utinout table, instead of 32. also it's not executing the stored procedure either. what's wrong with my T-SQL?
/****** Script for SelectTopNRows command from SSMS  ******/
--BEGIN TRANSACTION 
DECLARE @TimeIn DATETIME2 = '2011-10-21 00:03:00.000' --user input
DECLARE @TimOut DATETIME2 = '2011-10-21 01:02:00.000' --user input
DECLARE @SignInSignOutNotes VARCHAR = '' --user input
DECLARE @ProgramAttended VARCHAR = '' --user input
DECLARE @InOutUid INT
DECLARE @SportId INT = 1 --user input
DECLARE @NumRecs INT = 0

DECLARE @StudentIDs table (StudentId VARCHAR(8), StudentName VARCHAR(50))

INSERT INTO @StudentIDs (StudentId, StudentName)
	SELECT [AthleteUid], [AthleteName]
	FROM [studytimetrackerprime].[dbo].[utathleteinfo]
	WHERE SportUid = @SportId 
	
--SELECT * FROM @StudentIDs	
WHILE @NumRecs < (SELECT COUNT(*) FROM @StudentIDs)
BEGIN
	INSERT INTO utinout (StudentId, TimeIn, TimeOut, StudentName, SignInOutNotes, ProgramAttended)
	
	SELECT DISTINCT StudentId, @TimeIn, @TimOut, StudentName, @SignInSignOutNotes, @ProgramAttended 
	FROM @StudentIDs s INNER JOIN utathleteinfo u ON s.StudentId = u.AthleteUid
		
	SET @InOutUid = Scope_Identity() 
	SET @NumRecs = @NumRecs + 1
	
	EXEC UpdateAllTimeInfo @InOutUid				
END 
--ROLLBACK TRANSACTION

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rajkumar GsSoftware EngineerCommented:
If I am right, your loop may be executing without any stop.

declare @table table(
id int identity(1,1),
... Your other column
)
As I mention, have an identity column like this

then get maximum of this unique id into one variable. Use this in while condition.

declare a variable with value 1. Increment this value in while loop.
This variable < max variable should be while loop criteria

I have access to only phone now. Correct your query and try again

Raj

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
will do...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
ok...this is what i have now...it's better as it's not adding as many records. it's adding 32 * 32 (1024) records now and only executing the stored procedure for the 32nd record each time. so only one person id is getting the stored procedure ran against it...
/****** Script for SelectTopNRows command from SSMS  ******/
--BEGIN TRANSACTION 
DECLARE @TimeIn DATETIME2 = '2011-10-21 00:03:00.000' --user input
DECLARE @TimOut DATETIME2 = '2011-10-21 01:02:00.000' --user input
DECLARE @SignInSignOutNotes VARCHAR = '' --user input
DECLARE @ProgramAttended VARCHAR = '' --user input
DECLARE @InOutUid INT
DECLARE @SportId INT = 1 --user input
DECLARE @MaxId INT 
DECLARE @Inc INT = 0

DECLARE @StudentIDs table (id int identity(1,1), StudentId VARCHAR(8), StudentName VARCHAR(50))

INSERT INTO @StudentIDs (StudentId, StudentName)
	SELECT [AthleteUid], [AthleteName]
	FROM [studytimetrackerprime].[dbo].[utathleteinfo]
	WHERE SportUid = @SportId 
	
SELECT Max(id) FROM @StudentIDs	
WHILE @Inc < @MaxId
BEGIN
	SET @Inc = @Inc + 1
	INSERT INTO utinout (StudentId, TimeIn, TimeOut, StudentName, SignInOutNotes, ProgramAttended)
	
	SELECT DISTINCT StudentId, @TimeIn, @TimOut, StudentName, @SignInSignOutNotes, @ProgramAttended 
	FROM @StudentIDs s LEFT OUTER JOIN utathleteinfo u ON s.StudentId = u.AthleteUid
		
	 SET @InOutUid = Scope_Identity() 
	
	EXEC UpdateAllTimeInfo @InOutUid				
END 
--ROLLBACK TRANSACTION

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Mikes - I have made some corrections to your query. Please see the comments inside.
/****** Script for SelectTopNRows command from SSMS  ******/
--BEGIN TRANSACTION 
DECLARE @TimeIn DATETIME2 = '2011-10-21 00:03:00.000' --user input
DECLARE @TimOut DATETIME2 = '2011-10-21 01:02:00.000' --user input
DECLARE @SignInSignOutNotes VARCHAR = '' --user input
DECLARE @ProgramAttended VARCHAR = '' --user input
DECLARE @InOutUid INT
DECLARE @SportId INT = 1 --user input
DECLARE @MaxId INT 
DECLARE @Inc INT = 0

DECLARE @StudentIDs table (id int identity(1,1), StudentId VARCHAR(8), StudentName VARCHAR(50))

INSERT INTO @StudentIDs (StudentId, StudentName)
	SELECT [AthleteUid], [AthleteName]
	FROM [studytimetrackerprime].[dbo].[utathleteinfo]
	WHERE SportUid = @SportId 
	
SELECT @MaxId = Max(id) FROM @StudentIDs -- CORRECTION. It seems that Max(id) is not stored to @MaxId
	
WHILE @Inc < @MaxId
BEGIN
	SET @Inc = @Inc + 1
	INSERT INTO utinout (StudentId, TimeIn, TimeOut, StudentName, SignInOutNotes, ProgramAttended)
	
	SELECT DISTINCT StudentId, @TimeIn, @TimOut, StudentName, @SignInSignOutNotes, @ProgramAttended 
	FROM @StudentIDs s LEFT OUTER JOIN utathleteinfo u ON s.StudentId = u.AthleteUid
	WHERE s.id = @Inc -- missed this where condition.  -- CORRECTION
		
	 SET @InOutUid = Scope_Identity() 
	
	EXEC UpdateAllTimeInfo @InOutUid				
END 
--ROLLBACK TRANSACTION

Open in new window


Raj
0
 
LowfatspreadCommented:
1 .  are you clear on what distinct does?
      it applies to the whole row not just the first column...

2. what does the stored procedure do... does it affect the utatleteinfo table (or others)?

3. you appear to already have the information you are inserting into the utinout table...
    why do you need to join it to the utathleteinfo table?

4. can there be duplicate studentids present in the @studentids table since your initial population doesn't seem
    to allow/disallow this...?

5. the insert within your loop could validly insert more than one row (unless the data constrains against that)
    so scope identity will not do what you seem to desire (you only get the last? identity value)


you may need to use the MS SQL Server OUTPUT Clause extension to get the identity values generated into a table you can process....
alternatively a trigger on the utinout table may better provide what you wish to achieve (put the exec updatealltimeinfo into that)

could you provide some sample data and expected results?

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thank you...I did wind up solving this.I'll post my solution in about an hour or two...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
my solution is below. I know it's been a while but i wanted to wrap this up. not sure who provided the most help so i will split the points.
CREATE PROCEDURE [dbo].[UpdateAllTimeInfo]
(
	@InOutUid int
)
AS
BEGIN
        DECLARE @StudentID VARCHAR(8)
        DECLARE @TotalMinutes INT
        DECLARE @SemesterTotal INT
        DECLARE @StartWeekDate DATETIME
        DECLARE @EndWeekDate DATETIME
                        
        UPDATE utInOut SET TotalMinutes = DATEDIFF(Minute, TimeIn, TimeOut), 
        TotalTime = (CAST(DATEDIFF(Minute, TimeIn, TimeOut)/ 1440 as VARCHAR(12)) + ':' +
        CONVERT(CHAR(8), DATEADD(MINUTE, DATEDIFF(Minute, TimeIn, TimeOut) % 1440, '00:00'), 108))
        WHERE InOutUid = @InOutUid

        SELECT @StartWeekDate = StartDate FROM utApplicationControls
        SELECT @EndWeekDate = EndDate FROM utApplicationControls
        
        SELECT @StudentID = StudentID FROM utinout WHERE InOutUiD = @InOutUid
        SELECT @TotalMinutes = SUM(TotalMinutes) 
            FROM utinout 
        WHERE StudentID = @StudentID 
            AND TimeIn >= (SELECT StartDate FROM utApplicationControls) 
            AND TimeOut <= (SELECT EndDate FROM utApplicationControls) 
            AND TimeOut IS NOT NULL
        SELECT @SemesterTotal = SUM(TotalMinutes) FROM utinout WHERE StudentID = @StudentID
        
        UPDATE utathleteinfo SET TotalMinutes = @TotalMinutes,
        SemesterTotal = @SemesterTotal
        WHERE AthleteUid = @StudentID

        UPDATE utathleteinfo SET WeekTotal =  (CAST(@TotalMinutes / 1440 as VARCHAR(12)) + ':' +
        CONVERT(CHAR(8), DATEADD(MINUTE, @TotalMinutes % 1440, '00:00'), 108)),
        SemesterTime = (CAST(@SemesterTotal / 1440 as VARCHAR(12)) + ':' +
        CONVERT(CHAR(8), DATEADD(MINUTE, @SemesterTotal % 1440, '00:00'), 108))
        WHERE AthleteUid = @StudentID
END
GO

Open in new window

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thanks for your input and sorry about the slow turn around
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now