Output parameter problem in sql server store proce

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Output parameter problem in sql server store proce

Author

Topic

Indrajit
Starting Member

11 Posts

Posted - 2012-06-15 : 01:40:40

Hi all
I am using SQL server and I created a store procedure but I am getting following error

<pre lang="vb">Server: Msg 201, Level 16, State 4, Procedure SP_New_OrderNo, Line 0
Procedure 'SP_New_OrderNo' expects parameter '@ERROR', which was not supplied.</pre>

Can any one please help me?

Bellow I am giving my Sore procedure

<pre lang="sql">CREATE PROCEDURE SP_New_OrderNo @CustomerNo NVARCHAR(10), @ModelNo NVARCHAR(10), @ERROR VARCHAR(100)OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CusNo NVARCHAR(50)
DECLARE @MdlNo NVARCHAR(50)
SELECT @CusNo=CustomerNo, @MdlNo=ModelNo FROM TblSerialNo WHERE CustomerNo=@CustomerNo
IF @CusNo=@CustomerNo
BEGIN
IF @MdlNo=@ModelNo
BEGIN
SET @ERROR = 'THIS CUSTOMER NO. AND MODEL NO. IS IN DATABASE PLEASE ENTER SERIAL NUMBER'
END
ELSE
BEGIN
SET @ERROR = 'THIS CUSTOMER NO. IS IN DATABASE PLEASE ENTER MODELNO AND SERIAL NUMBER'
END
END
ELSE
BEGIN
SET @ERROR = 'PLEASE ENTER CUSTOMER NO.'
INSERT INTO TblSerialNo (CustomerNo, ModelNo) VALUES (@CustomerNo, @ModelNo)
END

END

GO

EXEC SP_New_OrderNo '111','123'</pre>

Thanks To all

IndrajitDasgupta

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-15 : 01:57:45

Output parameters behave similat to INPUT parameters. You need to specify the @Error in the EXEC.
EXEC SP_New_OrderNo '111','123',<place parameter> OUTPUT

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 15:59:56

usually way to do this is to return integer codes from procedure using RETURN statements. Then in front end application there will logic to capture return value and set appropriate error message to be diplayed to the user.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources