Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2012-09-07 : 18:43:23
|
have a sp1 calling a sp2. getting this error msgMsg 241, Level 16, State 1, Procedure SetKPIReportFormat, Line 27Conversion failed when converting date and/or time from character string.How can i pass datetime to dynamic sql?alter PROC Report.SetKPIReportFormat @Procedure VARCHAR(100),@StartDate AS DATETIME, @EndDate AS DATETIMEASDECLARE @AdAttributes XMLDECLARE @SQL VARCHAR(2000)SET @SQL = 'EXEC Report.' + @Procedure + ' ''' + CAST(@StartDate AS DATETIME) + ''', ''' + CAST(@EndDate AS DATETIME) + ''''DECLARE @ReportResult TABLE (KPICount XML)INSERT @ReportResultEXEC (@SQL)SELECT @AdAttributes = KPICount FROM @ReportResultSELECT Attr.col.value('@Name', 'VARCHAR(30)') AS 'Name', Attr.col.value('@Value', 'INT') AS 'Value'FROM @AdAttributes.nodes('//KPICounts/KPICount') Attr(col)GO |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2012-09-07 : 19:48:10
|
THANKS tkizer, it works |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|