CREATE PROCEDURE MySProc
     MyParameter1 int,
     MyParameter2 varchar(10),
     MyOutParameter3 int OUTPUT,
     MyOutParameters4 varchar(10) OUTPUT NULL
AS
...

	Set oCMD = Server.CreateObject("ADODB.Command")
	Set oRS = Server.CreateObject("ADODB.RecordSet")
	oCMD.ActiveConnection = MyADOConenction
	oCMD.CommandType = 4	' adCmdStoredProc
	oCMD.CommandText = ""
	oCMD.CommandTimeout = 30
' Refresh parameters from DB: oCmd.Parameters.Refresh
' Parameter name = oCmd.Parameters(nItem).Name

	' Set parameter (note: data type needs to be accomodated):
	oCmd.Parameters(nItem).value = MyParameterValue

	oRS.CursorType = 0	' Can't remember what the name of this is
	oRS.CursorLocation = 3	' Can't remember what the name of this is
	set oRS.Source = oCMD
	oRS.open	' Execute it!
' Return value:	oCmd.Parameters(0).value

	If MyADOConenction.errors.count > 0 then
' Process ADO errors
		for each objerror in MyADOConenction.errors
			m_strError = m_strError _
				& (objerror.description & chr(13) & chr(10))
		next
	End If

	set oCMD=nothing

CREATE PROCEDURE xsp_MySProc -- do NOT start name with "sp_..."
	@myParameter1	int,
	@myParameter2	varchar(10),
	@myParameter3	int=5	-- Optional
AS
SET NOCOUNT ON
SET XACT_ABORT ON	-- Abort on error
DECLARE	@MyLocalVariable1	int,
	@MyLocalVariable	varchar(10)

SELECT	MyCol1,
	MyCol2
FROM	dbo.MyTable
WHERE	    MyPKColum1 = @myParameter1
	AND MyPKColum2 = @myParameter2
ORDER BY MyCol1, MyCol2
SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

IF @intErrNo <> 0 -- OR @intRowCount < 1 -- include if NO rows is an error ....
BEGIN
	-- Error Handler
	RAISERROR(....)
	GOTO MySProc_EXIT
END
...

MySProc_EXIT:
SET NOCOUNT OFF
	RETURN
GO

quote:

---

Originally posted by t1g312

BTW, why do you say "do not start the name with sp_"?

---