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.

Recommended Answers

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

Jump to Post

All 3 Replies

I want SELECT @companyid = SCOPE_IDENTITY()

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.