October 4, 2010 at 7:21 am
Hi,
I am getting error while passing dynamic dates to storedprocedure with pivot.
This is my stored procdure.
create procedure Sample(@date1 datetime,@date2 datetime,@date3 datetime,@date4 datetime,@CampaignID int)
as
SELECT 'Forecasted' AS HeadCount,
[@date1] as date1
FROM
(SELECT *
FROM SAR_HeadCount) AS SourceTable
PIVOT
(
SUM(HeadCount)
FOR StartDate IN ([@date1])
) AS PivotTable;
StartDate is Datetime datatype only.
My error message
Msg 8114, Level 16, State 1, Procedure Sample, Line 4
Error converting data type nvarchar to datetime.
Msg 473, Level 16, State 1, Procedure Sample, Line 4
The incorrect value "@date1" is supplied in the PIVOT operator.
October 5, 2010 at 12:49 am
You can't use a variable name in a PIVOT list.
Post some sample data together with a description of what you are trying to achieve.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 5, 2010 at 2:15 am
Hi,
This is Table data
Empcount Startdate
196 8/1/2010
195 8/8/2010
187 8/15/2010
178 8/22/2010
I want result like this.Dates are dynamic which changs
HeadCount 8/1/2010 8/8/2010 8/15/2010 8/22/2010
Forecated HC 196 195 187 178
October 5, 2010 at 2:52 am
Please have a look at the CrossTab article referenced in my signature to see an alternative to PIVOT. Once you understand how it works, read the DynamicCrossTab article to be able to deal with flexible dates.
You could store the dates of your 4 input parameter in an temp table to query your target table only once.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply