Stored procedure parameters

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Comments posted to this topic are about the item Stored procedure parameters

    Igor Micev,
    My blog: www.igormicev.com

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71446

    Interesting question, thanks Igor
    Haven't thought about that in quite a while.
    Working with input and output parameters is almost instinctive by now...
    🙂

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • RonKyle

    SSC-Dedicated

    Points: 31459

    Will have to think about this more, but my first hunch is that this is just word play.  If you search "Output Parameters" you get a clear answer from a Microsoft website.  That 70% chose what I consider the correct answer would seem to me to reinforce that.  But I will mull this over further when I have more time.

  • Jeff Moden

    SSC Guru

    Points: 994863

    I take exception to the supposed correct answer.  If you have defaults on the parameters, input is not required.  I agree... a bit of word play has come into this if you consider actual usage instead of rote definition.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • sknox

    SSChampion

    Points: 12231

    RonKyle - Thursday, March 30, 2017 7:30 AM

    Will have to think about this more, but my first hunch is that this is just word play.  If you search "Output Parameters" you get a clear answer from a Microsoft website.  That 70% chose what I consider the correct answer would seem to me to reinforce that.  But I will mull this over further when I have more time.

    Jeff Moden - Thursday, March 30, 2017 8:32 AM

    I take exception to the supposed correct answer.  If you have defaults on the parameters, input is not required.  I agree... a bit of word play has come into this if you consider actual usage instead of rote definition.

    This was a multiple-answer question. It sounds like maybe you fellas selected only one answer?
    I don't think there's any question here:
    1. True. Parameters can most definitely be input-only. Any parameter not declared with the OUTPUT keyword will not be output.
    2. False. Parameters defined with the OUTPUT parameter are input/output parameters, not output only. You can choose to ignore the input in your stored procedure, but you cannot prevent clients from passing in a value.
    3. True. Parameters can absolutely be input and output in the same procedure. Whether you read this as both input-only and input/output parameters can declared on the same procedure, or that any single parameter can be used as both an input parameter and an output parameter, this statement is true.
    4. False. (As it's the logical opposite of 3.)

  • Jeff Moden

    SSC Guru

    Points: 994863

    That could definitely be a part of the problem. I definitely missed the obvious (choose 2) message and the fact that they were check boxes for answers instead of radio buttons.  I'll blame it on a very late night at work and not enough coffee yet because I have no other reasonable excuse for missing such obvious things. :blush:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • BillLudlow

    SSCertifiable

    Points: 6160

    Drat, I did it too fast and only picked one

  • RonKyle

    SSC-Dedicated

    Points: 31459

    It sounds like maybe you fellas selected only one answer?

    I did miss the choose 2.  I knew multiple choice was possible because of the check boxes versus the radio buttons.  I just thought you were trying to be tricky and there really only was one answer.  Had I not missed the "choose 2", I'm now not sure how I would have selected.  But it's not quite the word play I thought yesterday it might be.  I have to think about it more over the weekend.  Just as yesterday I'm pressed for time again today.

  • George Vobr

    SSCrazy Eights

    Points: 8995

    The syntax CREATE PROCEDURE statement allows you to create output_only procedure. The following stored procedure has only one output parameter.
    For this reason, I could not choose the correct answer to the question of 2017/03/30. But thanks for this question. I've repeated how to use Stored Procedure parameters.

    USE AdventureWorks
    GO

    CREATE PROCEDURE dbo.uspGetPersonsTotalCount @out_only_param INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT @out_only_param = COUNT(*)
        FROM [Person].[Address];
    END
    GO

    DECLARE @out_only_param INT;
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    /*Results
    PersonsTotalCount
    -------------------------
    19614

    (1 row(s) affected)
    */

  • halford13

    SSChasing Mays

    Points: 622

    George Vobr - Sunday, April 2, 2017 6:00 PM

    The syntax CREATE PROCEDURE statement allows you to create output_only procedure. The following stored procedure has only one output parameter.
    For this reason, I could not choose the correct answer to the question of 2017/03/30. But thanks for this question. I've repeated how to use Stored Procedure parameters.

    USE AdventureWorks
    GO

    CREATE PROCEDURE dbo.uspGetPersonsTotalCount @out_only_param INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT @out_only_param = COUNT(*)
        FROM [Person].[Address];
    END
    GO

    DECLARE @out_only_param INT;
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    /*Results
    PersonsTotalCount
    -------------------------
    19614

    (1 row(s) affected)
    */

    I thought the same way 🙂

  • sknox

    SSChampion

    Points: 12231

    George Vobr - Sunday, April 2, 2017 6:00 PM

    The syntax CREATE PROCEDURE statement allows you to create output_only procedure. The following stored procedure has only one output parameter.
    For this reason, I could not choose the correct answer to the question of 2017/03/30. But thanks for this question. I've repeated how to use Stored Procedure parameters.

    USE AdventureWorks
    GO

    CREATE PROCEDURE dbo.uspGetPersonsTotalCount @out_only_param INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT @out_only_param = COUNT(*)
        FROM [Person].[Address];
    END
    GO

    DECLARE @out_only_param INT;
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    /*Results
    PersonsTotalCount
    -------------------------
    19614

    (1 row(s) affected)
    */

    Except this still works:

    DECLARE @out_only_param INT = 5;
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    If the parameter were truly output-only, SQL Server would raise an error when you pass an initialized variable as the parameter.
    Just because your procedure doesn't use the input value doesn't mean that it isn't passed in.

  • George Vobr

    SSCrazy Eights

    Points: 8995

    sknox - Wednesday, April 5, 2017 6:46 PM

    George Vobr - Sunday, April 2, 2017 6:00 PM

    The syntax CREATE PROCEDURE statement allows you to create output_only procedure. The following stored procedure has only one output parameter.
    For this reason, I could not choose the correct answer to the question of 2017/03/30. But thanks for this question. I've repeated how to use Stored Procedure parameters.

    USE AdventureWorks
    GO

    CREATE PROCEDURE dbo.uspGetPersonsTotalCount @out_only_param INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT @out_only_param = COUNT(*)
        FROM [Person].[Address];
    END
    GO

    DECLARE @out_only_param INT;
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    /*Results
    PersonsTotalCount
    -------------------------
    19614

    (1 row(s) affected)
    */

    Except this still works:

    DECLARE @out_only_param INT = 5;
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    If the parameter were truly output-only, SQL Server would raise an error when you pass an initialized variable as the parameter.
    Just because your procedure doesn't use the input value doesn't mean that it isn't passed in.

    Hi sknox,
    thank you for your post. You didn't specify the version of SQL Server or details about the error. On my SQL Server is the query result without error.
    The query below is executed with setting Results to text (CTRL+T).

    USE AdventureWorks
    GO

    SET NOCOUNT ON;
    SELECT @@VERSION AS SQLServer_Version;

    DECLARE @out_only_param INT = 5;
    SELECT @out_only_param as Virtual_Input;  -- is ignored and overwritten by the output value of the procedure
    EXEC dbo.uspGetPersonsTotalCount @out_only_param OUTPUT;
    SELECT @out_only_param as PersonsTotalCount;
    GO

    /*
    Results

    SQLServer_Version
    ---------------------------------------------------------------------
    Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)
        Sep 23 2016 18:13:56
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )

    Virtual_Input
    -------------
    5

    PersonsTotalCount
    -----------------
    19614
    */

  • Iulian -207023

    SSCertifiable

    Points: 7508

    I think the 2nd answer, it can accept output params only, should have been true too.
    Running on 2016 dev ed.

    CREATE PROCEDURE xyz(@po INT OUTPUT)
    AS
      BEGIN
       SET @po = 1;
      END;
    GO

    DECLARE @a INT= 2;
    EXECUTE xyz
       @a -- OUTPUT;
    PRINT @a;

    EXECUTE xyz
       @a OUTPUT;

    PRINT @a;

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply