Looping thru params

[Deam]

I am trying to build a stored proc which will terminate all active employees from the selected Job BUT
Not from the selected Departments.
So the user goes through this task from the front-end and selects the JobDescription, DepartmentDescription
and a Date. When this task finishes the user hits the finish button which executes a stored proc. Since
the selected DepartmentCodes or JobCodes are more than one, I am running into issues. So eventually
instead of building an execute statement with one parameter, it builds a long one with all the Department
codes selected...which of course is wrong because the sp does not accept that many params. How should
I do it? This is how my sp looks:
Any help is greatly appreciated. Thanks in advance.

CREATE PROCEDURE [dbo].[MassTermination]

@EffectiveDate datetime,
@JobCode varchar,
@DepartmentCode varchar,
@TerminationReasonCode varchar
AS
SET NOCOUNT ON
CREATE TABLE #PersonTemp
(PersonGUID uniqueidentifier NOT NULL)
INSERT INTO #PersonTemp
SELECT Distinct tsh.PersonGUID
FROM TableStatusHistory tsh, TableJobHistory tjh, TableLocationHistory tlh
WHERE tsh.PersonGUID = tjh.PersonGUID
AND tsh.PersonGUID = tlh.PersonGUID
AND tsh.StatusCurrentFlag = 1
AND JobCode = @JobCode
AND DepartmentCode != @DepartmentCode

INSERT INTO TableStatusHistory
(PersonGUID,
StatusStartDate,
StatusCode
)
SELECT
u.PersonGUID,
@EffectiveDate,
'TERMINATED'
FROM TableStatusHistory s
INNER JOIN #PersonTemp u ON s.PersonGUID = u.PersonGUID WHERE s.LocationCurrentFlag = 1

 

[link9]

Say you had an array w/ 5 elements, and you wanted to execute a SPROC once for each of those elements:

dim myArray(4), i
'populate array here

dim comObj
set comObj = server.createObject("ADODB.Command&quot
comObj.commandType = 4
comObj.activeConnection = someConnection
comObj.commandText = "theNameOfMySproc"

for i = 0 to ubound(myArray)
comObj.parameters("@paramName&quot.value = myArray(i)
comObj.execute
next

So that you have your loop in your code, and just assign the different parameter values from the array... execute, and you're done.

A simplified example, but I think you can get the point from that much. If not, post back with the ASP code you're using, and we can help to modify it so it works for you.


paul

 

[sunila7]

i think from the front end u should concatenate all the selected departments for eg. ('001','002','003') and pass it into SP and use NOT IN keyword in the SQL to select..
like below.

INSERT INTO #PersonTemp
SELECT Distinct tsh.PersonGUID
FROM TableStatusHistory tsh, TableJobHistory tjh, TableLocationHistory tlh
WHERE tsh.PersonGUID = tjh.PersonGUID
AND tsh.PersonGUID = tlh.PersonGUID
AND tsh.StatusCurrentFlag = 1
AND JobCode = @JobCode
AND DepartmentCode NOT IN @DepartmentCode

sunil