• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

How do I execute a stored procedure against a table?

I need to write some SQL to execute a stored procedure against a column (it's value) in row of data from a table based on the value of a different column in that same row, how do i do this...
0
Michael Sterling
Asked:
Michael Sterling
1 Solution
 
tigin44Commented:
can you make this more clear?
0
 
JestersGrindCommented:
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

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
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?
0
Technology Partners: 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!

 
tigin44Commented:
DECLARE @val1      int
DECLARE @val2      int

SET @val1 = someValue


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

EXEC spYourSPName @val2
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
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

Open in new window

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
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?
0
 
dougaugCommented:
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).
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
ok...i will, and for my own information, is that the same as "INNER JOIN"?
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
k, that didn't work and the stored procedure still isn't executing for each insert. it's still executing every 32nd insert...
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@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
0
 
Ephraim WangoyaCommented:
try
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 
	INNER JOIN utathleteinfo u ON s.StudentId = u.AthleteUid
	WHERE s.id = @inc
		
	SET @InOutUid = Scope_Identity() 
	
	EXEC UpdateAllTimeInfo @InOutUid
	
	SELECT @Inc = @Inc + 1				
END 
GO
--ROLLBACK TRANSACTI

Open in new window

0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thank you...thanks a bunch...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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