Need help parsing parameter into crosstable

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help parsing parameter into crosstable

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-02 : 10:03:44

Dear ALl,

I trying to pass the variable into below exec at section bold. But i keep getting error on +. What shall i need to do on below code? Pls advise.

TQ.

set @fdate=@ffdate
set @tdate=@ttdate

exec CrossTab 'Orders',
'SELECT t.ASSETID,Dates,Datt=cast(cast(Month(Dates) as varchar)+cast(Year(Dates) as varchar) as varchar),DATEPUR,Orders,DEP_AMOUNT,DEP_YEAR,val=(DEP_AMOUNT/DEP_YEARCNT) FROM pivottbldet tb
INNER JOIN pivottbl t ON (t.ASSETID=tb.ASSETID and Year(Dates)=DEP_YEAR) where Dates between ''+@fdate+'' and ''+@tdate+'' ',
'Datt',
'max(Val)[]',
'ASSETID,DatePur'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-02 : 10:42:07

set @fdate=@ffdate
set @tdate=@ttdate

exec CrossTab 'Orders',
'SELECT t.ASSETID,Dates,Datt=cast(cast(Month(Dates) as varchar)+cast(Year(Dates) as varchar) as varchar),DATEPUR,Orders,DEP_AMOUNT,DEP_YEAR,val=(DEP_AMOUNT/DEP_YEARCNT) FROM pivottbldet tb
INNER JOIN pivottbl t ON (t.ASSETID=tb.ASSETID and Year(Dates)=DEP_YEAR) where Dates between '''+@fdate+''' and '''+@tdate+'' ',
'Datt',
'max(Val)[]',
'ASSETID,DatePur'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-02 : 10:45:25

1. form the query in a variable before passing into stored procedure
2. convert the 2 date variable value into string using YYYYMMDD format


declare @sql nvarchar(max)

select @sql = 'SELECT t.ASSETID . . . .
where Dates between ''' + convert(varchar(10), @fdate, 112) + ''' and ''' + convert(varchar(10), @tdate, 112) + ''''

KH
[spoiler]Time is always against us[/spoiler]

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-03 : 04:19:37

Thank you robvolk & KH Tan.

KH Tan answer is working fine.

Blessing.

Regards,
Mic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources