UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Stored Procedure Return Value In A Select Statement, Any Versions    
 
   
BruceM
post Dec 3 2018, 04:15 PM
Post#1


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


SQL Sever 2016

I have been reading about using stored procedures to return values, which I can do, but not in a way that the value is of any use. For instance, I have a stored procedure to return the current price from a Products table, using the ProductID as in input variable:

CODE
USE OrdersDatabase;
GO

CREATE PROCEDURE dbo.uspCurrentPrice
@ProductID int

AS

SET NOCOUNT ON;

SELECT UnitPrice
FROM dbo.tblProduct  
WHERE ProductID = @ProductID;

RETURN

GO

Some of my reading suggests that output parameters could be used. I tried this:
CODE
USE OrdersDatabase;
GO

ALTER PROCEDURE dbo.uspCurrentPrice
@ProdictID int,
@CurrentPrice money OUTPUT

AS

SET NOCOUNT ON;

SELECT UnitPrice
FROM dbo.tblProduct  
WHERE ProductID = @ProductID;

RETURN @CurrentPrice

GO


The following in a SSMS query returns the correct value with the first version:

EXEC OrdersDatabase.dbo.uspCurrentPrice @ProductID = 1234

With the second version, with the OUTPUT parameter, I have not discovered if there is a way to execute the procedure. I assume there is, but apparently it requires several layers of declarations and so forth.

The real problem is I have not discovered a way to use the value. Several things I read suggested the following would work. For reasons that are no doubt obvious to somebody who knows SQL Server syntax, it does not.

CODE
USE OrdersDatabase;
GO

DECLARE @ReturnValue money;
EXEC @ReturnValue = uspCurrentPrice @ProductID = 6275

SELECT @ReturnValue;

When I execute the code in a SSMS query it returns the correct EXEC result. Below that, 0.00. So, the stored procedure works correctly, but now to actually do anything with the value is eluding me. I'm using Access as the front end, and to tell the truth I'm about ready to go to a DLookup and accept the performance hit.
Go to the top of the page
 
MadPiet
post Dec 3 2018, 05:17 PM
Post#2



Posts: 2,611
Joined: 27-February 09



Can the procedure ever return more than one value? If not, you could convert this to a function. Works pretty much the same way as an Access function that you'd write in VBA, but in SQL Server you can return table-type object if you want.

(Note: this is an example of creating a very simple function in T-SQL... )

See this page: https://docs.microsoft.com/en-us/SQL/relati...SQL-server-2017

UDFs can be brutal slow, so use with caution. (Maybe see Kevn Boles' article "Death by UDF").

Here's a sample function from AdventureWorks:

CODE
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int  
AS  
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)  
    FROM Production.ProductInventory p  
    WHERE p.ProductID = @ProductID  
        AND p.LocationID = '6';  
     IF (@ret IS NULL)  
        SET @ret = 0;  
    RETURN @ret;  
END;


Then you could use the function like this:

CODE
SELECT TOP 5 p.ProductID
    , p.[Name] AS ProductName
    , dbo.[ufnGetProductStandardCost](p.ProductID,GETDATE()) AS PriceTag
FROM Production.Product p;

This post has been edited by MadPiet: Dec 3 2018, 05:21 PM
Go to the top of the page
 
cheekybuddha
post Dec 3 2018, 06:05 PM
Post#3


UtterAccess VIP
Posts: 10,650
Joined: 6-December 03
From: Telegraph Hill


Try:
CODE
IF object_id('uspCurrentPrice') > 0
  DROP PROCEDURE uspCurrentPrice;
GO
  
CREATE PROCEDURE dbo.uspCurrentPrice
  @ProductID int
AS
BEGIN

  SET NOCOUNT ON;

  SELECT UnitPrice
  FROM dbo.tblProduct  
  WHERE ProductID = @ProductID;

END


Then:
CODE
  With CurrentDb.Execute("uspCurrentPrice 6275;", dbFailOnError)
    If Not (.BOF And .EOF) Then
      Debug.Print .Fields("UnitPrice")
    End If
    .Close
  End With


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Minty
post Dec 4 2018, 04:34 AM
Post#4



Posts: 151
Joined: 5-July 16



The simplest route is to use a pass through query and make sure it is set to return results.
So in really simple terms if your SP was showing you how many SQL server emails you had sent out over the last 3 days;

CODE
CREATE PROCEDURE [dbo].[dbEMailStats]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

       SELECT TOP (100) PERCENT COUNT(msdb.dbo.sysmail_allitems.mailitem_id) AS No_Of_eMails, msdb.dbo.sysmail_allitems.sent_status
    FROM  msdb.dbo.sysmail_allitems INNER JOIN
                   msdb.dbo.sysmail_profile ON msdb.dbo.sysmail_profile.profile_id = msdb.dbo.sysmail_allitems.profile_id
    WHERE (msdb.dbo.sysmail_allitems.send_request_date > GETDATE() - 3)
    GROUP BY msdb.dbo.sysmail_allitems.sent_status
    RETURN    
END


Then your Passthrough query would be
CODE
EXEC dbo.dbEMailStats


And the result will be a datasheet of results. Just make sure the pass through is set to return results.
You can obviously pass parameters and do this in VBA if required as Cheeky described above.
This post has been edited by Minty: Dec 4 2018, 04:35 AM
Go to the top of the page
 
AlbertKallal
post Dec 4 2018, 04:36 AM
Post#5


UtterAccess VIP
Posts: 2,705
Joined: 12-April 07
From: Edmonton, Alberta Canada


A few things:

As a “general” rule, at least when working with Access, you are near always best to simply return values as a “result set”.

In plain English, that means the store procedure will return data just like a query. (a table select)

However, OFTEN when writing code in T-SQL (and not consuming) from Access, then often you want to call some code and you DO NOT want a table (dataset) returned.

This is especially the case for some routine that does some kind of calculation and NOT a select or table set is to be returned.

Now, lets clear up your issue:

First up:

RETURN <some value>

DO NOT use the above syntax.

Also, RETURN <some value> ONLY allows a integer value, and is for “status”.

So the standard in T-SQL is to return 0 if everyone was ok.

However, this “status” code return feature is NOT to be confused with return values.

AGAIN:

RETURN <some value> IS ONLY for returning a status code and the expression <some value> MUST be a integer (int).
(so you can't return anything but a int value).

And like most Access VBA code stubs, the RETURN (or exit sub in VBA) is optional.


Next up:

You must ALWAYS use OUTPUT in BOTH the store proc declare, and the EXEC calling code.

In other words, you need OUPUT used in BOTH THE store proc code and define as you have.


Furthermore in your code example you never SET the return value.

You have:

RETURN @CurrentPrice

(of course we now know we only need RETURN, right!)

But you never set that value of @CurrentPrice


So your code would have to be:

Set @CurrentPrice = (SELECT UnitPrice FROM tblProduct WHERE ProductID = @ProductID)

Thus your proc would/should be:

CODE
CREATE PROCEDURE dbo.uspCurrentPrice
@ProductID int,
@UnitPrice MONEY OUTPUT
AS
SET NOCOUNT ON;

SET @UnitPrice = (SELECT UnitPrice
FROM dbo.tblProduct  
WHERE ProductID = @ProductID)

RETURN


Now to make the call, you use:

EXEC uspCurrentPrice 1234, @MyCPrice OUTPUT

Or better as you did with named parms as:


EXEC uspCurrentPrice @ProductID = 1234, @UnitPrice = @MyCPrice OUTPUT

And note that the var @MyCPrice would have to be declared in the calling code.

eg:
DECLARE @MyCprice money
EXEC uspCurrentPrice @PoductID = 1234, @UnitPrice = @MyCPrice OUTPUT
print @MyCPrice

(above will work fine for testing in SSMS for testing).

Note that EVEN when you use OUTPUT, your return value (the expression) still goes on the right side of the expression!!!
(a “funny” syntax, since I wish you actually assigned the return parameter with our value on the left side).

But syntax for parameter and even output follows the same format. (the param name ALWAYS goes on the left side of the = (you can't flip this despite ones brain telling you it should be the other way!!!!).


Last but not least?

In Access, I stick to DAO and don’t use ADO.

So how do you return a value in Access (say a PT query)?

DAO does not support return values from a store proc.

If you “can”, then I would suggest you re-write the sproc to return a data set (like a select).

However, you may well be “tossed” into a working group in with web software, and ADO is being used. In these cases you cannot CHANGE the store proc because of other code in the system.

And VERY OFTEN “many” a procedure only needs to return a value and NOT a table set.

ADO supports return parameter but DAO does not.


If you are in that case in which you want to keep using DAO and get a return value from a pass though query?
(and you cannot modify the store proc because OTHER code uses the store proc.). And I really, I mean, why for most t-SQL and .net projects would anyone with a functional brain return a table set when paramters are the OFTEN and recommended choice here.

So, what you do in these cases?

Well, simply “convert” the return param value to a select result like this:

VBA
CODE
Sub GetValue()

   Dim strSQL     As String
   Dim MyReturn   As Currency
  
   strSQL = "DECLARE @MyCPrice money;" & _
            "EXEC uspCurrentPrice @ProductID = 1234, @UnitPrice = @MyCPrice OUTPUT;" & _
            "SELECT @MyCPrice"
            
   With CurrentDb.QueryDefs("MyPassR")
      .SQL = strSQL
      MyReturn = .OpenRecordset()(0)
   End With
  
End Sub


And of course, you can return multiple values – just remember to use OUTPUT in the parameter list you pass via the EXEC command.

And I ONLY recommend the above approach of converting a return value to a select (table) if you are "stuck" and want to continue using DAO.



Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
BruceM
post Dec 4 2018, 10:43 AM
Post#6


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


Thanks to all for replying. I should have mentioned that I posted the question at the end of my work day yesterday. That is why I haven't responded until now.

To add a little more detail to the situation, I am trying to return a single value: the current price of a product. This is, essentially, a purchase order database. Many POs are for recurring orders, so I have code to duplicate some of the main record (supplier and other details that tend not to change from one order to the next), add the current date, and leave other fields blank. Then I duplicate the related records, including the line items. I save the price when creating a purchase order, because of course it could change over time. When duplicating the record I want the current price, not the old one. So, for each related line item record I want the current price. In the Access-only implementation of this I used a CurrentPrice function in the UnitPrice query field:

UnitPrice: CurrentPrice(ProductID)

CurrentPrice opens a recordset and extracts the current price as its return value. However, for some reason this results in an error in the query, although it works correctly in the Immediate window. It occurred to me that, even if I could sort out the reason for the error or a way around it, such processing would be best performed in SQL Server. That's what led me to the current question.

In practice, I expect the current price is changed as needed the next time the product is selected as a line item, rather than by somebody periodically updating the Products table. I have made provision, when the price is changed in the line item record, to enquire whether the change is to be applied to the current PO only (a one-time or volume discount, perhaps), or to the Product record. If the latter, the Products table is updated. However, returning a value from a stored procedure (or function) seems like an important aspect of using SQL Server, so I am trying to learn more about it.

Albert, I have used to good effect your technique as described elsewhere of having a single pass through query as the "vessel" for dynamic SQL. So far, though, I have used it only for actions such as deleting a PO that was started but never completed (the person didn't realize there is a different supplier for the item, or whatever). I see what you are saying about using a SELECT. As for using ADO, I have little experience with it, but I have found this fine UA Wiki article, which should get me on track.

Thank you for the explanation of RETURN in a stored procedure. I had run across some discussion of the concept that "RETURN <some value> ONLY allows a integer value, and is for 'status'", but had not pieced together the summary explanation you have provided.

I too found the syntax of the OUTPUT parameter to be rather quirky. Again, thanks for the summary explanation.

Thanks to the others who responded.

MadPiet, I don't think a udf is the way to go here, but I appreciate the summary explanation. I expect UDFs will become part of my repertoire one day.

David, I like the simplicity of what you suggested. I'm also intrigued that from what you have shown, it seems possible to use CurrentDb.Execute to run a stored procedure directly, and get the result. I will experiment with that.

Minty, since the intent is to retrieve a single value, I'm not sure I see that your suggestion would be applicable. If it has an advantage over writing a pass through query directly, I don't see it, but I will keep it in mind.

To all, you have shown me a number of tools, and provided some explanation I had not been able to find in my searches. I have bookmarked the thread for future reference, and will now be doing some experimentation as time allows.
Go to the top of the page
 
cheekybuddha
post Dec 4 2018, 11:20 AM
Post#7


UtterAccess VIP
Posts: 10,650
Joined: 6-December 03
From: Telegraph Hill


>> I'm also intrigued that from what you have shown, it seems possible to use CurrentDb.Execute <<

Actually, you will probably have to do it with a Pass-trough query, more like what Albert suggested in his last example, and with the PT Query using a connection string to your SQLServer.

But, if your SP simply selects a recordset, then that's all you have to call in the query SQL.

:thumbup;

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Dec 4 2018, 12:41 PM
Post#8


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


The result I want is one field from one record, but I suppose that is a recordset. I hadn't looked at it that way, but it suggests another way of approaching this. I could look up Fields(0), I think.

A while ago I created a passthrough query with the SQL: SELECT 1 or something like that, just enough so I could save the query. Then I establish a DSN-less connection for that query, as with all tables, views, and passthrough queries. After that I just have to alter the SQL via the QueryDefs method Albert showed (I read another posting of his a few months ago, where he described the same thing he showed here).
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th December 2018 - 06:38 AM