Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    10

    Unanswered: create procedure, alter table problem

    Hi All,
    Ive a problem with creating a stored procedure that my limited knowledge of sql can't seem to get round. I've ran the script shown below without a problem in query analyzer but when I create it as a procedure and view it in enterprise manager the last select statement is removed.

    Any ideas?
    Thanks David

    (Using SQL Server 2003)

    CREATE PROCEDURE dbo.sp_dvk_rep_Hudson

    @Batch integer, /*= 2136*/
    @company_ varchar(4) /*= 'DB'*/
    AS
    DROP TABLE Hudson
    SELECT subc_, detail_,
    SUM(gross_ - citb_ )AS 'Hudson_Gross',
    SUM(materials_)AS 'Materials',
    SUM(tax_)AS 'Tax',
    ROUND( MIN(CAST(tax_ as numeric(10,6)) / CAST(Taxable_ as numeric(10,6))* 100) ,2)AS 'Tax Rate',
    SUM(vat_)AS 'VAT'
    INTO Hudson
    FROM SCtran
    WHERE trancode_ IN ('CERT', 'INV')
    and batchno_ = @Batch
    and co_ = @company_
    GROUP BY subc_, detail_

    ALTER TABLE Hudson
    ADD Fee_ money
    CONSTRAINT DF_Hudson_Fee_ DEFAULT 13.00 NOT NULL
    GO
    SELECT
    subc_,
    Hudson_Gross - Fee_ AS 'Hudson_Gross',
    Materials,
    Tax,
    Tax Rate,
    VAT,
    Fee_
    FROM Hudson

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this
    Code:
    CREATE PROCEDURE dbo.sp_dvk_rep_Hudson (
    	@Batch		integer, /*= 2136*/
    	@company_	varchar(4) /*= 'DB'*/
    )
    AS
    	SET NOCOUNT ON 
    
    	DROP TABLE Hudson;
    
    	SELECT subc_, 
    		detail_,
    		SUM(gross_ - citb_ )AS 'Hudson_Gross',
    		SUM(materials_)AS 'Materials',
    		SUM(tax_)AS 'Tax',
    		ROUND( MIN(CAST(tax_ as numeric(10,6)) / CAST(Taxable_ as numeric(10,6))* 100) ,2)AS 'Tax Rate',
    		SUM(vat_)AS 'VAT'
    	INTO Hudson
    	FROM SCtran
    	WHERE trancode_ IN ('CERT', 'INV')
    		and batchno_ = @Batch
    		and co_ = @company_
    	GROUP BY subc_, detail_;
    
    	ALTER TABLE Hudson
    		ADD Fee_ money 
    		CONSTRAINT DF_Hudson_Fee_ DEFAULT 13.00 NOT NULL;
    
    	SELECT
    		subc_,
    		Hudson_Gross - Fee_ AS 'Hudson_Gross',
    		Materials,
    		Tax,
    		Tax Rate,
    		VAT,
    		Fee_
    	FROM Hudson;
    
    	SET NOCOUNT OFF
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2009
    Posts
    10
    That works greatWim. Thank you for your help. David

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by davidk1 View Post
    That works greatWim. Thank you for your help. David
    The GREATWim

    However

    I REALLY Don't think you want this in a stored procedure

    How many times are you going to execute this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2009
    Posts
    10
    Thank you for your reply Brett, low use of the sp, once or twice a week. The script that Wim helped me out with retains the last select statement after creation but results in the error message 'Invalid Column 'Fee_'' on execution. Any ideas?

    Regards
    David
    Last edited by davidk1; 12-03-09 at 18:29.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Brett Kaiser View Post
    ...
    I REALLY Don't think you want this in a stored procedure
    ....
    The GREATBrett's right.

    You may consider creating the Hudson table outside the SP and do a TRUNCATE TABLE Hudson in the sp. It would also solve the 'Invalid Column 'Fee_'' error.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Dec 2009
    Posts
    10
    Hi Wim

    Took your advice and created the table outside of the SP. Works fine, thanks again.
    David

Posting Permissions

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