April 5, 2005 at 12:50 pm
This might be simple, but couldn't figure it out...
CREATE PROCEDURE dbo.spSEL_Personnel_TEST
@OfficeID INT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Begin
SELECT DSP.PersonnelID
FROM dbo.DataSPersonnel DSP
INNER JOIN dbo.Personnel P
ON P.PersonnelID = DSP.PersonnelID
INNER JOIN dbo.PersonnelOfficeM POM
ON P.PersonnelID = POM.PersonnelID
INNER JOIN dbo.PersonnelM PM
ON P.PersonnelID = PM.PersonnelID
INNER JOIN dbo.MDataSource MDS
ON PM.SourceID = MDS.SourceID
INNER JOIN dbo.OfficeM
ON POM.OfficeID = OM.OfficeID
WHERE (POM.IsActive = 1) AND (POM.IsAgent = 1)
AND (POM.OfficeID = @OfficeID)
AND (DSP.DataSourceID = 11)
AND (PM.SourceID = OM.SourceID)
EXEC sp_SEL_PProfile_TEST PersonnelID, 1
End
Above SP returns numerous PersonnelIDs that need to be passed on as parameter values to the SP being executed after the SELECT. Is that possible? If I assign the PersonnelID from first SELECT to something like @PersonnelID then only the last PersonnelID is coming across as the @PersonnelID variable can hold only one value and it takes the last value from the resultset that's returned. I need to pass each PersonnelID value that the spSEL_Personnel_TEST Sproc returns to sp_SEL_PProfile_TEST Sproc that's being executed at the end. How can I do that?
Thanks.
JN
April 5, 2005 at 12:53 pm
One simple solution I see is to transform spSEL_Personnel_TEST into a table function that return all the ids.
then simple call
sp_SEL_PProfile_TEST /*no id,*/ 1
Select * from whatever W inner join dbo.fnSEL_PProfile_TEST (param1, param2, param3) fnProfil on W.id = fnProfil.id
April 5, 2005 at 1:50 pm
Thanks, I tried and it's working, but getting an error - thinking need to change syntax? Following is how my Sproc looks like now:
CREATE PROCEDURE dbo.spSEL_Personnel_TEST
@OfficeID INT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Begin
SELECT PersonnelID
FROM dbo.fn_PersonnelID(@OfficeID)
EXEC sp_SEL_PProfile_TEST /*no id,*/ 1
End
When I execute spSEL_Personnel_TEST (first Sproc) with OfficeID value supplied to it, I get following error:
sp_SEL_PProfile_TEST expects parameter '@IsUpdate', which was not supplied.
But I am supplying the value of 1 for @IsUpdate as you can see at the end in:
EXEC sp_SEL_PProfile_TEST /*no id,*/ 1
So why do I get the error that I am not supplying a value for @IsUpdate parameter? My syntax must be wrong?
Thanks.
JN
April 5, 2005 at 2:07 pm
I guess I didn't make myself clear. I would change the 2nd sp so that it doesn't work on only 1 row at the time, but on all rows at the time.
in the 2nd sp :
do whatever you need
but instead of having this
where id = @id
do this :
where id in (SELECT PersonnelID
FROM dbo.fn_PersonnelID(@OfficeID)).
It would probably be even better if you could simply join to the tables directly and avoid the in () operator all together.
April 5, 2005 at 2:12 pm
I am not sure if that can be done...
The 2nd Sproc doesn't have Param like @OfficeID and for me to do the Function call from 2nd Sproc, I need value for an OfficeID, which is supplied to the first Sproc only. So I can't do a function call from 2nd Sproc, as I don't have the required @OfficeID value that the function is looking for.
Suggesions?
Thanks.
JN
April 5, 2005 at 2:18 pm
add a parameter to the 2nd sp
April 5, 2005 at 2:38 pm
I don't think this would work for what I need to do...
I have a 3rd Sproc that executes from 2nd Sproc and inserts or updates values (Checksum) into a table that's used to keep track of updates to a record based on PersonnelID so the 3rd Sproc expects PersonnelID param value supplied to it. I am doing a compare for record updates or new records using Checksum value from last insert/update to Checksum value that's current - all assigned to @Variable, which can hold only one value at a time so can do check on only one PersonnelID at a time. So to the 2nd Sproc, I must pass one PersonnelID value at a time so that it can do Checksum on this PersonnelID's records and determine if it's a new record or if the record values have been updated since last Checksum value insert/update.
Any other suggestions??
JN
April 5, 2005 at 2:48 pm
can you post the code from all the sps so i can provide a better solution?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply