Hi,
I've tried in my novice state tried to create a stored
procedure, but
before I show you this perhaps I could give you an insight
into what it is
that I am trying to do.
I have three tables -
dbo.JBClient
JBCLID int(PK)
JBCLSiteID int
JBCLName nvarchar(50)
JBCLAddress nvarchar(500)
JBCLEmail nvarchar(225)
JBCLAccountType nvarchar(50)
JBCLAccountLive nvarchar(50)
JBCLBillingContact nvarchar(100)
JBCLreguserMonthlyOverwrite int
dbo.JBInvoiceBatch
JBIID int(PK)
JBISiteID int
JBIProduct nvarchar(50)
JBIDate datetime
dbo.AccountInvoice
JBACIID int(PK) (I'm contemplating removing this)
JBACISiteID int
JBACIBatchID int
JBACIClientID int
JBACIAccountOverwrite int
JBACIBillingContact nvarchar(100)
JBACIClientEmail nvarchar(225)
JBACIAccountType nvarchar(50)
JBACIClientName nvarchar(50)
JBACIClientAddress nvarchar(500)
This is a web based program, the idea is that when the site
admin presses a
button, the system creates a new invoice batch for the
relevant site. so site
admin for site1 presses a button and the current data is
inserted into
dbo.JBInvoiceBatch.
JBIID Autonumber
JBISiteID 1 (variable)
JBIProduct ACCOUNT (Variable)
JBIDate The Current Date (not including time)
the button that is pressed looks like this
runinvoice.asp?SiteID=1&Product=ACCOUNT
At the same time the stored procedure searches for all client
records
(dbo.Client) where JBCLSiteID = 1 (variable) AND
(JBCLAccountType = 'Reg
User' OR JBCLAccountType = 'Reg User5' OR JBCLAccountType =
'Reg User10' OR
JBCLAccountType = 'Multi User') AND JBCLAccountLive = 'y'
It then inserts the following information into
dbo.AccountInvoice
JBACIID = AUTONUMBER
JBACISiteID = 1 (variable)
JBACIBatchID = JBIID from dbo.JBInvoiceBatch
JBACIClientID = JBCLID from dbo.JBClient
JBACIAccountOverwrite = JBCLreguserMonthlyOverwrite
JBACIBillingContact = JBCLBillingContact
JBACIClientEmail = JBCLEmail
JBACIAccountType = JBCLAccountType
JBACIClientName = JBCLName
JBACIClientAddress = JBCLAddress
The thing is that there could be 1000 clients that meet the
criteria.
The store procedure that I've attempted is below -
CREATE PROCEDURE AccountInvoice
@JBCLID int,
@JBCLName nvarchar(50),
@JBCLAccountType nvarchar(50),
@JBCLBillingContact nvarchar(100),
@JBCLEmail nvarchar(225),
@JBCLAddress nvarchar(500),
@JBCLreguserMonthlyOverwrite nvarchar(50)
@JBIDate datetime,
@SiteID int,
@Product nvarchar(50),
AS
Declare @NewID INT
INSERT INTO dbo.JBInvoiceBatch(JBISiteID, JBIProduct,
JBIDate)
VALUES (@SiteID, @Product, GETDATE())
SELECT @NewID = SCOPE_IDENTITY()
INSERT INTO dbo.JBAccountInvoice(JBACIClientID, JBACISiteID,
JBACIBatchID,
JBACIAccountOverwrite, JBACIBillingContact, JBACIClientEmail,
JBACIAccountType, JBACIClientName, JBACIClientAddress)
VALUES (@JBCLID, @SiteID, @NewID,
@JBCLreguserMonthlyOverwrite,
@JBCLBillingContact, @JBCLEmail, @JBCLAccountType, @JBCLName,
@JBCLAddress)
FROM dbo.JBClient
WHERE @JBCLSiteID = SiteID and JBCLAccountLive = 'y' and
(JBCLAccountType =
'Reg User' OR JBCLAccountType = 'Reg User5' OR
JBCLAccountType = 'Reg User10'
OR JBCLAccountType = 'Multi User')
Hope you can help me with this -
Thanks again
Gareth
1
Reply
AdChoices