So i am making an erp program for practice from adventureworks2019 database in management studio in sql and one stored procedure with output parameters didnt run properly unless i specified their value as null, while others similar procedures did not have such an issue. Why? and are the other procedures faulty? What is the textbook way syntactically executing procedures with output parameters?, also any tips pls?
drop procedure if exists sohead;
create procedure sohead
@BusinessEntityid int,
@customerid int,
@persontype nchar(2) output,
@storeid int output,
@orderdate datetime output,
@duedate datetime output,
@accountnumber nvarchar(15) output,
@salespersonid int output,
@territoryid int output,
@billToAddressid int output,
@shipToAddressid int output,
@shipMethodid int,
@creditcardid int output,
@currencyrateid int =null,
@subtotal money,
@taxamt money,
@freight money,
–totaldue self generated and computed
@comment nvarchar(128)
AS
BEGIN
set nocount on;
begin try
begin transaction
--@storeid
select @storeid = storeid from sales.customer where CustomerID = @customerid;
--orderdate
SET @orderdate = GETDATE();
--duedate
SET @duedate = DATEADD(day, 15, @orderdate);
--salespersonid
if @storeid IS NULL
SET @salespersonid = NULL;
else
SELECT @salespersonid = SalesPersonID
FROM Sales.Store
WHERE BusinessEntityID = @storeid;
--territoryid
select @territoryid = territoryid from sales.Customer
where customerid = @customerid;
--biiltoaddress AND shipaddressid
--;WITH persontypecte AS (
-- SELECT persontype
-- FROM person.Person
-- WHERE BusinessEntityID = @businessentityid
-- )
--select @persontype = persontype from persontypecte
select @persontype = persontype FROM person.Person
WHERE BusinessEntityID = @businessentityid;
if @persontype = 'SC'
BEGIN
;with billshipadd as(
SELECT AddressID FROM
person.BusinessEntityAddress
where BusinessEntityid = @storeid
)
select @BILLTOADDRESSID = addressid,@SHIPTOADDRESSID = addressid from billshipadd; --address GIA 'SC'
END
ELSE IF @PERSONTYPE = 'IN'
BEGIN
;with billshipadd as(
SELECT AddressID FROM
person.BusinessEntityAddress
where BusinessEntityid = @businessentityid
)
select @BILLTOADDRESSID = addressid,@SHIPTOADDRESSID = addressid from billshipadd; --address gia 'IN'
END;
--creditcardid
select @creditcardid = CreditCardID from Sales.PersonCreditCard where businessentityid = @BusinessEntityid;
--accountnumber
select @accountnumber = accountnumber from sales.customer where customerid = @customerid;
--currencyrateid
--null
insert into sales.salesorderheader(revisionNumber, orderdate, duedate, shipdate, status,
onlineorderflag, purchaseordernumber, accountnumber, customerid, salespersonid, territoryid,
billtoaddressid, shiptoaddressid, shipmethodid, creditcardid, creditcardapprovalcode,
currencyrateid, subtotal, taxamt, freight, comment, rowguid, modifieddate)
VALUES(
0,
@orderdate,
@duedate,
null,
1,
0,
null,
@accountnumber,
@customerid,
@salespersonid,
@territoryid,
@billToAddressid,
@shipToAddressid,
@shipMethodid,
@creditcardid,
null, --credit card approval code
@currencyrateid,
@subtotal,
@taxamt,
@freight,
--@totaldue, --mono toy
@comment,
newid(),
getdate()
);
commit transaction
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
rollback transaction;
throw;
end catch
end;
exec sohead @businessentityid=1983 , @customerid=30113 ,@shipmethodid= 1, @subtotal =5,
@taxamt =5, @freight = 5, @comment = null,@persontype = null, @storeid = null,@orderdate = null, @duedate = null, @accountnumber= null, @salespersonid= null,
@territoryid = null, @billToAddressid = null, @shipToAddressid=null, @creditcardid = null;
Apparrently this is the right way to execute it
but i thought this was enough : exec sohead @businessentityid=1983 , @customerid=30113 ,@shipmethodid= 1, @subtotal =5,
@taxamt =5, @freight = 5;
user23592235 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.