SQL Server, getting a store procedure's result

microsoft.public.sqlserver.server - Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.


SQL Server >> getting a store procedure's result

by silviocortes » Tue, 14 Sep 2004 00:37:43 GMT

Hey guys,

I have a store procedure that returns a recorset. Here's an example:

create procedure ABC as
--some code here that works with @x and @y.. and then the last line
of the proc:
SELECT @x,@y

That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @x and @y.
Is there a way to get these results back in a variable as I call the
store proc?

Thanks,

Top

SQL Server >> getting a store procedure's result

by Narayana Vyas Kondreddi » Tue, 14 Sep 2004 00:50:42 GMT


You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.

More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/




Hey guys,

I have a store procedure that returns a recorset. Here's an example:

create procedure ABC as
--some code here that works with @x and @y.. and then the last line
of the proc:
SELECT @x,@y

That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @x and @y.
Is there a way to get these results back in a variable as I call the
store proc?

Thanks,




Top
Similar Threads

1. help with store procedure - getting results from 3 queries 

Hi,
I have a table user with the following field.
id,code,xdate

I am trying to write a stored procedure that would return the results as the 
following 3 queries 

a. select count(id) from user where code like 'TR%'
b. select count(id) from user where code like 'TR%' and xdate < getdate()
c. select count(id) from user where code like 'XP%' and xdate < getdate()

Basically the new stored procedure should give me the results of a,b & c in 
the most efficient way. I would really appreciate your help.
Thanks

2. problem getting result set through a stored procedure call using VB. - SQL Server

3. Using variables for tablename in stored procedures/getting records in a Recordset from stored procedure 

Hello,

with this stored procedure I can get Table data in a Recordset
(VB6/SQL-Server 2000)
a) stored procedure

CREATE    PROCEDURE [dbo].[sp_WJNr] @JahrAng int, @NrAng int, @DSNr
nvarchar(5)
AS
SELECT * FROM tbl_VerfW WHERE Jahr = @JahrAng AND Nummer = @NrAng AND
DSNr = @DSNr

b)VB6-Code:
Option Explicit
Dim cmdKd As New ADODB.Command, cmdVerf As New ADODB.Command
Dim rsKd As New ADODB.Recordset, rsVerf As New ADODB.Recordset

Sub getData(txtNummer As String, txtJahr As String)
 With cmdVerf
        .ActiveConnection = cnDBConn
        .CommandType = adCmdStoredProc
        .CommandText = "sp_WJNr"
        .Parameters.Append .CreateParameter("@JahrAng", adInteger,
adParamInput, , txtJahr)
        .Parameters.Append .CreateParameter("@NrAng", adInteger,
adParamInput, , txtNummer)
        .Parameters.Append .CreateParameter("@DSNr", adChar,
adParamInput, 5, "84308")
        Set rsVerf = .Execute
    End With
    If rsVerf.RecordCount <> 0 Then    'record found
        DatenKd rsVerf("ID_Kd"), frmWiderspruch
        With frmWiderspruch
            .txtNummer = NZ(rsVerf("Nummer"), "")
            .txtJahr = NZ(rsVerf("Jahr"), "")
            .txtEinDat = NZ(rsVerf("EinDat"), "")
            .txtErldat = NZ(rsVerf("ErlDat"), "")
            .txtzdADat = NZ(rsVerf("zdADat"), "")
            If Not IsNull(rsVerf("ID_Vertr")) Then
                DatenVertr rsVerf("ID_Vertr"), frmWiderspruch
            End If
            .Show
        End With
        Unload frmNrJahr
    Else                                'Nor Record found
        MsgBox ...
        txtNummer = ""
        txtJahr = ""
    End If
    rsVerf.Close
    cmdVerf.Parameters.Delete ("@JahrAng")
    cmdVerf.Parameters.Delete ("@NrAng")
    cmdVerf.Parameters.Delete ("@DSNr")
    Set rsVerf = Nothing
    Set cmdVerf = Nothing
    End Sub

This works without any problems.

Because i need this stored procedure for more than ohne table in ma
database, I changed the stored procedure the following way (tblName as
string variable for the name of the table)
a) Stored procedure
CREATE    PROCEDURE sp_GenJNr @tblname nvarchar(128),
                           @Nummer integer,
			   @Jahr integer,
			   @DSNr nvarchar(5) AS
   SET NOCOUNT ON
   DECLARE @sql nvarchar(4000)
   SET @sql = 'SELECT *
               FROM ' + quotename(@tblname) + '
               WHERE Jahr = @Jahr And Nummer = @Nummer AND DSNr =
@DSNr'

   EXEC sp_executesql @sql, N'@Jahr Integer, @Nummer Integer, @DSNr
nvarchar',
		      @Jahr = @Jahr, @Nummer = @Nummer, @DSNr = @DSNr

GO

Using SQL Server 2000 QueryAnalayzer this also works without any
problems, I get the records I looking for. But I can get the data
into a recordset. I had changed the code shown before the following
way:
Sub GetData(txtNummer As String, txtJahr As String)
        Select Case strVerfArt
            Case "W"
                strtblName = "tbl_VerfW"
            Case "K"
                strtblName = "tbl_VerfK"
        End Select
    With cmdVerf
         .ActiveConnection = cnDBConn
        .CommandType = adCmdStoredProc
        .CommandText = "sp_GenJNr"
        .Parameters.Append .CreateParameter("@tblname", adVarWChar,
adParamInput, 128, strtblName)
        .Parameters.Append .CreateParameter("@Jahr", adInteger,
adParamInput, , txtJahr)
        .Parameters.Append .CreateParameter("@Nummer", adInteger,
adParamInput, , txtNummer)
        .Parameters.Append .CreateParameter("@DSNr", adChar,
adParamInput, 5, "84308")
        'Debug.Print strSQL
        Set rsVerf = .Execute '(strSQL)
    End With
    If rsVerf.RecordCount <> 0 Then    'Record found
        DatenKd rsVerf("ID_Kd"), frmWiderspruch

rsVerf.RecordCount allways retrieves 0, although there is an existing
record in the table. What is wrong with my code? How can I get the
Record into a recordset with a stored procedure of this type?

Thank you.

Karl

4. Stored Procedures getting created as system stored procedures. - SQL Server

5. Getting the return from a stored procedure from within a stored procedure 

Im trying to get stp1 to get the return value from stp2. stp2 returns
a int. Here is the code that I used in stp1

delcare @return as in

set @return = (dbo.stp2 (PARAM1, PARAM2))

I have tried different way to execute this but I get a error:
"Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
either column "dbo" or the user-defined function or aggregate
"dbo.stp2", or the name is ambiguous."

Is there a way to get the return value of a stored procedure from
within a stored procedure?

6. Using stored procedure result set in another stored procedure

7. How to stored a stored procedure's results into a table

8. Passing a result set from one stored procedure to another stored procedure - SQL Server