SQL Stored Procedures 1

[robinmiller]

I am attempting to return a record set from a stored procedure to a VB app.

Question is :

1) What must I have in the stored procedure to return a recordset.

2) What must I have in VB to accept it ?

Procedure is as follows

CREATE PROCEDURE .sp_Order(@ID as int)
AS
SELECT dbo.Customers.cusName, dbo.Customers.cusContact, dbo.Customers.cusAddressL1, dbo.Customers.cusAddressL2, dbo.Customers.cusAddressL3,
dbo.Customers.cusAddressL4, dbo.Customers.cusPostCode, dbo.Customers.cusCountry, dbo.Customers.cusTel, dbo.Customers.cusFax,
dbo.Customers.cusDelContact, dbo.lkupTerms.trmDescription, dbo.Employees.empSName, dbrders.ordID, dbrders.ordDate, dbrders.ordPO,
dbrders.ordOurRef, dbrders.ordGrossCost, dbrders.ordDiscount, dbrders.ordVAT, dbrders.ordDeliveryCost, dbrders.ordNettCost,
dbrders.ordNotes, dbo.Delivery.delAddressL1, dbo.Delivery.delAddressL2, dbo.Delivery.delAddressL3, dbo.Delivery.delAddressL4,
dbo.Delivery.delPostCode, dbo.Delivery.delCountry, dbo.Delivery.delTel, dbo.Delivery.delFax, dbo.Stock.stkCode, dbo.Stock.stkName,
dbo.Stock.stkUnitPrice, dbo.Stock.stkEAN8Barcode, dbrderLines.orddetAmount, dbrderLines.orddetCost, dbrderLines.orddetDiscount,
dbrderLines.orddetTotal
FROM dbo.Delivery INNER JOIN
dbrders ON dbo.Delivery.delordID = dbrders.ordID LEFT OUTER JOIN
dbo.Employees ON dbrders.ordempID = dbo.Employees.empID RIGHT OUTER JOIN
dbrderLines ON dbrders.ordID = dbrderLines.orddetordID RIGHT OUTER JOIN
dbo.Customers ON dbrders.ordcusID = dbo.Customers.cusID LEFT OUTER JOIN
dbo.Stock ON dbrderLines.orddetstkID = dbo.Stock.stkID LEFT OUTER JOIN
dbo.lkupTerms ON dbo.Customers.cusTerms = dbo.lkupTerms.trmID
Where dbrders.ordID = @ID
Return

 

[chiph]

Robin -

You need a copy of a book called "SQL Server 2000 Stored Procedure Programming" by Sunderic & Woodhead. ISBN is: 0-07-212566-7

It explains all about that, plus how to use good error handling in stored procedures.

Chip H.

 

[NickISD]

Robin,
The select part of your query determines which will be returned back in your recordset. The example below is an SP that returns the next available key to use based on the parameter passed to it. The openAdiResultset call is my own call to a class module and the intdenull function is a wrapper that checks for Nulls, but you should be able to pick the bones out of it.

Hope this helps, Nick W

----- Stored Procedure
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'SP_GetNextKey')
DROP PROCEDURE SP_GetNextKey
GO
CREATE PROCEDURE SP_GetNextKey(@iKeyID int) AS
BEGIN TRANSACTION
UPDATE TABLEKEYS
SET TABLEKEYS.nlCurrentKey = TABLEKEYS.nlCurrentKey + 1
WHERE
TABLEKEYS.nlTableKeyID = @iKeyID
SELECT
TABLEKEYS.nlCurrentKey
FROM
TABLEKEYS
WHERE
TABLEKEYS.nlTableKeyID = @iKeyID
COMMIT TRANSACTION
GO


----- VB Code
iNewMember = 0: cSql = "SP_GetNextKey 1"
iOk = Db.OpenADOResultSet(rs, cSql, adOpenStatic)
If iOk = True Then
If Not (rs.EOF And rs.BOF) Then
iNewMember = IntDeNull(rs!nlCurrentKey)
rs.Close

 

[robinmiller]

Thanks. Much appreciated