Help with query please

dkspivey · June 17th, 2005, 10:30 AM

Hello. The code below is part of a larger stored procedure to generate record IDs. This portion of the code is giving the following error message:
Server: Msg 170, Level 15, State 1, Line 11

Code:

DECLARE @id_int int
DECLARE @id_text varchar(13)
DECLARE @id_temp varchar(13)
DECLARE @CurrentYr int

SET @CurrentYr = convert(int, year(getdate()))

SELECT @id_temp = vchRoutingID, 
case 
    when @CurrentYr = intCurrentYr then
    @id_int = CONVERT(INT,SUBSTRING(vchRoutingID,8,DATALENGTH(vchRoutingID)-5))
    when @CurrentYr > intCurrentYr then @id_int = 0 end
FROM tblRoutingID WITH (TABLOCKX,HOLDLOCK)

Individually, the lines work. So what might the problem be? Any help will be greatly appreciated. Thanks.

Jeff Mason · June 17th, 2005, 10:46 AM

Quote:

quote:Originally posted by dkspivey
Individually, the lines work. So what might the problem be?

I seriously doubt the CASE lines work.

In SQL, CASE ... END is an expression which returns a value; it is not a procedural construct with code in it like assignments.

Your SELECT should read something like:

Code:

SELECT @id_temp = vchRoutingID,
@id_int = 
    CASE 
        WHEN @CurrentYr = intCurrentYr THEN
            CONVERT(INT,SUBSTRING(vchRoutingID,8,DATALENGTH(vchRoutingID)-5))
        WHEN @CurrentYr > intCurrentYr THEN
            0
    END
FROM ...

Note also that you are using a SELECT from a table to assign local variables and this will fail if the SELECT returns more than one row ...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

dkspivey · June 17th, 2005, 11:31 AM

Hi Jeff!

Thanks so much for your help. The procedure works.

You're absolutely right about the CASE statement. What I should have said was that the lines worked if I removed the CASE statement.

Thanks again, Donna :D