Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: Must declare the scalar variables in storedproce

    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...


    Code:
    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. #2
    Join Date
    Apr 2007
    Posts
    5
    I want SELECT @companyid = SCOPE_IDENTITY()where can i add this ??

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    Already asked here
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81615
    and on wrox with two other different aliases.

  4. #4
    Join Date
    Apr 2007
    Posts
    5
    oh,this was seen as we are doing project in a group.One query can be asked by all the group members....
    So,doubt must be the same for the whole group.

    It's fine thanxs ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •