August 21, 2007 at 9:28 am
I need to run the same select statement against multiple databases so I'm trying to create a sproc with a dbName parameter. I am getting the following error message. Could someone please tell me what i'm doing wrong. I think is something simple, but not sure what. Thanks
Msg 156, Level 15, State 1, Procedure srptGrossProfitSalesByLibrary, Line 30
Incorrect syntax near the keyword 'CASE'.
USE [PAR_WORK_IN_PROGRESS]
GO
/****** Object: StoredProcedure [dbo].[srptGrossProfitSalesByLibrary] Script Date: 08/21/2007 09:06:25 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dean Gross
-- Create date: 8/13/2007
-- Description: Calculate GPS
-- =============================================
ALTER
PROCEDURE [dbo].[srptGrossProfitSalesByLibrary]
-- Add the parameters for the stored procedure here
@dbName
nvarchar(10) = PARFLS --database to query
,@GBU nvarchar(10) = PAR --global business unit
,@BookedDate smalldatetime = '20061231' --get bookings after this date
,@debug bit = 0
AS
--DECLARE @sql nvarchar(max) -- sql statement
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--TODO add database parameter so that multiple dbs can be used easily
SELECT dbo.Programs.Program
, LEFT(loc.Work_Location, 1) + '0' AS DivNum
, CASE WHEN LEFT(loc.Work_Location, 1)+ '0' = '80' THEN 'Buildings' ELSE d.[Division Name] END AS Division
, JM.Job_Number AS Job
, Cust.Customer_Name AS Customer
, Emp.Employee_First_and_Last_Name AS BDRep
, SUM(BudDet.Budgeted_Cost_Amount) AS [Budgeted GPS]
, dbo.Calendar.FP AS FinPer
, dbo.Calendar.FY AS FinYear
, 'New' AS GPSType
CASE @dbName
WHEN 'PARFLS' THEN
FROM dbo.Divisions AS d
INNER JOIN dbo.Calendar
INNER JOIN parfls.dbo.BudgetDetail AS BudDet
INNER JOIN parfls.dbo.JobMaster AS JM ON BudDet.Job_Number = JM.Job_Number
INNER JOIN parfls.dbo.Customer AS Cust
ON JM.Customer_Number = Cust.Customer_Number
INNER JOIN parfls.dbo.WorkLocation AS Loc
ON JM.Office_Number = Loc.Work_Location
INNER JOIN parfls.dbo.CostTypeReporting AS CTR
ON BudDet.Cost_Type = CTR.CostType
ON dbo.Calendar.dt = JM.Job_Booked_Date
LEFT OUTER JOIN dbo.Programs
INNER JOIN dbo.CustomerPrograms
ON dbo.Programs.ProgramID = dbo.CustomerPrograms.ProgramID
ON JM.Customer_Number = dbo.CustomerPrograms.Customer_number
LEFT OUTER JOIN parfls.dbo.HrEmployee AS Emp
INNER JOIN parfls.dbo.BusinessDevelopment AS BD
ON Emp.Employee_Number = BD.Business_Development_Rep
ON JM.Job_Number = BD.Job_Number
ON d.[Division Number] = LEFT(Loc.Work_Location, 1) + '0'
WHEN 'CANFLS' THEN
FROM dbo.Divisions AS d
INNER JOIN dbo.Calendar
INNER JOIN canfls.dbo.BudgetDetail AS BudDet
INNER JOIN canfls.dbo.JobMaster AS JM ON BudDet.Job_Number = JM.Job_Number
INNER JOIN canfls.dbo.Customer AS Cust
ON JM.Customer_Number = Cust.Customer_Number
INNER JOIN canfls.dbo.WorkLocation AS Loc
ON JM.Office_Number = Loc.Work_Location
INNER JOIN canfls.dbo.CostTypeReporting AS CTR
ON BudDet.Cost_Type = CTR.CostType
ON dbo.Calendar.dt = JM.Job_Booked_Date
LEFT OUTER JOIN dbo.Programs
INNER JOIN dbo.CustomerPrograms
ON dbo.Programs.ProgramID = dbo.CustomerPrograms.ProgramID
ON JM.Customer_Number = dbo.CustomerPrograms.Customer_number
LEFT OUTER JOIN canfls.dbo.HrEmployee AS Emp
INNER JOIN canfls.dbo.BusinessDevelopment AS BD
ON Emp.Employee_Number = BD.Business_Development_Rep
ON JM.Job_Number = BD.Job_Number
ON d.[Division Number] = LEFT(Loc.Work_Location, 1) + 0
END
WHERE (JM.Job_Booked_Date > CONVERT(DATETIME, '2007-01-01 00:00:00', 102))
AND (CTR.RptGroup = 'gp')
AND (JM.Office_Number BETWEEN '200' AND '899')
AND (d.GBU = @GBU)
GROUP BY JM.Office_Number
, Cust.Customer_Name
, JM.Job_Number
, Emp.Employee_First_and_Last_Name
, Loc.Work_Location, d.[Division Name]
, dbo.Calendar.FP
, dbo.Calendar.FY
, d.[Division Number]
, dbo.Programs.Program
HAVING (SUM(BudDet.Budgeted_Cost_Amount) <> 0)
END
August 21, 2007 at 9:52 am
missing a comma up there before one of the CASE statements.
CASE when ... end
is like a column. Needs commas to separate out.
You have
select x
, y
case when ...
, z
Need the command between Y and CASE
August 22, 2007 at 11:11 pm
you can't 'CASE ... WHEN ...' the from, where clauses in a SQL. try using some others... perhaps like following :
select <col1>, <col2>....
from < all where, group by ... staff from 'PARFLS' with a extra cond. @dbName= 'PARFLS' >
Union All
select <col1>, <col2>....
from < all where, group by ... staff from 'CANFLS' with a extra cond. @dbName= 'CANFLS'>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply