asked on

Use stored procedure to return results and total

Using classic ASP/MS SQL 2008

I am trying to create a simple stored procedure for reports. I want it to return the results so that I can display them on a table, this is what I did, but nothing is returned :#

I need to results to be returned along with the total of records as well.

/****** Object: StoredProcedure [dbo].[rp_contacts_01_results] Script Date: 03/23/2015 09:46:03 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[rp_contacts_01_results]
AS
SELECT a.UserId ,
a.FirmId ,
a.EmployerId ,
a.LastNm ,
a.FirstNm ,
a.Title ,
a.Email ,
b.MaidenNm
FROM Users a
LEFT JOIN Users AS b ON a.EmployerId = b.UserId
WHERE a.UserType = 'corpuser'
ORDER BY b.MaidenNm ,
a.LastNm

I am using Dreamweaver to run the command, but none of the results are showing up. It only works if I do a recordset with a query on the page in which case I cant use a stored procedure :(

Aleks

ASKER

This is what I did: https://helpx.adobe.com/dreamweaver/kb/using-stored-procedures-within-dreamweaver.html

but when I expand the stored procedure it only shows @RETURN_Value instead of each of the fields I selected along with the total just like a recordset would do.

Big Monty

It only works if I do a recordset with a query on the page in which case I cant use a stored procedure :(

that's not necessarily true, you can call a stored procedure and stick the results into a recordset. I'm not going to refer to the above URL as it doesn't really show you what you need to do. I would refer here for a better explanation on how to do what you're looking

in the meantime, you can use the following code as a base to build what you want:

        set rs = Server.CreateObject("ADODB.RecordSet")
        set conn = Server.CreateObject("ADODB.Connection")
        set cmd = Server.CreateObject("ADODB.Command")

        sql = "rp_contacts_01_results"
        conn.Open connectionString

        with cmd
            .ActiveConnection = conn
            .CommandText = sql
     '--       .Parameters.Append  .CreateParameter( "paramName", adVarChar, adParamInput, 15, paramValue )
        end with
        rs.Open cmd

        if not rs.BOF and rs.EOF then    '-- results were returned
             do while not rs.EOF
                '-- write out your results however you like
                rs.MoveNext
            loop
        end if

       '-- clean up
       if rs.State <> 0 then rs.Close
       set rs = nothing
       if conn.State <> 0 then conn.Close
       set conn = nothing
       set cmd = nothing

Open in new window

you'll see in the command definition, I commented out a parameter. I left that as an example in case you wanted to eventually pass in a parameter(s). If you don't need it, you can safely remove that line

Aleks

ASKER

Problem is I don't think Dreamweaver will recognize that recordset and therefore I won't be able to use the interface to display the results, etc. Which is something I would like to keep doing.

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

Yep, thanks.