combining statements in SP

[kristinac]

Is it possible to create a single stored procedure that does two things from two different tables? I would like to write one that says

SELECT this value from table1
WHERE othervalue = whatever

THEN

SELECT this value from table2
WHERE table1Value = table2value

Is that possible or do I have to use two seperate stored procs?

Sorry if this has been covered somewhere in this forum. I'm new to sql server and haven't found anything that really addresses this yet.

Thanks for any input....

 

[JayKusch]

Yes you may do that!

Thanks

J. Kusch

 

[nigelrivett]

No - you can do that in a single SP.
As you have it it will return two resultsets.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

I guess I should relay what I was refering to ... You can have various select statements in an SP IF you are using them to aquire a final result set. ie. setting a variable via a select statement to be used in another select statement further down in the code.

Thanks

J. Kusch

 

[SQLSister]

YOu can also return multiple result sets if your user interface program can handle them We do this all the time for web pages which display more than one set of data.

 

[nigelrivett]

My post was meant to indicate that what you are trying to do is fine in a single SP.
(I wasn't very clear).

You can do most things you can think of in an SP.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[kristinac]

Thanks so much for your replies. I kind of feel like a dummy because I realized I was overcomplicating the whole thing. This:

SELECT tStatus.Queue
FROM tStatus
WHERE tStatus.Id =
(SELECT tPermits.Status
FROM tPermits
WHERE tPermits.Id = 1008)

Worked just fine. How brain dead am I??

I still am not sure though how to combine statements within one SP. I was told you can use SQL Server SPs to say "SELECT suchandsuch and do this THEN go do this THEN go do this." I am not sure about the syntax for such things though.

Thanks again!

 

[JohnDTampaBay]

Perhaps this will illustrate the syntax for multi-statement procedures using your final query. This particular example is overkill, but maybe it will give you the general idea.

CREATE PROC uspGetStatus
  @PermitID intAS
BEGIN
  DECLARE @StatusID int

  SELECT @StatusID = tPermits.Status
  FROM tPermits
  WHERE tPermits.Id = @PermitID

  SELECT tStatus.Queue
  FROM tStatus
  WHERE tStatus.Id = @StatusID
END

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[kristinac]

Thank you!! That makes more sense now!