Stored Procedure Help

[kim1]

I have an embedded SQL in my ASP program that I want to convert into stored procedure. It returns a recordset containing a list of sales people. It goes like this:

"SELECT name, address, phonenumber, region FROM sales_staff ORDER BY name"

I only have done stored procedure returning one record at a time. I do not know how to do it when I need to return more than one record(or list). Could somebody show me how?

Thanks.

 

[culshaja]

Here is the following you need to create a stored procedure:

CREATE PROCEDURE retrieve_salestaff
AS

  SET NOCOUNT ON

  SELECT name, address, phonenumber, region 
    FROM sales_staff 
  ORDER BY name ASC

  RETURN

GO

The purpose of

SET NOCOUNT ON

is to prevent two recordsets being returned via ADO as a second recordset is returned containing the status and number of rows returned.

If you need any more help just post and I'll answer if I can.

james
James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[TomasDill]

kim1, in addition to James' note, check the SET ROWCOUNT command. If you used SET ROWCOUNT 1 somewhere, you will get only one record from any query in the connection session. If this is the case, use SET ROWCOUNT 0 to disable records count limitation.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[Blauther]

Just a quick question...

If you set "NOCOUNT ON" at the beginning, should you turn it off at the end of the procedure?

--Brian