Add DECLARE and SET parameters in INSERT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Add DECLARE and SET parameters in INSERT statement

Author

Topic

aturner
Starting Member

29 Posts

Posted - 2004-10-25 : 10:23:18

I have two tables that are called ReceiptAuto and Receipts. In both tables is a field called "ReceiptAuto" that automatically generates a unique number based upon three types of fields: 1) cost center that is called deptid in the the users table; 2) current year, and 3) sequential number - 00001. The entire field would be displayed as: 2030400001.

Scenario: When a user logs into an application it ties the emplid and deptid (cost center) together. However, the deptid will not displayed. In the Receipts table I have declared several variables that I need to tie into the users table. Question 1: How do I change "SET @costCenter = '123'" which associates the userid and deptid from the USERS table? Question 2: In order to have the ReceiptAuto to generate the cost center, year and sequential number when a record is inserted within the stored procedure, do I move the SET @costcenter into the VALUE (ReceiptAuto...) or do a SET @receiptauto = (SELECT statement)? (See tables below)

Receipts Table:

CREATE PROCEDURE dbo.spInsert_Receipts
@ReceiptID int,
@ReceiptAuto char(12), (This is the ReceiptAutoID from the ReceiptAuto table. Is this the correct datatype?)
@CityID int,
@RcptDate datetime out,
@RcptAmt numeric(18,0),
@OfficeID int,
@PymtTypeID int,
@CheckNum char(10),
@RecdFrom char(10),
@CaseNumber varchar(15),
@CasePlaintiff varchar(30),
@CaseDefendant varchar(30),
@CourtName varchar(30),
@SubpeonaDate datetime,
@UserID int,
@Waived bit,
@WaivedReason varchar(512),
@Voided bit,
@VoidDate datetime out,
@VoidReason varchar(512)
AS
--
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON

DECLARE @costCenter char(3), @nextNum int

SET @costCenter = '123' (this needs to tie into the users table based upon userid and deptid, see below)
SET @nextNum = (select ReceiptAutoID from ReceiptAuto)

UPDATE ReceiptAuto
SET ReceiptAutoID = ReceiptAutoID + 1

SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5)) (I need to move a copy of this to the @ReceiptAuto field, but I'm not sure how to display it correctly)

IF @RcptDate Is Null
SET @RcptDate = (getdate())
IF @VoidDate Is Null
SET @VoidDate = (getdate())

INSERT INTO [Receipts] (
ReceiptAuto, (do I need this field here, since it's being automatically created from the ReceiptAuto table)
CityID,
RcptDate,
RcptAmt,
OfficeID,
PymtTypeID,
CheckNum,
RecdFrom,
CaseNumber,
CasePlaintiff,
CaseDefendant,
CourtName,
SubpeonaDate,
UserID,
Waived,
WaivedReason,
Voided,
VoidDate,
VoidReason)
Values (
@ReceiptAuto = SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5)) Is this correct?
@CityID,
@RcptDate,
@RcptAmt,
@OfficeID,
@PymtTypeID,
@CheckNum,
@RecdFrom,
@CaseNumber,
@CasePlaintiff,
@CaseDefendant,
@CourtName,
@SubpeonaDate,
@UserID,
@Waived,
@WaivedReason,
@Voided,
@VoidDate,
@VoidReason)
GO

ReceiptAuto Table:

ReceiptAutoID int

Users Table:

CREATE View dbo.vwSelect_Users
AS
SELECT
UserID,
LogEmpID,
Password,
SecLvlID,
RTRIM(EMPLID) AS EmpID,
LTRIM(RTRIM(LAST_NAME)) + ', ' + LTRIM(RTRIM(FIRST_NAME)) AS "Full Name",
RTRIM(SUBSTRING(DEPTID, 3, 3)) + ' - ' + DESCR AS "Cost Center",
DateCreated,
DateLastLogin,
InActiveDate
FROM [Users]

Sample Data:

1 10200400001 24 Central Station - 10 2004-10-19 07:52:58.310 71 2 NULL 1010 Cottontail, Peter 123456789 Cottontail, Peter Bunny, Bugs San Bernardino Municipal 2004-10-01 00:00:00.000 360 NULL NULL NULL 2004-10-19 07:52:58.310 NULL
2 16200400002 20 City: Highland - 16 2004-10-21 13:02:39.060 120 1 980 4312 Miller, Barney 223459811 Bird, Tweety Stallone, Sylvester Chino Court 2004-11-02 00:00:00.000 360 1 On Welfare NULL 2004-10-21 13:02:39.060 NULL
3 57200400003 2 City: Apple Valley - 57 2004-10-22 10:04:29.047 52 1 452 NULL Stallone, Sylvester 465398712 Sixkiller, Paul Poindexter, Ernesto Victorville Municipal 2005-01-15 00:00:00.000 360 NULL NULL NULL NULL NULL
4 30200400004 20 City: Rancho Cucamonga - 30 2004-10-22 10:14:32.547 45 2 NULL 2365 Bird, Tweey NULL NULL NULL NULL NULL 360 NULL NULL NULL NULL NULL

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 11:44:01

wouldn't be easier to create a function which creates the id and put that for a default value in a column?

Go with the flow & have fun! Else fight the flow

aturner
Starting Member

29 Posts

Posted - 2004-10-25 : 11:46:25

I never used a function, so I'm not sure how to incorporate what I currently have in my stored procedure to a function. Would it be hard to show me an example with the information I provided in my first request.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 12:07:50

acctually you can't because you have
UPDATE ReceiptAuto
SET ReceiptAutoID = ReceiptAutoID + 1

i didn't see that...

to answer you questions:
1. you don't need this after the update but u do in insert.
SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5)) (I need to move a copy of this to the @ReceiptAuto field, but I'm not sure how to display it correctly)

2. i believe you'd need
SET @nextNum = (select max(ReceiptAutoID) from ReceiptAuto)

as far as i can tell...

Go with the flow & have fun! Else fight the flow

aturner
Starting Member

29 Posts

Posted - 2004-10-25 : 12:21:55

Do I still need this "SET @costCenter = '123'"? If so, how can I change the '123' to add a select statement that verifies the LogEmpId and DeptID from the Users table, which is the same as the cost center and is being used to verify that the user and deptid match the department or division they are assigned to.

Users Table:

CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [char] (30) AS NULL ,
[Password] [char] (30) AS NULL ,
[SecLvlID] [int] NULL ,
[OfficeID] [int] NULL ,
[EmpLID] [char] (11) AS NULL ,
[Last_Name] [varchar] (30) AS NULL ,
[First_Name] [varchar] (30) AS NULL ,
[DeptID] [char] (5) AS NULL,
[DateCreated] [datetime] NULL ,
[DateLastLogin] [datetime] NULL ,
[InActiveDate] [datetime] NULL
) ON [PRIMARY]
GO

Also, how do I correctly place "SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5)) " variable next to the @ReceiptAuto under VALUEs field? Or, do I really need it their if I declared above the INSERT statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources