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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
LelloLello

8/22/2022 - Mon
SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
LelloLello

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

exec YourStoredProcedureName
ASKER CERTIFIED SOLUTION
mastoo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
LelloLello

ASKER
my sql
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
LelloLello

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

ASKER
I have SQL Server Group
            LOCAL
                   Databases
                           MyDATABASE
                                       Stored Procedures.
LelloLello

ASKER
Any idea where i should type the exec ????
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.