Stored procedure parameters

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  March 29, 2017 at 9:24 pm

  #322580

  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: 72625

  More actions

  March 30, 2017 at 2:52 am

  #1935656

  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: 31556

  More actions

  March 30, 2017 at 7:30 am

  #1935711

  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: 1004340

  More actions

  March 30, 2017 at 8:32 am

  #1935739

  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.

  Change is inevitable... Change for the better is not.
  

  ---

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

• sknox

  SSChampion

  Points: 12388

  More actions

  March 30, 2017 at 6:41 pm

  #1935892

  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: 1004340

  More actions

  March 30, 2017 at 7:45 pm

  #1935894

  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.

  Change is inevitable... Change for the better is not.
  

  ---

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

• BillLudlow

  SSCertifiable

  Points: 6553

  More actions

  March 31, 2017 at 3:00 am

  #1935925

  Drat, I did it too fast and only picked one

• RonKyle

  SSC-Dedicated

  Points: 31556

  More actions

  March 31, 2017 at 6:50 am

  #1935963

  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: 9996

  More actions

  April 2, 2017 at 6:00 pm

  #1936191

  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

  Say Hey Kid

  Points: 682

  More actions

  April 5, 2017 at 10:34 am

  #1936852

  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: 12388

  More actions

  April 5, 2017 at 6:46 pm

  #1936946

  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: 9996

  More actions

  April 6, 2017 at 6:03 am

  #1936996

  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: 7639

  More actions

  April 12, 2017 at 12:33 am

  #1937756

  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;