SQL Query: Pass DATETIME parameter to Dynamic SQL in Stored Procedure [Solved]
I could not pass datetime parameter to a stored procedure, where datatime parameter was being used in dynamic sql query, the lesson I have learned:
- convert the parameter to nvarchar rather than to datetime. You should use this conversion for even integer value as well.
- use extra single inverted comma before and after conversion of the datetime parameter to nvarchar
- The above two steps, our target is to achieve the date like this, APPL_ApplicationDate <='27-jan-2015'.
WHERE APPL_ApplicationDate <= '''+CONVERT(nvarchar, @RefDate)+'''
Solution:
WHERE APPL_ApplicationDate <= '''+CONVERT(nvarchar, @RefDate)+'''
Solution:
WHERE APPL_ApplicationDate <= '''+CONVERT(nvarchar, @RefDate)+'''
set @SqlCommand=N'
WITH ctePivotData AS
(
select
CMPN_Company_Name, APPL_Student_Status, STUD_Student_ID from ng.dbo.STUDstudent
INNER
JOIN ng.dbo.CMPN_Company_main
ON
STUDstudent.STUD_School_ISN=CMPN_Company_main.CMPN_ISN
INNER
JOIN ng.dbo.APPLications
ON
STUDstudent.STUD_Student_ID=APPLications.APPL_Student_ID
WHERE
APPL_ApplicationDate <= '''+CONVERT(nvarchar, @RefDate)+'''
)
SELECT CMPN_Company_Name,' +
@SpreadingList +
' INTO #PivotTable
FROM ctePivotData
PIVOT(count(STUD_Student_ID) FOR
APPL_Student_Status IN ('
+ @SpreadingList
+ ') ) AS P
order by CMPN_Company_Name;
select * from #PivotTable;';
--print @SqlCommand
execute sp_executesql @SqlCommand;
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home