February 14, 2011 at 2:37 pm
Hello. I have the following code;
CREATE PROCEDURE dbo.sp_FinSummary (
@EntityID int,
@FranchiseID int,
@ProjTypeID int,
@GroupBy varchar(50),
@DetailLevel varchar (255)
)
AS
BEGIN
SET NOCOUNT ON;
IF @GroupBy = 'Franchise'
BEGIN
CREATE TABLE dbo.tblFinSummary1 (
FranchiseGroup as varchar(255),
Franchise as varchar(255),
DetailLevel as varchar(255),
Actual as numeric(38, 11),
[Plan] as numeric(38, 11),
DataMonth as datetime
)
INSERT INTO dbo.tblFinSummary1 ( FranchiseGroup,
Franchise, DetailLevel, Actual, [plan], DataMonth )
SELECT fg.FranchiseGroup
, f.Franchise
, CASE @DetailLevel
WHEN 'Project' Then p.ProjectCode
WHEN 'Program' THEN prg.Program
WHEN 'Project Type' THEN pt.ProjectType
END AS DetailLevel
, SUM(
CASE ps.PlanName
WHEN 'Actuals' THEN ps.ProjectSpend
ELSE 0
END) AS Actual
,SUM(
CASE ps.PlanName
WHEN 'Actuals' THEN 0
ELSE (
CASE
WHEN ps.PlanName like '%plan%' THEN ps.ProjectSpend
END)
END) AS [Plan]
,ps.DataMonth
FROM dbo.tblProjectSpend ps
INNER JOIN dbo.tblProjects p
ON p.ProjectCode = ps.ProjectCode
INNER JOIN dbo.tblProject2Franchises p2f
ON p.ProjectID = p2f.ProjectID
INNER JOIN dbo.tblFranchises f
ON p2f.FranchiseID = f.FranchiseID
INNER JOIN dbo.tblPrograms prg
ON p.ProgramID = prg.ProgramID
INNER JOIN dbo.tblProjectTypes pt
ON pt.ProjectTypeID = p.ProjectTypeID
INNER JOIN dbo.tblFranchiseGroups fg
ON fg.FranchiseGroupID = f.FranchiseGroupID
WHERE
fg.FranchiseGroupID = @EntityID
AND f.FranchiseID = @FranchiseID
AND YEAR(ps.DataMonth) = 2010
GROUP BY fg.FranchiseGroup
, f.Franchise
, CASE @DetailLevel
WHEN 'Project' Then p.ProjectCode
WHEN 'Program' THEN prg.Program
WHEN 'Project Type' THEN pt.ProjectType
END
,ps.DataMonth
SELECT * FROM dbo.tblFinSummary1
END
ELSE IF @GroupBy = 'Project'
BEGIN
CREATE TABLE dbo.tblFinSummary1 (
Project as varchar(255),
DetailLevel as varchar(255),
Actual as numeric(38, 11),
[Plan] as numeric(38, 11),
DataMonth as datetime
)
END
END
--DROP TABLE dbo.tblFinSummary1
GO
For some reason I keep getting two instances of the following err:
'varchar' is not a recognized built-in function name.
I am lost. I can't figure out where I went wrong. Any help would be appreciated.
February 14, 2011 at 2:46 pm
Just remove the "as" between your column names and the data type.
Example:
Instead of
FranchiseGroup as varchar(255),
use
FranchiseGroup varchar(255),
February 14, 2011 at 3:08 pm
Worked. Thanks for the quick response. I knew it was something simple.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply