Stored procedure returns 0 instead of value
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
add a comment |
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
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 usingmysql
,postgresql
,sql-server
,oracle
ordb2
- or something else entirely. The code appears to be for SQL Server, butLIMIT 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 useOUTPUT
when calling a stored proc with an output variable.
– Nick.McDermaid
Nov 12 '18 at 5:35
add a comment |
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
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
sql sql-server rdbms
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 usingmysql
,postgresql
,sql-server
,oracle
ordb2
- or something else entirely. The code appears to be for SQL Server, butLIMIT 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 useOUTPUT
when calling a stored proc with an output variable.
– Nick.McDermaid
Nov 12 '18 at 5:35
add a comment |
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 usingmysql
,postgresql
,sql-server
,oracle
ordb2
- or something else entirely. The code appears to be for SQL Server, butLIMIT 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 useOUTPUT
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
add a comment |
2 Answers
2
active
oldest
votes
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;
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
add a comment |
If its MySql (Limit is in mySql), you can simply call:
Call dbo.GET('table', @out);
No need to have separate variable @output
.
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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;
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
add a comment |
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;
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
add a comment |
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;
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;
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
add a comment |
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
add a comment |
If its MySql (Limit is in mySql), you can simply call:
Call dbo.GET('table', @out);
No need to have separate variable @output
.
add a comment |
If its MySql (Limit is in mySql), you can simply call:
Call dbo.GET('table', @out);
No need to have separate variable @output
.
add a comment |
If its MySql (Limit is in mySql), you can simply call:
Call dbo.GET('table', @out);
No need to have separate variable @output
.
If its MySql (Limit is in mySql), you can simply call:
Call dbo.GET('table', @out);
No need to have separate variable @output
.
answered Nov 12 '18 at 6:10
GauravsaGauravsa
2,3251816
2,3251816
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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
ordb2
- or something else entirely. The code appears to be for SQL Server, butLIMIT 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 useOUTPUT
when calling a stored proc with an output variable.– Nick.McDermaid
Nov 12 '18 at 5:35