[RESOLVED] Variable for Where - 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

SQL Server 2005 Forums

Transact-SQL (2005)

[RESOLVED] Variable for Where

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2012-03-28 : 05:49:16

Hi Experts. I'm pretty sure you'll be able to see what I am trying to do in this code.

I am trying to set my where clause then use it in this query, however I get the message; An expression of non-boolean type specified in a context where a condition is expected, near '@where'. I am not understanding why I am getting the message, can someone correct this for me?


ALTER PROCEDURE SP_InvItemsMonthCount
@month int
AS
DECLARE @where varchar(50)
SET @where = CASE @month
	WHEN 1 THEN 'Invoice.[Inv Date] BETWEEN ''01/01/12'' AND ''31/01/12'
	WHEN 2 THEN 'Invoice.[Inv Date] BETWEEN ''01/02/12'' AND ''28/02/12'
	WHEN 3 THEN 'Invoice.[Inv Date] BETWEEN ''01/03/12'' AND ''31/03/12'
	WHEN 4 THEN 'Invoice.[Inv Date] BETWEEN ''01/04/12'' AND ''30/01/12'
	WHEN 5 THEN 'Invoice.[Inv Date] BETWEEN ''01/05/12'' AND ''31/01/12'
	WHEN 6 THEN 'Invoice.[Inv Date] BETWEEN ''01/06/12'' AND ''30/01/12'
	WHEN 7 THEN 'Invoice.[Inv Date] BETWEEN ''01/07/12'' AND ''31/01/12'
	WHEN 8 THEN 'Invoice.[Inv Date] BETWEEN ''01/08/12'' AND ''31/01/12'
	WHEN 9 THEN 'Invoice.[Inv Date] BETWEEN ''01/09/12'' AND ''30/01/12'
	WHEN 10 THEN 'Invoice.[Inv Date] BETWEEN ''01/10/12'' AND ''31/01/12'
	WHEN 11 THEN 'Invoice.[Inv Date] BETWEEN ''01/11/12'' AND ''30/01/12'
	WHEN 12 THEN 'Invoice.[Inv Date] BETWEEN ''01/12/12'' AND ''31/01/12'
END
SET DATEFORMAT 
	DMY
SELECT 
	SUM(QTY)
FROM 
	vv_InvItems INNER JOIN Invoice on vv_InvItems.[Invoice No] = Invoice.[Invoice No] 
WHERE
	@where

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-28 : 06:12:12

[code]ALTER PROCEDURE usp_InvItemsMonthCount
(
@Year SMALLINT,
@Month TINYINT
)
AS

SET NOCOUNT ON

DECLARE @FromDate DATETIME,
@ToDate DATETIME

IF @Year IS NULL OR @Year < 1900 OR @Year > 9999
SET @Year = DATEPART(YEAR, GETDATE())

IF @Month IS NULL OR @Month < 1 OR @Month > 12
SET @Month = DATEPART(MONTH, GETDATE())

SELECT @FromDate = DATEADD(MONTH, 12 * @Year + @Month - 22801, '19000101'),
@ToDate = DATEADD(MONTH, 12 * @Year + @Month - 22800, '19000101')

SELECT SUM(x.Qty)
FROM dbo.vv_InvItems AS x
INNER JOIN dbo.Invoice AS i ON i.[Invoice No] = x.[Invoice No]
WHERE i.[Inv Date] >= @FromDate
AND i.[Inv Date] < @ToDate[/code]

N 56°04'39.26"
E 12°55'05.63"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2012-03-28 : 06:31:44

Legend thanks so much!

"Impossible is Nothing"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources