Stored procedure returns 0 instead of value










2














There is a stored procedure that can return top 1 result as



USE [DB]
GO
.....

CREATE PROCEDURE [dbo].[GET]
(@in VARCHAR(10), @Out VARCHAR(10) OUTPUT)
AS
SELECT top 1 @Out = tab.Col
FROM table tab


RETURN
GO


When I call it in main query



DECLARE @output VARCHAR(10)
DECLARE @in VARCHAR(10)
DECLARE @Out VARCHAR(10)

EXECUTE dbo.GET @in = 'table', @Out = @output


It prints @output as 0;



but if I do



EXECUTE dbo.GET @in = 'table', @Out = @Out


And print @out, I get the correct value.



Why could this happen?



I did pass output @Out to pre-defined variable @output










share|improve this question























  • Please refer codeproject.com/articles/794765/…
    – Sanal Sunny
    Nov 12 '18 at 4:08






  • 3




    Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. The code appears to be for SQL Server, but LIMIT 1 is a MySQL construct not available in SQL Server ......
    – marc_s
    Nov 12 '18 at 4:58







  • 1




    Please tag the rdbms. If it's for SQL server then note: 1. You never do anything with @OutPK or populate @Out inside the proc; 2. You also need to use OUTPUT when calling a stored proc with an output variable.
    – Nick.McDermaid
    Nov 12 '18 at 5:35















2














There is a stored procedure that can return top 1 result as



USE [DB]
GO
.....

CREATE PROCEDURE [dbo].[GET]
(@in VARCHAR(10), @Out VARCHAR(10) OUTPUT)
AS
SELECT top 1 @Out = tab.Col
FROM table tab


RETURN
GO


When I call it in main query



DECLARE @output VARCHAR(10)
DECLARE @in VARCHAR(10)
DECLARE @Out VARCHAR(10)

EXECUTE dbo.GET @in = 'table', @Out = @output


It prints @output as 0;



but if I do



EXECUTE dbo.GET @in = 'table', @Out = @Out


And print @out, I get the correct value.



Why could this happen?



I did pass output @Out to pre-defined variable @output










share|improve this question























  • Please refer codeproject.com/articles/794765/…
    – Sanal Sunny
    Nov 12 '18 at 4:08






  • 3




    Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. The code appears to be for SQL Server, but LIMIT 1 is a MySQL construct not available in SQL Server ......
    – marc_s
    Nov 12 '18 at 4:58







  • 1




    Please tag the rdbms. If it's for SQL server then note: 1. You never do anything with @OutPK or populate @Out inside the proc; 2. You also need to use OUTPUT when calling a stored proc with an output variable.
    – Nick.McDermaid
    Nov 12 '18 at 5:35













2












2








2







There is a stored procedure that can return top 1 result as



USE [DB]
GO
.....

CREATE PROCEDURE [dbo].[GET]
(@in VARCHAR(10), @Out VARCHAR(10) OUTPUT)
AS
SELECT top 1 @Out = tab.Col
FROM table tab


RETURN
GO


When I call it in main query



DECLARE @output VARCHAR(10)
DECLARE @in VARCHAR(10)
DECLARE @Out VARCHAR(10)

EXECUTE dbo.GET @in = 'table', @Out = @output


It prints @output as 0;



but if I do



EXECUTE dbo.GET @in = 'table', @Out = @Out


And print @out, I get the correct value.



Why could this happen?



I did pass output @Out to pre-defined variable @output










share|improve this question















There is a stored procedure that can return top 1 result as



USE [DB]
GO
.....

CREATE PROCEDURE [dbo].[GET]
(@in VARCHAR(10), @Out VARCHAR(10) OUTPUT)
AS
SELECT top 1 @Out = tab.Col
FROM table tab


RETURN
GO


When I call it in main query



DECLARE @output VARCHAR(10)
DECLARE @in VARCHAR(10)
DECLARE @Out VARCHAR(10)

EXECUTE dbo.GET @in = 'table', @Out = @output


It prints @output as 0;



but if I do



EXECUTE dbo.GET @in = 'table', @Out = @Out


And print @out, I get the correct value.



Why could this happen?



I did pass output @Out to pre-defined variable @output







sql sql-server rdbms






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 '18 at 3:02







NewPy

















asked Nov 12 '18 at 3:35









NewPyNewPy

1276




1276











  • Please refer codeproject.com/articles/794765/…
    – Sanal Sunny
    Nov 12 '18 at 4:08






  • 3




    Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. The code appears to be for SQL Server, but LIMIT 1 is a MySQL construct not available in SQL Server ......
    – marc_s
    Nov 12 '18 at 4:58







  • 1




    Please tag the rdbms. If it's for SQL server then note: 1. You never do anything with @OutPK or populate @Out inside the proc; 2. You also need to use OUTPUT when calling a stored proc with an output variable.
    – Nick.McDermaid
    Nov 12 '18 at 5:35
















  • Please refer codeproject.com/articles/794765/…
    – Sanal Sunny
    Nov 12 '18 at 4:08






  • 3




    Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. The code appears to be for SQL Server, but LIMIT 1 is a MySQL construct not available in SQL Server ......
    – marc_s
    Nov 12 '18 at 4:58







  • 1




    Please tag the rdbms. If it's for SQL server then note: 1. You never do anything with @OutPK or populate @Out inside the proc; 2. You also need to use OUTPUT when calling a stored proc with an output variable.
    – Nick.McDermaid
    Nov 12 '18 at 5:35















Please refer codeproject.com/articles/794765/…
– Sanal Sunny
Nov 12 '18 at 4:08




Please refer codeproject.com/articles/794765/…
– Sanal Sunny
Nov 12 '18 at 4:08




3




3




Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. The code appears to be for SQL Server, but LIMIT 1 is a MySQL construct not available in SQL Server ......
– marc_s
Nov 12 '18 at 4:58





Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. The code appears to be for SQL Server, but LIMIT 1 is a MySQL construct not available in SQL Server ......
– marc_s
Nov 12 '18 at 4:58





1




1




Please tag the rdbms. If it's for SQL server then note: 1. You never do anything with @OutPK or populate @Out inside the proc; 2. You also need to use OUTPUT when calling a stored proc with an output variable.
– Nick.McDermaid
Nov 12 '18 at 5:35




Please tag the rdbms. If it's for SQL server then note: 1. You never do anything with @OutPK or populate @Out inside the proc; 2. You also need to use OUTPUT when calling a stored proc with an output variable.
– Nick.McDermaid
Nov 12 '18 at 5:35












2 Answers
2






active

oldest

votes


















2














Assuming SQLS due to presence of 'dbo' and sqlserver tag



Your query in the procedure doesn't assign a value to the out parameter (called @out) it assigns to some other variable called @outpk. Resolve the naming mismatch and make them the same



Sqlserver does not support LIMIT. To limit result set size use SELECT TOP 1 *. Using TOP (or any similar result set restrictor) without ORDER BY is illogical. Specify an ORDER BY



In sqlserver, output parameters must be passed with the OUTPUT keyword when calling the procedure:



EXEC sprocname @inputparameter ='value', @outputparameter = @variableToSet OUTPUT;


Use semicolons; omitting them is deprecated




Example



USE [DB]
GO

CREATE PROCEDURE [dbo].[GET]
(@in VARCHAR(10), @OutPk VARCHAR(10) OUTPUT)
AS
SELECT @OutPK = tab.Col
FROM table tab
ORDER BY tab.Col;
GO

DECLARE @output VARCHAR(10);

EXECUTE dbo.GET @in = 'table', @OutPK = @output OUTPUT

SELECT @output;





share|improve this answer






















  • Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
    – NewPy
    Nov 13 '18 at 3:02











  • I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
    – Caius Jard
    Nov 13 '18 at 7:39



















0














If its MySql (Limit is in mySql), you can simply call:



Call dbo.GET('table', @out);


No need to have separate variable @output.






share|improve this answer




















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53255642%2fstored-procedure-returns-0-instead-of-value%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    Assuming SQLS due to presence of 'dbo' and sqlserver tag



    Your query in the procedure doesn't assign a value to the out parameter (called @out) it assigns to some other variable called @outpk. Resolve the naming mismatch and make them the same



    Sqlserver does not support LIMIT. To limit result set size use SELECT TOP 1 *. Using TOP (or any similar result set restrictor) without ORDER BY is illogical. Specify an ORDER BY



    In sqlserver, output parameters must be passed with the OUTPUT keyword when calling the procedure:



    EXEC sprocname @inputparameter ='value', @outputparameter = @variableToSet OUTPUT;


    Use semicolons; omitting them is deprecated




    Example



    USE [DB]
    GO

    CREATE PROCEDURE [dbo].[GET]
    (@in VARCHAR(10), @OutPk VARCHAR(10) OUTPUT)
    AS
    SELECT @OutPK = tab.Col
    FROM table tab
    ORDER BY tab.Col;
    GO

    DECLARE @output VARCHAR(10);

    EXECUTE dbo.GET @in = 'table', @OutPK = @output OUTPUT

    SELECT @output;





    share|improve this answer






















    • Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
      – NewPy
      Nov 13 '18 at 3:02











    • I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
      – Caius Jard
      Nov 13 '18 at 7:39
















    2














    Assuming SQLS due to presence of 'dbo' and sqlserver tag



    Your query in the procedure doesn't assign a value to the out parameter (called @out) it assigns to some other variable called @outpk. Resolve the naming mismatch and make them the same



    Sqlserver does not support LIMIT. To limit result set size use SELECT TOP 1 *. Using TOP (or any similar result set restrictor) without ORDER BY is illogical. Specify an ORDER BY



    In sqlserver, output parameters must be passed with the OUTPUT keyword when calling the procedure:



    EXEC sprocname @inputparameter ='value', @outputparameter = @variableToSet OUTPUT;


    Use semicolons; omitting them is deprecated




    Example



    USE [DB]
    GO

    CREATE PROCEDURE [dbo].[GET]
    (@in VARCHAR(10), @OutPk VARCHAR(10) OUTPUT)
    AS
    SELECT @OutPK = tab.Col
    FROM table tab
    ORDER BY tab.Col;
    GO

    DECLARE @output VARCHAR(10);

    EXECUTE dbo.GET @in = 'table', @OutPK = @output OUTPUT

    SELECT @output;





    share|improve this answer






















    • Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
      – NewPy
      Nov 13 '18 at 3:02











    • I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
      – Caius Jard
      Nov 13 '18 at 7:39














    2












    2








    2






    Assuming SQLS due to presence of 'dbo' and sqlserver tag



    Your query in the procedure doesn't assign a value to the out parameter (called @out) it assigns to some other variable called @outpk. Resolve the naming mismatch and make them the same



    Sqlserver does not support LIMIT. To limit result set size use SELECT TOP 1 *. Using TOP (or any similar result set restrictor) without ORDER BY is illogical. Specify an ORDER BY



    In sqlserver, output parameters must be passed with the OUTPUT keyword when calling the procedure:



    EXEC sprocname @inputparameter ='value', @outputparameter = @variableToSet OUTPUT;


    Use semicolons; omitting them is deprecated




    Example



    USE [DB]
    GO

    CREATE PROCEDURE [dbo].[GET]
    (@in VARCHAR(10), @OutPk VARCHAR(10) OUTPUT)
    AS
    SELECT @OutPK = tab.Col
    FROM table tab
    ORDER BY tab.Col;
    GO

    DECLARE @output VARCHAR(10);

    EXECUTE dbo.GET @in = 'table', @OutPK = @output OUTPUT

    SELECT @output;





    share|improve this answer














    Assuming SQLS due to presence of 'dbo' and sqlserver tag



    Your query in the procedure doesn't assign a value to the out parameter (called @out) it assigns to some other variable called @outpk. Resolve the naming mismatch and make them the same



    Sqlserver does not support LIMIT. To limit result set size use SELECT TOP 1 *. Using TOP (or any similar result set restrictor) without ORDER BY is illogical. Specify an ORDER BY



    In sqlserver, output parameters must be passed with the OUTPUT keyword when calling the procedure:



    EXEC sprocname @inputparameter ='value', @outputparameter = @variableToSet OUTPUT;


    Use semicolons; omitting them is deprecated




    Example



    USE [DB]
    GO

    CREATE PROCEDURE [dbo].[GET]
    (@in VARCHAR(10), @OutPk VARCHAR(10) OUTPUT)
    AS
    SELECT @OutPK = tab.Col
    FROM table tab
    ORDER BY tab.Col;
    GO

    DECLARE @output VARCHAR(10);

    EXECUTE dbo.GET @in = 'table', @OutPK = @output OUTPUT

    SELECT @output;






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 12 '18 at 6:34

























    answered Nov 12 '18 at 6:29









    Caius JardCaius Jard

    10.5k21138




    10.5k21138











    • Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
      – NewPy
      Nov 13 '18 at 3:02











    • I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
      – Caius Jard
      Nov 13 '18 at 7:39

















    • Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
      – NewPy
      Nov 13 '18 at 3:02











    • I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
      – Caius Jard
      Nov 13 '18 at 7:39
















    Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
    – NewPy
    Nov 13 '18 at 3:02





    Actually it was my typo. I did use @Out = tab.Col in the SP. Would you please explain why it was still not returning right result?
    – NewPy
    Nov 13 '18 at 3:02













    I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
    – Caius Jard
    Nov 13 '18 at 7:39





    I expect it was the missing OUTPUT keyword when executing the proc. You probably managed to arrange a situation where @out had a value thanks to being set in the procedure, but @output could only ever get a value from being set in the EXECUTE call, and omitting the OUTPUT keyword meant that didn’t happen. Long story short; remember the output keyword when you pass a parameter to an output argument of a stored procedure
    – Caius Jard
    Nov 13 '18 at 7:39














    0














    If its MySql (Limit is in mySql), you can simply call:



    Call dbo.GET('table', @out);


    No need to have separate variable @output.






    share|improve this answer

























      0














      If its MySql (Limit is in mySql), you can simply call:



      Call dbo.GET('table', @out);


      No need to have separate variable @output.






      share|improve this answer























        0












        0








        0






        If its MySql (Limit is in mySql), you can simply call:



        Call dbo.GET('table', @out);


        No need to have separate variable @output.






        share|improve this answer












        If its MySql (Limit is in mySql), you can simply call:



        Call dbo.GET('table', @out);


        No need to have separate variable @output.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 12 '18 at 6:10









        GauravsaGauravsa

        2,3251816




        2,3251816



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53255642%2fstored-procedure-returns-0-instead-of-value%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Use pre created SQLite database for Android project in kotlin

            Darth Vader #20

            Ondo