Link to home
Start Free TrialLog in
Avatar of LelloLello
LelloLello

asked on

Procedure in SQL 2000.

I have a stored procedure on my SQL 2000. How i can run this in SQL 2000.
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LelloLello
LelloLello

ASKER

So i have two, this the first one
create procedure sp_CreateYear(@NewYear integer)
as
begin
  declare @local_var integer
  declare @kount integer

truncate table Year_Prep

select @kount = count(*)
from name
where ID NOT IN (select id from CIA_MULTIPLE where [CIA_YEAR] = @NewYear)

update Counter set
  LAST_VALUE = LAST_VALUE+@kount,
  LAST_UPDATED = getdate(),
  UPDATED_BY = user_name()
  where COUNTER_NAME = 'CIA_MULTIPLE'

select @local_var = LAST_VALUE
from Counter
where COUNTER_NAME = 'CIA_MULTIPLE'

select @local_var = @local_var - @kount

insert into Year_Prep (ID, NEW_YEAR)
select ID, @NewYear
from name
where ID NOT IN (select id from CIA_MULTIPLE where [CIA_YEAR] = @NewYear)

insert into CIA_MULTIPLE (ID, SEQN, [CIA_YEAR])
select ID, SEQN+@local_var, NEW_YEAR
from Year_Prep

end


GO


this is the second one.



CREATE  PROCEDURE sp_CreateCIAYearDescendingOrder AS
/* -------------------------------------------------------------*
 * Project:             
 * iMIS:            
 * Created By:        
 * Last Modified:        09/22/03

records are inserted once a year into the two tables cia_multiple and IAA_MULTIPLE. When the user receives the
data from the member the information is entered into the current year.

They want the current year at the top - descending order. Easier to review/enter most current year, avoids
scrolling down.


 *______________________________________________________________*/

SET NOCOUNT ON

declare @SPMSG             varchar(255)
declare @nError       integer
declare @bTrans            bit

declare @nprep_count      integer
declare @nprod_count      integer

--Clear temp table
truncate table CIA_MULTIPLE_PREP

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Truncate CIA_MULTIPLE_PREP'
   goto SP_ERR
END

--Load temp table in proper order with year descending
Insert CIA_MULTIPLE_PREP
(
[ID],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
)
select
[ID],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
from CIA_MULTIPLE
order by ID, [CIA_YEAR] DESC

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Insert CIA_MULTIPLE_PREP'
   goto SP_ERR
END

select @nprep_count = count(*) from  CIA_MULTIPLE_PREP
select @nprod_count = count(*) from  CIA_MULTIPLE

select '@nprep_count = ' + cast(@nprep_count as varchar(10))
select '@nprod_count = ' + cast(@nprod_count as varchar(10))

if @nprep_count <> @nprod_count BEGIN
   select @SPMSG =  'ERROR - Recounts do not match CIA_MULTIPLE_PREP/CIA_MULTIPLE'
   goto SP_ERR
   END

begin   transaction
Set @bTrans = 1

--clear ud table
truncate table CIA_MULTIPLE

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Truncate CIA_MULTIPLE'
   goto SP_ERR
END

--Load ud table from temp table with year descending
Insert CIA_MULTIPLE
(
[ID],
[SEQN],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
)
select
[ID],
[SEQN],
[CIA_YEAR],
[BALLOT_1],
[BALLOT_2],
[IAA_BATCH],
[IAA_ASTIN],
[IAA_AFIR],
[IAA_IAA],
[IAA_IACA]
from CIA_MULTIPLE_PREP
order by ID, [CIA_YEAR] DESC

select @nError=@@ERROR
if @nError<>0
BEGIN
   select @SPMSG =  'ERROR - Insert CIA_MULTIPLE_PREP'
   goto SP_ERR
END

declare @maxSeqn int

select @maxSeqn = max(seqn) from cia_multiple

update counter
set last_value = @maxSeqn
from counter
where counter_name = 'CIA_MULTIPLE'

select @SPMSG =  'Complete'
select @SPMSG

SP_EXIT:

if @bTrans = 1 begin
      If @nError <> 0
            rollback  transaction
      else
            commit transaction
      end

return @nError

SP_ERR:
select @SPMSG =  @SPMSG + ' ERROR = '+ convert(varchar(50),@nError) + ' DateTime = '+ convert(varchar(50),GetDate())

RAISERROR ('sp_IMPORTFR_Receipts: %d ', 25, 1, @SPMSG)  with LOG

goto SP_EXIT

/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CIA_MULTIPLE_PREP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CIA_MULTIPLE_PREP]

CREATE TABLE [dbo].[CIA_MULTIPLE_PREP] (
      [ID] [varchar] (10) NOT NULL ,
      [SEQN] [int] IDENTITY NOT NULL ,
      [CIA_YEAR] [float] NOT NULL ,
      [BALLOT_1] [int] NOT NULL ,
      [BALLOT_2] [int] NOT NULL ,
      [IAA_BATCH] [int] NOT NULL ,
      [IAA_ASTIN] [bit] NOT NULL ,
      [IAA_AFIR] [bit] NOT NULL ,
      [IAA_IAA] [bit] NOT NULL ,
      [IAA_IACA] [bit] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[CIA_MULTIPLE_PREP] WITH NOCHECK ADD
      CONSTRAINT [DF__CIAP_MULTIPLE__ID__65C116E7] DEFAULT ('') FOR [ID],
      CONSTRAINT [DF__CIAP_MULTI__CIA_Y__67A95F59] DEFAULT (0) FOR [CIA_YEAR],
      CONSTRAINT [DF__CIAP_MULTI__BALLO__689D8392] DEFAULT (0) FOR [BALLOT_1],
      CONSTRAINT [DF__CIAP_MULTI__BALLO__6991A7CB] DEFAULT (0) FOR [BALLOT_2],
      CONSTRAINT [DF__CIAP_MULTI__IAA_B__6A85CC04] DEFAULT (0) FOR [IAA_BATCH],
      CONSTRAINT [DF__CIAP_MULTI__IAA_A__6B79F03D] DEFAULT (0) FOR [IAA_ASTIN],
      CONSTRAINT [DF__CIAP_MULTI__IAA_A__6C6E1476] DEFAULT (0) FOR [IAA_AFIR],
      CONSTRAINT [DF__CIAP_MULTI__IAA_I__6D6238AF] DEFAULT (0) FOR [IAA_IAA],
      CONSTRAINT [DF__CIAP_MULTI__IAA_I__6E565CE8] DEFAULT (0) FOR [IAA_IACA],
      CONSTRAINT [pkCIAP_MULTIPLEID] PRIMARY KEY  CLUSTERED
      (
            [ID],
            [SEQN]
      )  ON [PRIMARY]


SELECT     ID, SEQN, CIA_YEAR
FROM         CIA_MULTIPLE   --CIATest db
WHERE     (ID = '650069')

--BEFORE
ID         SEQN        CIA_YEAR                                              
---------- ----------- -----------------------------------------------------
650069     55          2002.0
650069     4041        2003.0
650069     6467        1999.0
650069     7207        1998.0
650069     7959        1997.0
650069     8506        1996.0

--After
ID         SEQN        CIA_YEAR                                              
---------- ----------- -----------------------------------------------------
650069     1103        2003.0
650069     1104        2002.0
650069     1105        1999.0
650069     1106        1998.0
650069     1107        1997.0
650069     1108        1996.0


SELECT     ID, SEQN, YEAR
FROM         IAA_MULTIPLE  -- IAATest db
WHERE     (ID = '100001')

--BEFORE
ID         SEQN        YEAR
---------- ----------- ----
100001     70774       2003
100001     34208       2002
100001     2           2001
100001     165         2000
100001     0           1999

--After
ID         SEQN        YEAR
---------- ----------- ----
100001     6           2003
100001     7           2002
100001     8           2001
100001     9           2000
100001     10          1999

*/

GO
where i can type this... i didn't find the task ?

exec YourStoredProcedureName
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How do you open a query window connecting to the sql server.  Put this in the window and execute:  I have SQL Server Enterprise Manager 2000.
I have SQL Server Group
            LOCAL
                   Databases
                           MyDATABASE
                                       Stored Procedures.
Any idea where i should type the exec ????