0

I have created the following stored procedure in sql server 2005.I m using asp.net with C# 2005.
Can someone please rectify my errors why i m getting such type of the errors...

ALTER PROCEDURE CompanyStoredProcedure
    @uspcompanyid INT NULL,
    @uspcompanyname VARCHAR(20),
    @uspaddress1 VARCHAR(30),
    @frmErrorMessage AS VARCHAR(256) OUTPUT,
    @RETURNVALUE AS INT OUTPUT,
    @RETURNID AS INT OUTPUT
AS 
BEGIN

SET NOCOUNT ON

/*

RETURN_VALUE Comments
1            Data Inserted
2            Data Updated 
-9            Other errors
*/
    DECLARE @companyid INT,
        @companyname VARCHAR(20),
        @address1 VARCHAR(30) 

--validation...
IF ( @uspcompanyname IS NULL OR @uspcompanyname = '' ) 
    BEGIN
        SET  @RETURNVALUE = -9
        SET  @frmErrorMessage = 'Company Name is empty'
        RETURN -9                        
    END     

IF EXISTS ( SELECT  * FROM Companymaster WHERE Companyid = @companyid ) 
    BEGIN
        UPDATE Company
        SET companyname = @companyname,
                address1 = @address1
        WHERE    companyid= @uspcompanyid

        SET  @frmErrorMessage = 'Company Name/Address  has been updated'
        SET @RETURNVALUE = 2
    END
ELSE 
    BEGIN
        INSERT  INTO companymaster ( companyname, address1 )
        VALUES  (@companyname,@address1)

        SET  @frmErrorMessage = 'Company Name/Address info has been Inserted'
        SET @RETURNVALUE = 1
    END

SET NOCOUNT OFF     
END

THANXS in advance.

2
Contributors
3
Replies
4
Views
10 Years
Discussion Span
Last Post by davidcairns
0

You don't need those declared variables, use the ones passed in as parameters (@upscompanyid, @upscompanyname and @upsaddress1). Delete the declared variables and make sure you are only using the parameters

0

Also you will need to declare another parameter as the OUTPUT version of companyid. This would then be set using the SCOPE_IDENTITY function after the insert

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.