473,715 Members | 2,142 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6495
>> 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 tblprassignment Days where BillingStatus = 'billnow'

--process though each selected record

if tblprassignment Days.ClientRegH ours or
tblprassignment Days.ClientOTHo urs not = 0 then
insert into
tblARInvoiceDet ailTemp
[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 tblprassignment days
[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),yea r(2),week(2),mo nth(2) leading zeros on assignid
[Job#] tblprassignment .AssignID
[DEBatchID] Chips batch import#

here is the DDL for tblPRassignment days

CREATE TABLE [dbo].[tblPRAssignment Days] (
[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_Gene ral_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[ProvWeekendCall] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ProvMiscelAmoun t] [numeric](8, 2) NULL ,
[ProvMiscelBillC lient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursO R] [bit] NULL ,
[InsuranceClassO R] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[InsuranceTotalA mount] [numeric](8, 2) NULL ,
[InsuranceTotalA mountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightChar ge] [bit] NULL ,
[ClientWeekendCh arge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[ClientMiscelAmo unt] [numeric](8, 2) NULL ,
[ClientRegHoursO R] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmou nt] [numeric](8, 2) NULL ,
[InvoiceCreateDa te] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE SQL_Latin1_Gene ral_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].[tblARInvoiceDet ailTemp] (
[Invoice_Line] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Invoice_ID] [varchar] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE SQL_Latin1_Gene ral_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].[tblARInvoiceDet ailTemp] (
[Invoice_Line] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Invoice_ID] [varchar] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPRAssignment Days] (
[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_Gene ral_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[ProvWeekendCall] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ProvMiscelAmoun t] [numeric](8, 2) NULL ,
[ProvMiscelBillC lient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursO R] [bit] NULL ,
[InsuranceClassO R] [varchar] (5) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[InsuranceTotalA mount] [numeric](8, 2) NULL ,
[InsuranceTotalA mountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightChar ge] [bit] NULL ,
[ClientWeekendCh arge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[ClientMiscelAmo unt] [numeric](8, 2) NULL ,
[ClientRegHoursO R] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmou nt] [numeric](8, 2) NULL ,
[InvoiceCreateDa te] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Updated] [smalldatetime] NULL ,
[Archive] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPRAssignment Days] WITH NOCHECK ADD
CONSTRAINT [PK_tblPRAssignm entDays] PRIMARY KEY CLUSTERED
(
[TimesheetDayID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPRAssignment Days] WITH NOCHECK ADD
CONSTRAINT [DF_tblPRAssignm entDays_Created] DEFAULT (getdate()) FOR
[Created],
CONSTRAINT [DF_tblPRAssignm entDays_Archive] DEFAULT (0) FOR [Archive]
GO

CREATE INDEX [ix_tblPRAssignm entDays_AssignI D] ON
[dbo].[tblPRAssignment Days]([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***@bizdatas olutions.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***@bizdatas olutions.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
1994
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 would love to use it on a project. I'm trying to work with stored procedures and am having a problem. Here goes: Software: Windows NT, VB6, MySQL 5.0.01-alpha-nt,MySQL ODBC 3.51 Here's my code:
2
3372
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 write about how it really must be done.. this is syntactically a right proc body create procedure doit
18
19481
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 executed will return one record and i want to use this data in the calling stored procedure. Is this possible ? Thanks in advance
5
3823
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 tables. The function is already implemented in the Client software and as UDF-compliant in MySQL and Oracle. Now there's just MS-SQL left... The problem now is for sure, I'm in need of a scalar return value (a varchar) composed out of a dynamic...
2
5726
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 know why because I dont have anything refering to stored procedure 'S' I have ran my SQL String with sample values and it works fine. So I
5
6520
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
1472
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 username and password from a front-end, and performing a simple INSERT into a SQL Server table via a custom stored procedure in the database. All privileges are fine, and the stored procedure works fine (inserts records perfectly; tested with...
0
1131
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 stored procedure i made. How do i check the syntax? i really dont know what to do, little help pls thanks
5
1782
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 Incorrect syntax near the keyword 'SELECT'. Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47 Incorrect syntax near the keyword 'select'. Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52 Incorrect syntax near the keyword...
0
9322
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9193
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9093
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9039
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7966
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6641
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4472
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4734
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2532
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.