Quick question on stored procedures.

[Gavin_Mannion]

Hi All,

I have a sp which I would like it to search on a certain table and then with it's results do anothr search on another table.

so I have something like this.

select ID from Table1

select fName from Table2 WHERE ID = Table1ID

see what I mean?

[Buzby]

Although you haven't actually posed a question, I think what you are trying to get at is this;

SELECT * FROM Table2 WHERE Table2.ID IN (SELECT ID FROM Table1)

This will give you all the records from table2 where there is an equivalent ID in Table1. Is that what you meant?
You can add a WHERE caluse the the second SELECT statement if necessary.

[Gavin_Mannion]

Originally posted by Buzby
Although you haven't actually posed a question, I think what you are trying to get at is this;

SELECT * FROM Table2 WHERE Table2.ID IN (SELECT ID FROM Table1)

This will give you all the records from table2 where there is an equivalent ID in Table1. Is that what you meant?
You can add a WHERE caluse the the second SELECT statement if necessary.

I did post a question, you have half answered it.

Here is the full question.

How do I use the results of my first query in the stroed procedure in the second query of my stored procedure.

[Buzby]

Right - do you mean set up a kinda subset of Table1, and base a query on that..? If you do;

SELECT * FROM
(SELECT * FROM Table1 WHERE ..... ) AS TempTable
WHERE .......

this will select some records from Table1 (based on the first WHERE clause), and then the outer query will select certain records from that subset of records, based on the 2nd WHERE clause.

Is that what you mean?
If not - please explain further.. we'll get there in the end.

[Buzby]

Or do you mean;

DECLARE @Table1ID INTEGER

SELECT @Table1ID = ID FROM Table1 WHERE .......
SELECT * FROM Table2 WHERE ID = @Table1ID

this stores the ID from table1 in @Table1ID, and then you can use it search for stuff in Table2

Is that it?

[Gavin_Mannion]

Okay here is my exact problem ...

I have the following sql statement

SELECT LoginName FROM ssbt_ClinicStaff WHERE StaffID = @StaffID

I am then calling a second stored procedure

sp_DropLogin '?I need the loginname from the previous one?'

how would I solve this problem?

Thanks

[Buzby]

DECLARE @Table1ID INTEGER

SELECT @Table1ID = ID FROM Table1 WHERE .......
SELECT * FROM Table2 WHERE ID = @Table1ID

But then, if that's all you want then you can do this;

SELECT Table2.* FROM Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID

[Gavin_Mannion]

Originally posted by Buzby
Or do you mean;

DECLARE @Table1ID INTEGER

SELECT @Table1ID = ID FROM Table1 WHERE .......
SELECT * FROM Table2 WHERE ID = @Table1ID

this stores the ID from table1 in @Table1ID, and then you can use it search for stuff in Table2

Is that it?

aha I think it is

[Buzby]

Well you just pass the @LoginName to the SP;

DECLARE @LoginNAme VARCHAR(20)

SELECT @LoginName = LoginName FROM .....

EXEC sp_DropLogin @LoginName



You set up sp_DropLogin like this;

CREATE PROCEDURE sp_DropLogin

@LoginName VARCHAR(20)

AS

....
and continue...