Stored Procedure calling another that returns a value, no output parameter

From: Colin Colin (ccole_at_ghs.guthrie.org)
Date: 05/25/04 

Date: Tue, 25 May 2004 08:07:39 -0700

I have a Stored Procedure that I wrote a while ago that accepts 1
parameter, and at the end of the SP I do a SELECT so it returs a
?recordset? containing a value.

I am using this SP from various ASP pages on my intranet site.
Here is the code:
Public Function GetID(ByRef Coum_cn, ByVal TblName, ByRef IDVal)
Dim Coum_GetID
Dim CmdStr
Dim rs
        CmdStr = "EXEC dbo.GetID @p_FLD='" & TblName & "'"
        Set rs = Coum_CN.Execute(CmdStr)
        IDVal = rs(0)
        rs.close
End Function

Now I am trying to use this same SP from anothother SP, and I am having
trouble figuring out the calling syntax:

I have tried this:
DECLARE @SQL VARCHAR(1000)
DECLARE @NT_ID INT
SELECT @NT_ID=EXEC dbo.GetID 'NT_ID'
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'EXEC'.

I also tried this which almost works:
DECLARE @SQL VARCHAR(1000)
DECLARE @NT_ID INT
EXEC @NT_ID = dbo.GetID 'NT_ID'
SELECT @NT_ID as 'nt_id'

But the output I get:
This comes from the Stored Procedure itself:
-----------
243799

And this is the returned value:
nt_id
-----------
0

So @NT_ID doesn't get set.

Here is the basic code of my GetID SP:
CREATE PROCEDURE dbo.GetID
    @p_FLD VARCHAR(10)

AS
        DECLARE @p_ID INT
        DECLARE @TVAL INT
--- a bunch of stuff

--- then at the end
        SELECT @p_ID
GO

I realize that I could make @p_ID an output parameter instead of a
returned recordset value but then I would have to rewrite my ASP code.
So I rather not do that.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Calling SP from ADO and passing parameters
    ... You don't need to declare a parameter as OUTPUT if you only return one ... Just declare @CheckResult as a local ... I have a stored procedure which I ... > Dim prm As ADODB.Parameter ...
    (microsoft.public.data.ado)
  • Re: Calling SP from ADO and passing parameters
    ... You don't need to declare a parameter as OUTPUT if you only return one ... Just declare @CheckResult as a local ... I have a stored procedure which I ... > Dim prm As ADODB.Parameter ...
    (microsoft.public.vb.database.ado)
  • Re: Retrieving the stored procedure parameter list in ado.net
    ... 'Declare and open Connection. ... Dim cn As OleDbConnection = _ ... Dim prm As OleDbParameter = _ ... The stored procedure part is not an issue, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Newbie: Declaring Variables
    ... You need to declare an ADO.NET Command object and set its commandType ... property to Stored Procedure, then you can set the commandText property to ... > Dim strParm00 As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Querying a database
    ... Use My Database ... Declare @sCnt VarChar ... Set Nocount Off ... Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter ...
    (microsoft.public.vb.database)