473,287 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Need Help with a Stored Procedure Syntax

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.
Jul 20 '05 #1
7 6475
>> 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 )
Jul 20 '05 #2
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!
Jul 20 '05 #3
Please read: http://www.aspfaq.com/5006

--
- Anith
( Please reply to newsgroups only )
Jul 20 '05 #4

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!
Jul 20 '05 #5
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
Jul 20 '05 #6
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 )
Jul 20 '05 #7
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
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rich R | last post by:
First, let me introduce myself to the group. My name is Rich and I've been doing databases for about 25 years with the last 10 being pretty much MS SQL Server. I've become intrigued with MySQL and...
2
by: E.N. | last post by:
Hi For testing & developing I try to create a stored proc using the mysql query tool. But it complains about syntax. I've tried to see what help and other samples could return but they do not...
18
by: Jarrod Morrison | last post by:
Hi All I was wondering if there is a way to call a stored procedure from inside another stored procedure. So for example my first procedure will call a second stored procedure which when...
5
by: Ralph | last post by:
Hi all, I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to implement a logic to receive an adress build out of various user definable fields from various user defined...
2
by: Caro | last post by:
I have a stored procedure spGetAccessLogDynamic and when I try to call it I get the following error: Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'S'. I dont...
5
by: MS | last post by:
Here's my simple stored procedure: ALTER PROCEDURE GetMemberIDByEmail @Email EmailAddress, @ID int OUTPUT AS SELECT @ID = ID FROM tbl_Member WHERE Email=@Email RETURN
3
by: Jack Black | last post by:
Help!! I'm trying to call a custom stored procedure from a VB.Net code-behind page in an ASP.Net application, and I keep getting an error with no real helpful info... Basically, I'm accepting a...
0
by: cuddles | last post by:
Hi, Im very new to this and i need some urgent help. I'm converting a COBOL stored procedure into an SQL stored procedure with logic. Im using IBM DB2 and im not sure about the syntax of the...
5
by: bbawa1 | last post by:
I have the following stroed procedue. But whebnnI execute it it gives me following errors. Could you please tell me what is wrong Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.