Passing a Fields Value Based on a Parameter Stored Procedure

[cruncher06]

I have the following to create a stored procedure and it works fine, but I need to get the list of JobId's in the output without supplying parameters. Here is what I am trying to do:

The parameter is automatically entered through code.

CREATEPROCEDURE spSelectedJobs
@id intOUTPUT
AS

SELECT id ,JobId
FROM job_notify
WHERE id = @id

At this point I want to know what JobId's are selected by the user and create a query that excudes the JobId from a Job List, basically:

SELECT JobId
FROM city_jobs
WHERE JobId <> JobID_From_SELECT_STATEMENT_ABOVE

This would provide a list of jobs that the user has not selected.



Any thoughts. I have tried creating a function as well, but so far, no luck.

Thanks.


Chris

[wwb_99]

1) Don't prefix stored procs with sp, the system uses that to denote system stored procs, so it ends up looking in MASTER before looking at your database.

2) Why are you using an output parameter?

3) I'm having trouble following the question, but I think what you need to do something like:

SELECT Job_Id
FROM City_Jobs
WHERE Job_Id NOT IN (
   SELECT Job_Id
   FROM job_notify
   WHERE id = @id)