Stored procedure output parameter problem Why is the output always NULL?

sam7978465

The stored procedure code is as follows:
CREATE PROCEDURE UP_Production_Authenticate
@sOrdNo nvarchar (100),
@sResult nvarchar (30) output
AS
if not exists (select * from tblContract where sOrdNo = @ sOrdNo)
begin
set @ sResult = 'yes'
end
else
begin
set @ sResult = 'no'
end
return

After execution, the @sResult value is NULL anyway. The code tracked by the tracker is:
declare @ P1 bit
set @ P1 = NULL
exec sp_executesql N'UP_Production_Authenticate ', N' @ sOrdNo nvarchar (100), @ sResult bit output ', @sOrdNo = N'9042', @sResult = @ P1 output
select @ P1

Why does the system automatically assign a NULL value to @ P1?

michealhenry

CREATE PROCEDURE UP_Production_Authenticate
@sOrdNo nvarchar (100),
@sResult nvarchar (30) output
AS
set @sResult = ''
if not exists (select * from tblContract where sOrdNo = @ sOrdNo)
begin
set @ sResult = 'yes'
end
else
begin
set @ sResult = 'no'
end
return


--Try this?

cv4bf5d65fd

CREATE PROCEDURE UP_Production_Authenticate
@sOrdNo nvarchar (100),
@sResult nvarchar (30) output
AS
set @sResult = '' --- give an initial value first
if not exists (select * from tblContract where sOrdNo = @ sOrdNo)
begin
set @ sResult = 'yes'
end
else
begin
set @ sResult = 'no'
end
return

sam7978465

Useless, given the initial value, the execution tracking is still the same
declare @ P1 nvarchar (30)
set @ P1 = NULL
exec sp_executesql N'UP_Production_Authenticate ', N' @ sOrdNo nvarchar (100), @sSubOrdNo nvarchar (100), @sStaff nvarchar (100), @sResult nvarchar (30) output ', @sOrdNo = N'9042', @sSubOrdNo = N'14-002 ', @sStaff = N'lily', @sResult = @ P1 output
select @ P1


I have another stored procedure
CREATE PROCEDURE UP_frmMessage_Read
@sStaff nvarchar (10),
@sResult nvarchar (10) output
AS
  select iXh, sNo, sReceipt, sMessage, sSender, dNow from tblMessage where sReceipt = @ sStaff and sStatus <> 3
if @@ rowcount = 0
 set @ sResult = 'No'
else
 set @ sResult = 'Yes'

Tracked out is

declare @ P1 nvarchar (10)
set @ P1 = N'No '
exec UP_frmMessage_Read @sStaff = N'lily ', @sResult = @ P1 output
select @ P1

This does not give an initial value, why is the set @ P1 = N'No ', not a NULL value?

dream88

Write it like this during execution
declare @ P1 nvarchar (30)
set @ P1 = NULL
exec sp_executesql N'UP_Production_Authenticate ', N' @ sOrdNo nvarchar (100), @ sResult nvarchar (30) output ', @sOrdNo = N'9042', @sResult = @ P1 output
select @ P1

sam7978465

What's upstairs? I didn't understand

wgfxman

declare @ p1 varchar (1024)
SELECT @ p1 = ''
exec UP_Production_Authenticate N'9042 ', @ p1 output
select @ p1

There may be a problem with the calling method.

hiqwolfzzh

CREATE PROCEDURE UP_Production_Authenticate
@sOrdNo nvarchar (100),
@sResult nvarchar (30) output
AS
if not exists (select * from tblContract where sOrdNo = @ sOrdNo)
set @ sResult = 'yes'

else
set @ sResult = 'no'

return

sam7978465

wgfxman: You may understand it wrong, that section is not the code that is called, the code that is tracked out, the call is the stored procedure that is directly called.

hiqwolfzzh: That Begin..end can also be added, the same effect without adding it. I tried.

pupupu01

The code is correct. If it returns NULL, it is only possible that you called wrongly or that if was not executed at all.
Actual situation. Look carefully for your actual stored procedure.