Stored Procedure Output 101 2

[cranger01]

All,

I admit, I use stored procedures normally for updates, inserts and deletes. My skills and reference material is basic. I don't expect an answer, but if you can point me to online resources I would appreciate it. I haven't refined my search enough appearently cause I haven't found on this site yet.

I am creating a stored procedure that takes two input fields and then returns 5 fields from 2 tables along with a row counter back to an ASP page. Not used to working with output back to ASP and am not an ASP coder by trade.

Do I have to define all the fields as output since I am attaching a rownumber field and assign them or do I just increment the rownumber field?

CREATE PROCEDURE dbo.spMergProductsPrices


@category VARCHAR(50),
@subcategory VARCHAR(50),
@rownumber INT OUTPUT

AS


SELECT dbo.epp_products.product_name, dbo.epp_products.product_id, dbo.epp_products.description, dbo.epp_products.category,
dbo.epp_products.subcategory, dbo.epp_store_price.product_size, dbo.epp_store_price.regular_price
FROM dbo.epp_products INNER JOIN
dbo.epp_store_price ON dbo.epp_products.product_id = dbo.epp_store_price.product_id
WHERE (dbo.epp_products.category = @category) AND (dbo.epp_products.subcategory = @subcategory)



GO

 

[bborissov]

With this SP you pass back a created recordset and nothing else. If you want to set @rownumber and get it from your frontend, you must change it:

CREATE PROCEDURE dbo.spMergProductsPrices


@category VARCHAR(50),
@subcategory VARCHAR(50),
@rownumber INT OUTPUT

AS


SELECT dbo.epp_products.product_name,
       dbo.epp_products.product_id,
       dbo.epp_products.description,
       dbo.epp_products.category,
       dbo.epp_products.subcategory,
       dbo.epp_store_price.product_size,
       dbo.epp_store_price.regular_price
FROM  dbo.epp_products
      INNER JOIN dbo.epp_store_price ON
                 dbo.epp_products.product_id =
                 dbo.epp_store_price.product_id
WHERE (dbo.epp_products.category = @category) AND
      (dbo.epp_products.subcategory = @subcategory)

SET @rownumber = @@ROWCOUNT -- store number of selected records

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cranger01]

That was quick. Thanks.

I think that is what I want.

Say there are 3 results for my query based on category and subcategory. I need to return the following as an example. Rownumber being the last column

Widget, 141, for widgeting, big, 3.25, 1
Cog, 224, for cogging, very big, 5.24, 2
Chain, 723, for chaining, small, 9.42, 3

 

[bborissov]

What version of SQL Server?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cranger01]

SQL 2000

 

[cranger01]

Actually, That wasn't what I wanted. I get the records back that I want, but I do not get @rowcount retured as part of the record set. Which is what I want.

 

[bborissov]

@rowcount will never be returned as part of recordset. I don't know how you run this query but I suppose you use ADO (because of ASP), In your ADO object add a 3 parameters and declare last as output, after you run ths SP then check 3th parameter to get value of the @rownumber.
Something like:
(this code is taken from

http://www.freevbcode.com/ShowCode.asp?ID=3687)

Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim objPara As ADODB.Parameter
Dim objpara2 As ADODB.Parameter
Dim objpara3 As ADODB.Parameter
Dim objRS As ADODB.Recordset
Dim k As Integer
    
Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command
    
'Creating the DB connection string
'Please change the below connection string as per your
'server and database being used.
objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY INFO=TRUE;USER ID=sa;INITIAL CATALOG=TestSQL;DATA SOURCE=Rockets"
    
'Opening the connection
objCon.Open objCon.ConnectionString
    
MsgBox "Connection opened"
    
'assigning the command object parameters
With objCom
     .CommandText = "spMergProductsPrices"     'Name of the stored procedure
     .CommandType = adCmdStoredProc  'Type : stored procedure
     .ActiveConnection = objCon.ConnectionString
End With

'Create 3 parameters
Set objPara = objCom.CreateParameter("category", adVarChar, adParamInput, 50)
Set objpara2 = objCom.CreateParameter("subcategory", adVarChar, 
adParamInput, 50)
Set objpara3 = objCom.CreateParameter("rownumber", adInteger, 
adParamOutput)

'Append the parameters to command object
objCom.Parameters.Append objPara
objCom.Parameters.Append objpara2
objCom.Parameters.Append objpara3
    
'Store the result in a recordset
Set objRS = objCom.Execute
    
'Open the recordset
Do While Not objRS.EOF
   For k = 0 To objRS.Fields.Count - 1
       Debug.Print objRS(k).Name & ": " & objRS(k).Value  
   Next
   Debug.Print "_____" 
   objRS.MoveNext
Loop
    
'Close the recordset
objRS.Close
    
'retrieve the output parameters values
MsgBox "Total records returned: " & objPara3.Value

'close connection
objCon.Close

'cleaning up
Set objCom = Nothing
Set objCon = Nothing
Set objPara = Nothing
Set objpara2 = Nothing
Set objpara3 = Nothing
Set objRS = Nothing

(not tested at all)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cranger01]

Thanks everyone for your questions..... I asked the developer what he is using to call the sp...this is his response...

I am using asp.net (.net 2.0), accessing the stored procedure from dll library via objectDataSource and displaying data as a dataset. But as long us the stored procedure works, my custom data access can tap into any stored procedure

I asked him if he can use a rownumber from the Datasource and this is his reponse...

It is possible to create a new column, calculate how many rows there are, and assign unique ID as a counter. But it will not be an efficient code for the purpose I want. That takes up response time. Since store procedure is a precompiled native code for the data, it will be fast and efficient


Since a lot of you are experts on SP, are you telling me what I am trying to do can't be done, meaning I can't return a rowcount with the recordset? or just easier to be coded and accounted for in the calling program.

 

[bborissov]

I am totaly confised now. What you want:

1. Something like Recordnumber in your recordset:

Widget, 141, for widgeting, big, 3.25, [b][COLOR=blue]1[/color][/b]
Cog, 224, for cogging, very big, 5.24, [b][COLOR=blue]2[/color][/b]
Chain, 723, for chaining, small, 9.42, [b][COLOR=blue]3[/color][/b]

2. Tolal number of returned records. Recordset have no rownumber columd, but you have 3d parameter that will give you this info.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cranger01]

Option 1

 

[gmmastros]

In sql 2000, your best bet would be to first insert the data in to a table variable that has an identity field, and then select the data from there. Like this...

CREATE PROCEDURE dbo.spMergProductsPrices


@category VARCHAR(50),
@subcategory VARCHAR(50),
@rownumber INT OUTPUT

AS
[!]SET NOCOUNT ON[/!]
Declare @Temp 
Table   (RowId Integer Identity(1,1),
        product_name varchar(100),
        product_id int,
        description varchar(100),
        category varchar(100),
        subcategory varchar(100),
        product_size int,
        regular_price decimal(10,2)
        )

Insert
into   @Temp
       (
        product_name,
        product_id,
        description,
        category,
        subcategory,
        product_size,
        regular_price
       )
SELECT dbo.epp_products.product_name, 
       dbo.epp_products.product_id, 
       dbo.epp_products.description, 
       dbo.epp_products.category, 
       dbo.epp_products.subcategory, 
       dbo.epp_store_price.product_size, 
       dbo.epp_store_price.regular_price
FROM   dbo.epp_products INNER JOIN
          dbo.epp_store_price ON dbo.epp_products.product_id = dbo.epp_store_price.product_id
WHERE  (dbo.epp_products.category = @category) AND (dbo.epp_products.subcategory = @subcategory)
Order By ???

Select  product_name,
        product_id,
        description,
        category,
        subcategory,
        product_size,
        regular_price,
        RowId As RowNumber
From    @Temp
Order By RowId

Notice where I declare the @Temp table. I guessed on the field type/size, so you will need to adjust that. Also, your main query should have an order by, or else the row numbers will be meaningless.

For what it's worth... Incrementing a counter while displaying the results in a ASP page is the better solution. The difference in time will be nearly non-existent (unless you are displaying hundreds of thousands of records which is a bad design to begin with). There will be performance implications with the approach. Sure, it will likely be fast, but it won't be as fast as it was because you are now doing more work.

Also, to prevent a followup question, don't forget to add the Set NoCount On as shown in the procedure.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cranger01]

Thanks to both of you.!!!

I got it working in either cases.

Actually, either of you have a good recommendation for books on the subject.

 

[gmmastros]

faq183-3324

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom