Gray5452
asked on
Case statement in a Stored Proc syntax
Can someone please point out the errors in my syntax here..Basically just going to call this proc from SSRS in order to bring back one of four datasets and the 'sitename' if that possible...
Thanks in Advance,Gray
-- ========================== ========== ========== ==
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ========================== ========== ========== ==
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================== ========== =========
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- ========================== ========== =========
CREATE PROCEDURE [dbo].[Digital_Sitename]
@start datetime,
@end datetime,
@varSitename nvarchar(40)
AS
BEGIN
SET NOCOUNT ON;
SELECT Case
WHEN @varSitename = 'Google' THEN
SELECT sitename,campaign,SUM(cost )as 'sitecost',SUM(calls)as 'sitecalls'
FROM [KoolSmiles].[dbo].[v_Digi talMedia]
where (REportDate between @start and @end)and sitename = 'Google'
group by sitename,campaign
When @varSitename='Bing' THEN
SELECT sitename,campaign,SUM(cost )as 'sitecost',SUM(calls)as 'sitecalls'
FROM [KoolSmiles].[dbo].[v_Digi talMedia]
where (REportDate between @start and @end)and sitename = 'Bing'
group by sitename,campaign
WHEN @varSitename='Marchex' THEN
SELECT sitename,campaign,(SUM(cal ls)*15) as 'sitecost', SUM(calls)as 'sitecalls'
FROM [KoolSmiles].[dbo].[v_Digi talMedia]
where (REportDate between @start and @end)and sitename ='Marchex'
group by sitename,campaign
WHEN @varSitename='Yellow Pages' THEN
Select sitename,campaign, (DATEDIFF(D,@start,@end)+1 )*(8774/30 ) as 'spend',sum(calls)as 'calls'
FROM [KoolSmiles].[dbo].[v_Digi talMedia]
where (REportDate between @start and @end) and sitename ='Yellow Pages'
group by sitename,campaign
END Case
GO
END
Thanks in Advance,Gray
-- ==========================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ==========================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- ==========================
CREATE PROCEDURE [dbo].[Digital_Sitename]
@start datetime,
@end datetime,
@varSitename nvarchar(40)
AS
BEGIN
SET NOCOUNT ON;
SELECT Case
WHEN @varSitename = 'Google' THEN
SELECT sitename,campaign,SUM(cost
FROM [KoolSmiles].[dbo].[v_Digi
where (REportDate between @start and @end)and sitename = 'Google'
group by sitename,campaign
When @varSitename='Bing' THEN
SELECT sitename,campaign,SUM(cost
FROM [KoolSmiles].[dbo].[v_Digi
where (REportDate between @start and @end)and sitename = 'Bing'
group by sitename,campaign
WHEN @varSitename='Marchex' THEN
SELECT sitename,campaign,(SUM(cal
FROM [KoolSmiles].[dbo].[v_Digi
where (REportDate between @start and @end)and sitename ='Marchex'
group by sitename,campaign
WHEN @varSitename='Yellow Pages' THEN
Select sitename,campaign, (DATEDIFF(D,@start,@end)+1
FROM [KoolSmiles].[dbo].[v_Digi
where (REportDate between @start and @end) and sitename ='Yellow Pages'
group by sitename,campaign
END Case
GO
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER