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:

  1. convert the parameter to nvarchar rather than to datetime. You should use this conversion for even integer value as well.
  2. use extra single inverted comma before and after conversion of the datetime parameter to nvarchar
  3. 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)+”’
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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s