Need Help with a Stored Procedure Syntax

[Aleem]

I need help in writing a stored procedure on SQL Server 2000.
Basically the stored procedure's primary task is to generate invoice
records and insert the records in a invoice table. In order to
generate the invoice records, I have an initial table which are
basically Day Records. The task that I would like to complete is to
select all records from that initial table and I guess put them into a
temp table. Now that i have my temp table, I would like to loop thru
the table record by record, and do inserts in the invoice table. I
cant seem to figure out the syntax since I am somewhat weak in TSQL
Programming. I would appreciate any assistance on this forum or to my
email. Also If you need some pseudocode for the process or the DDL for
the initial table and the invoice table, I can definitely post that in
the forum possibly in the next thread. Please advise, I would need a
full written syntax to get me started since i have some other
processes that I would need to build using the template. Thanks again.

[Anith Sen]

>> Also If you need some pseudocode for the process or the DDL for the
initial table and the invoice table, I can definitely post that in the forum
possibly in the next thread <<

That will definitely help. Include sample data & expected results as well.

I would need a full written syntax to get me started ... <<

I think you are looking more at a specific approach rather than just syntax.
There is no better syntax reference than SQL Server Books Online.

--
- Anith
( Please reply to newsgroups only )

[Mansoor Aleem]

Thanks for offering me some help:

Here is the pseudocode for what i would like to do

Select tblprassignmentDays where BillingStatus = 'billnow'

--process though each selected record

if tblprassignmentDays.ClientRegHours or
tblprassignmentDays.ClientOTHours not = 0 then
insert into
tblARInvoiceDetailTemp
[Invoice_Line] Initially null until Invoice_ID is Generated
Concatenated Inv# + Line # (Unique)
[Invoice_ID] Initially null until Invoice_ID is Generated
[Date] AssignDate from tblprassignmentdays
[Units] see below
[Description] see below
[Quantity] if invoice is credit '-1' otherwise '01'
[BillType] see below
[Amount] see below
[GLAccount] see below
[SWKMonth] concatenated field
Assignid(6),year(2),week(2),month(2) leading zeros on assignid
[Job#] tblprassignment.AssignID
[DEBatchID] Chips batch import#

here is the DDL for tblPRassignmentdays

CREATE TABLE [dbo].[tblPRAssignmentDays] (
[TimesheetDayID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeSheetWeekID] [bigint] NULL ,
[AssignID] [int] NULL ,
[SlotID] [int] NULL ,
[AssignDate] [smalldatetime] NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvWeekendCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProvMiscelAmount] [numeric](8, 2) NULL ,
[ProvMiscelBillClient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursOR] [bit] NULL ,
[InsuranceClassOR] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InsuranceTotalAmount] [numeric](8, 2) NULL ,
[InsuranceTotalAmountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightCharge] [bit] NULL ,
[ClientWeekendCharge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ClientMiscelAmount] [numeric](8, 2) NULL ,
[ClientRegHoursOR] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmount] [numeric](8, 2) NULL ,
[InvoiceCreateDate] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [smalldatetime] NULL ,
[Archive] [bit] NULL
) ON [PRIMARY]
GO
here is the ddl for the table i would like to insert:

CREATE TABLE [dbo].[tblARInvoiceDetailTemp] (
[Invoice_Line] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Invoice_ID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Anith Sen]

Please read: http://www.aspfaq.com/5006

--
- Anith
( Please reply to newsgroups only )

[Mansoor Aleem]

CREATE TABLE [dbo].[tblARInvoiceDetailTemp] (
[Invoice_Line] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Invoice_ID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPRAssignmentDays] (
[TimesheetDayID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeSheetWeekID] [bigint] NULL ,
[AssignID] [int] NULL ,
[SlotID] [int] NULL ,
[AssignDate] [smalldatetime] NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvWeekendCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProvMiscelAmount] [numeric](8, 2) NULL ,
[ProvMiscelBillClient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursOR] [bit] NULL ,
[InsuranceClassOR] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InsuranceTotalAmount] [numeric](8, 2) NULL ,
[InsuranceTotalAmountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightCharge] [bit] NULL ,
[ClientWeekendCharge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ClientMiscelAmount] [numeric](8, 2) NULL ,
[ClientRegHoursOR] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmount] [numeric](8, 2) NULL ,
[InvoiceCreateDate] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [smalldatetime] NULL ,
[Archive] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPRAssignmentDays] WITH NOCHECK ADD
CONSTRAINT [PK_tblPRAssignmentDays] PRIMARY KEY CLUSTERED
(
[TimesheetDayID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPRAssignmentDays] WITH NOCHECK ADD
CONSTRAINT [DF_tblPRAssignmentDays_Created] DEFAULT (getdate()) FOR
[Created],
CONSTRAINT [DF_tblPRAssignmentDays_Archive] DEFAULT (0) FOR [Archive]
GO

CREATE INDEX [ix_tblPRAssignmentDays_AssignID] ON
[dbo].[tblPRAssignmentDays]([AssignID]) ON [PRIMARY]
GO


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Anith Sen (an***@bizdatasolutions.com) writes:

Please read: http://www.aspfaq.com/5006

Anith, are you sure you got the number right? I tried the link, but that
lead me to http://www.aspfaq.com/wholelist.asp.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anith Sen]

That is strange, though, probably you are using Avant/Mozilla? Let Aaron
know about it. I could see the link fine thru IE6.0. In any case, the link
simply gives a set of guidelines for posting DDLs & sample data. Here it is
again...

What does he mean when he asks for table structure and sample data?
Usually, narrative and a scribbled semblance of a table isn't enough to
fully understand the structure and nature of your data, at least not enough
to solve your problem. So that we can spend more time actually working on
your query (either solving an existing syntax or logic problem, or coming up
with a way to get the output in your desired format), it helps if we can
create the table on our system, and populate it with data, with minimal
effort. So, often, you will see us ask for table structure and sample data.
"Table structure" is the CREATE TABLE script that will allow us to
reproduce and work against your table on our own systems. You can generate
CREATE TABLE scripts from within Enterprise Manager by opening your
database, expanding tables, right-clicking the table(s) in question, and
choosing All Tasks > Generate SQL Script... Where appropriate, please
include keys, constraints, indexes and relationships - we don't like to
guess or make assumptions about your data, as they might impact the
solution. These options are provided in the GUI on the options page. I
usually make sure that "Script Indexes" and "Script PRIMARY keys, FOREIGN
keys, defaults and check constraints" are enabled, and that I select a file
format of Windows text (ANSI).

To provide "sample data," you can use this code from Vyas to generate
INSERT statements from your existing tables. Usually it is not necessary
recreate your entire data set, but make sure there is enough data to
reproduce the problem.

Please include the scripts inside a plain text newsgroup post. Attachments
are generally frowned upon, and often ignored / blocked.

--
- Anith
( Please reply to newsgroups only )

[Erland Sommarskog]

Anith Sen (an***@bizdatasolutions.com) writes:

That is strange, though, probably you are using Avant/Mozilla? Let Aaron
know about it. I could see the link fine thru IE6.0. In any case, the
link simply gives a set of guidelines for posting DDLs & sample data.
Here it is again...

Thanks! A little testing showed that Mozilla 1.21 manages the page, but
not Opera 6 or 7, nor Netscape 4.79.

I'll drop Aaron a line about it.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp