Must declare the scalar variables in storedprocedure in sql 2005
 

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.

Re: Must declare the scalar variables in storedprocedure in sql 2005
 

I want SELECT @companyid = SCOPE_IDENTITY()

Re: Must declare the scalar variables in storedprocedure in sql 2005
 

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

Re: Must declare the scalar variables in storedprocedure in sql 2005
 

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