0

In SQL Sever, I run a stored procedure like this:

begin
    declare @account_id int = 98
    declare @transaction_id int = 2877
    declare @transaction_type_id int = null

    exec sp_sample
                  @account_id,
                  @transaction_id,
                  @transaction_type_id

end

and it returns a result like this:

enter image description here

How can I get two anonymous values above and assign them to two variables for the next calculation?

My stored procedure definition:

create procedure sp_sample
    (@account_id integer, 
     @transaction_id integer,
     @transaction_type_id integer)
/*with encryption*/
as
    declare @pricing_method char(1),
            @pricing_status char(1)
begin
    set nocount on

    execute sp_out_pricing_details  
                @account_id, 
                @transaction_id, 
                @transaction_type_id, 
                @pricing_method  output, 
                @pricing_status output

    select @pricing_method, @pricing_status
end
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hoang Nguyen
  • 61
  • 1
  • 13
  • 1
    What is `sp_sample`? That isn't a documented, nor undocumented, **S**pecial **P**rocedure. What do you mean by an "anonymous" value? – Thom A Jan 13 '21 at 09:59
  • @Larnu `sp_sample` is the name of my store procedure And as you can see in the picture, the store returns 2 anonymous column with 2 appropriate values **N** and **E**. – Hoang Nguyen Jan 13 '21 at 10:04
  • 1
    `sp_` is a **reserved** prefix by Microsoft for **S**pecial **P**rocedures, you should not be using it for user procedures. That dosen't explain what you mean by "anonymous" columns though. What makes a column "anonymous"? What is the definition of your Procedure? – Thom A Jan 13 '21 at 10:06
  • @Larnu I updated the definition in the question. – Hoang Nguyen Jan 13 '21 at 10:11
  • 1
    Why not give your columns in your `SELECT` (`select @pricing_method, @pricing_status`) aliases? Also, `sp_out_pricing_details` is *also* not a documented nor undocumented Special Procedure. Using the `sp_` prefix for user procedures isn't a good idea; as I mentioned it's reserved and for good reason. Your procedures could just suddenly stop working, and using the prefix comes with a performance hit. [Is the sp_ prefix still a no-no?](https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix) – Thom A Jan 13 '21 at 10:15
  • The columns, however, aren't "anonymous", they just don't have a name/alias (which is why I suggest giving them an alias). Then you can consume the information from them far more easily. – Thom A Jan 13 '21 at 10:18
  • I am not the author of them. They are the existing store :( – Hoang Nguyen Jan 13 '21 at 10:18
  • Then take the time to `ALTER` them and improve them. :) Adding an alias is simple: `SELECT @pricing_method AS PricingMethod, @pricing_status AS PricingStatus;`. Though, I must admit, I don't really see the point of that procedure; why not just call `sp_out_pricing_details`? Then you have the 2 variables returned as `OUTPUT` parameters. `sp_sample` adds no benefit here. If does no additional processing or transformations, it just calls another procedure; there's no benefit of doing that. The real "answer" here is to not use `sp_sample`, as it doesn't "do" anything. – Thom A Jan 13 '21 at 10:19
  • It seem I see the question in here (the comment with 192 votes) https://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure – Hoang Nguyen Jan 13 '21 at 10:22
  • 1
    That question shows how to `INSERT` data from a procedure into a table. You want the data in 2 variables. The simple answer would be to use `sp_out_pricing_details`, as it already has the `OUTPUT` parameters you want. Like I said, `sp_sample` serves no purpose here. – Thom A Jan 13 '21 at 10:27

1 Answers1

1

As I mention in the comment, sp_sample serves no purpose here. All it does is SELECT the 2 OUTPUT parameters, and without aliases. It does no additional "work"; it doesn't change the data, get additional default values from else where, or runs process prior to calling sp_out_pricing_details. As a result, it's actually making things harder for you, as sp_out_pricing_details actually completely serves the thing you want to do.

sp_out_pricing_details has 2 OUTPUT parameters and those are the 2 values that are being SELECTed (without aliases) in sp_sample and the ones you want in your variables. sp_sample is "blinding" you from the solution that is actually literally in front of you, in the definition of sp_sample. Just call sp_out_pricing_details directly with the same SQL:

DECLARE @account_id int = 98;
DECLARE @transaction_id int = 2877;
DECLARE @transaction_type_id int = NULL;

DECLARE @pricing_method char(1),
        @pricing_status char(1);

EXECUTE dbo.sp_out_pricing_details @account_id, --Again, the sp_ prefix is reserved by Microsoft, don't use it to User Procedure
                                   @transaction_id, 
                                   @transaction_type_id, 
                                   @pricing_method OUTPUT, 
                                   @pricing_status OUTPUT;

And then your 2 values are in your variables, @pricing_method and @pricing_status.

Thom A
  • 88,727
  • 11
  • 45
  • 75