Proc Help: Insert/Update With @@Identity

[baldwingrand]

The goal of this stored procedure is to populate the CaseNumber field of the Request table with three other fields: RequestID, DepartmentID, and InputDate. When I run it, all data is inserted into the table except the CaseNumber (that is NULL), so the part of this proc that is supposed to create the CaseNumber is not working. What am I doing wrong? (NOTE: I'm relatively new to SQL). Thanks.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spRequest]
(
@vDepartmentID varchar (50),
@vDescription varchar(500),
@vDollarAmount money,
@vAccount varchar (15),
)

AS

--Insert values into Request table.
BEGIN

Declare @vCaseNumber varchar(11)
Declare @vNewRequestID int

Insert Request (DepartmentID, Description, DollarAmount, Account)
Values (@vDepartmentID, @vDescription, @vDollarAmount, @vAccount)

Select @vNewRequestID = @@Identity

--Create CaseNumber
Select @vCaseNumber =

--DepartmentID field of Request table, padded with 3 zeros
right('000' + cast(@vDepartmentID As varchar),3)+

--InputDate field of Request table, in Julian format
cast(datepart(dayofyear,getDate()) As varchar)+

--RequestID field of Request table, padded with 5 zeros
right('00000' + cast(@vNewRequestID As varchar),5)

Update Request
Set CaseNumber = @vCaseNumber
Where RequestID = @vNewRequestID

END

[chazzy]

does that procedure even compile? i see a number of potential issues with it; but i don't have a sql server compiler so i can't verify.

[baldwingrand]

It does. No errors. To add insult to injury, it was working perfectly until a few days ago, but I didn't change anything. If you have some suggestions based on the issues you see with it, I'd be eternally grateful. I know very little about using @@Identity and cast, so I am not even sure where to begin. Thanks!