Case statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Case statement

Author

Topic

shareen
Starting Member

6 Posts

Posted - 2015-04-21 : 12:52:23

Hi

I have declared @SupplierDate and @year however I keep getting the
error 'Incorrect syntax near '='.'

CASE when @SupplierDate = '1' then
SUBSTRING((CONVERT(VarChar(8),po.OriginalPromDelDate),1, 4)) = @year
ELSe
SUBSTRING(CONVERT(VarChar(8),po.reqdate),1, 4) = @year
END

It is complaining about the equals next to @year.

Can you help

skhalil

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-21 : 13:12:17

Assuming you are trying to assign a value to the variable @year, you should do the following:

@year = 
	CASE when @SupplierDate = '1' then 
	SUBSTRING(CONVERT(VarChar(8),po.OriginalPromDelDate),1, 4)
	ELSe 
	SUBSTRING(CONVERT(VarChar(8),po.reqdate),1, 4)
	END

It might be shorter to use the following:

@year = 
	CASE WHEN @SupplierDate = '1' THEN YEAR(po.OriginalPromDelDate)
	ELSE YEAR(po.reqdate) END

What is @SupplierDate. Is it not a date? If it is, '1' does not seem to be what you want. Or is it some sort of flag?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-21 : 13:13:17

DECLARE @SupplierDate int , @year varchar(8)
DECLARE @OriginalPromDelDate date = '1/1/2013'--substitute for column po.OriginalPromDelDate
DECLARE @reqdate date = '1/1/2016'--substitute for column po.reqdate
SELECT @year = CASE when @SupplierDate = 1 THEN SUBSTRING(CONVERT(VarChar(8),@OriginalPromDelDate),1, 4)
ELSE SUBSTRING(CONVERT(VarChar(8),@reqdate),1, 4)
END
SELECT @year

--but you can simplyfy
GO
DECLARE @SupplierDate int , @year varchar(8)
DECLARE @OriginalPromDelDate date = '1/1/2013'--substitute for column po.OriginalPromDelDate
DECLARE @reqdate date = '1/1/2016'--substitute for column po.reqdate
SELECT @year = CASE when @SupplierDate = 1 THEN YEAR(@OriginalPromDelDate)
ELSE YEAR(@reqdate)
END
SELECT @year

shareen
Starting Member

6 Posts

Posted - 2015-04-22 : 05:31:54

Hi Guys

Thanks for your response my code works now and looks like the following

DECLARE @SupplierDate VarChar(50) set @SupplierDate = 'PickDate'

@Year =

CASE
WHEN @SupplierDate = 'OriginalPromDelDate' THEN
SUBSTRING(CONVERT(VarChar(8),po.OriginalPromDelDate),1, 4)
ELSE
SUBSTRING(CONVERT(VarChar(8),po.PickDate),1, 4)
END
AND

@Month =

CASE
WHEN @SupplierDate = 'OriginalPromDelDate' THEN
SUBSTRING((CONVERT(VARCHAR(50),po.OriginalPromDelDate)),5,2)
ELSE
SUBSTRING((CONVERT(VARCHAR(50),po.PickDate)),5,2)
END

AND

@Week =

CASE
WHEN @SupplierDate = 'OriginalPromDelDate' THEN
(((SUBSTRING((CONVERT(VARCHAR(50),po.OriginalPromDelDate)),7,2)-1)/7)+1)
ELSE
(((SUBSTRING((CONVERT(VARCHAR(50),po.PickDate)),7,2)-1)/7)+1)
END

skhalil

shareen
Starting Member

6 Posts

Posted - 2015-04-22 : 05:37:43

One more question guys
you can see in the above case statement for @Week I have added the code below. How do I add the OR @Week = '' to it (when running the report this gives the user the option to either put a specific week in or leave it blank and all the weeks for that month will appear)

(((SUBSTRING((CONVERT(VARCHAR(50),po.OriginalPromDelDate)),7,2)-1)/7)+1 = @Week OR @Week = '')

skhalil

Kristen
Test

22859 Posts

Posted - 2015-04-22 : 07:21:50

Generally if you have some @Parameters which the user provides and tehy have the option to leave them blank then you can do


SELECT ...
FROM dbo.MyTable
     JOIN dbo.MyOtherTable
         ON SomeColumn = SomeOtherColumn
WHERE     (@Param1 IS NULL OR Col1 = @Param1)
      AND (@Param2 IS NULL OR Col2 = @Param2) 
      ...

It would be OK to use @Param1 = '' instead of @Param1 IS NULL - however ... if @Param1 is Date, INT, etc. then '' doesn't make any viable sense, so better to pass NULL.

If you are using a stored procedure you could do:


CREATE PROCEDURE MySProc
    @strDateParam varchar(24)
AS
DECLARE @dtDateParam datetime

SELECT @dtDateParam + CONVERT(datetime, NullIf(RTrim(@strDateParam), ''))

and then use @dtDateParam within any WHERE clause etc. (i.e. don't use @strDateParam elsewhere in the SProc at all)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-22 : 07:31:15

[code]@year = CASE when @SupplierDate = '1' then CONVERT(VarChar(4), po.OriginalPromDelDate, 120)
ELSe CONVERT(VarChar(4), po.reqdate, 120)
END[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kristen
Test

22859 Posts

Posted - 2015-04-22 : 07:34:31

Is @YEAR even a String Variable? Might be numeric ...

Either way would it not be better to use DATEPART or some other DATE function, rather than String Manipulation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources