How do I execute a stored procedure against a table?

can you make this more clear?

Sounds like you are trying to use a stored procedure the same way that you use a function.  If this is one value being passed, you should be able to capture that value into a variable and pass it on to your stored procedure.  If you are talking about multiple values, you'll have to capture those values into a temp table or table variable and then loop through the table and execute the stored procedure once for each record.

Greg

i have a table...in that table are certain rows that that i want to execute a stored procedure against, (against a column of a row) based on the a value in one of the other columns in that row, for each row in the table. so...the theory goes something like:

in table A,
 if column B = x
   then exec myStoredProc column C

does that help?

DECLARE @val1      int
DECLARE @val2      int

SET @val1 = someValue


IF EXISTS(      SELECT @val2 = col2
                  FROM yourTable
                  WHERE col1 = @val1)

EXEC spYourSPName @val2

the attached code is what i was trying to do but it was failing in the following ways:

- it was inserting too many rows
- it was not executing the stored procedure except for against the last record.

the first insert, inserts 32 records into my temp table. the second insert, inserts 32 * 32 records for some reason and executes the stored procedure only on the 32nd record each time.

if i remove the while look i get the 32 records inserted 1 time, and the stored proc only runs against the 32nd record.

i need it to insert the 32 records once, and execute the stored procedure after each insert.

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 = 1

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	
WHILE @Inc < @MaxId
BEGIN
	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
	
	SELECT @Inc = @Inc + 1				
END 
GO
--ROLLBACK TRANSACTI

Select allOpen in new window

so my plan was to just let it insert the 32 records i need, then go back in run something like what tigin44 just sent me (by the way, thanks i will try that) against my table. i'd prefer to get my SQL working but i'm looking for alternatives to "keep things moving" as it would be. Any clue as to why my SQL is behaving the way it is?

Maybe you are inserting 32 records at each loop iteration because you are using a left join in the clause below:

      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

Since in your @StudentIDs table you have 32 records, at each select you execute using left outer join (and @StudentIDs is the outer table) you'll always get 32 records.

Try to replace the left outer join with an equi join (just JOIN).

ok...i will, and for my own information, is that the same as "INNER JOIN"?

k, that didn't work and the stored procedure still isn't executing for each insert. it's still executing every 32nd insert...

@tigin44: there is a syntax error at the first "=" in your SQL. not sure why

DECLARE @val1      int
DECLARE @val2      int

SET @val1 = someValue


IF EXISTS(      SELECT @val2 = col2 <--here i'm getting a syntax error for the "=" not sure why
                  FROM yourTable
                  WHERE col1 = @val1)

EXEC spYourSPName @val2

thank you...thanks a bunch...