Stored Procedure??!! Help!

Report · Jan 15, 2008

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

Report · Jan 17, 2008

I'm not sure what you have going on. What I do is create it in query
analyzerto ensure it works and then transfer to a stored procedure
adding the augments. Then test it in query analyzer with augments.
When that all passes, try it out in a page.