reporting services - SSRS not returning any data with Table-valued parameters -
i'm struggling understanding why adding 1 or more nullable parameters before or after table-valued parameters return empty recordset.
i followed example here: http://geekswithblogs.net/gruffcode/archive/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services.aspx
then added parameters mimic environment , cannot more records unless replace @accounttype=null @accounttype=4
here code in dataset:
exec( ' declare @customeridlist report.integerlisttabletype ' + @customeridinserts + ' exec rpt_customertransactionsummary @startdate=''' + @startdate + ''', @enddate=''' + @enddate + ''', @customerids = @customeridlist')
and sql trace (result: empty recordset):
exec sp_executesql n'exec( '' declare @customeridlist report.integerlisttabletype '' + @customeridinserts + '' exec rpt_customertransactionsummary @startdate='''''' + @startdate + '''''', @enddate='''''' + @enddate + '''''', @accounttype='''''' + @accounttype + '''''', @customerids = @customeridlist'')', n'@customeridinserts nvarchar(40), @startdate datetime, @enddate datetime, @accounttype int', @customeridinserts=n'insert @customeridlist values (304813)', @startdate='2013-01-01 00:00:00', @enddate='2014-12-31 00:00:00', @accounttype=null
if replace null 4 , run query in sql management studio expected results:
exec sp_executesql n'exec( '' declare @customeridlist report.integerlisttabletype '' + @customeridinserts + '' exec rpt_customertransactionsummary @startdate='''''' + @startdate + '''''', @enddate='''''' + @enddate + '''''', @accounttype='''''' + @accounttype + '''''', @customerids = @customeridlist'')', n'@customeridinserts nvarchar(40), @startdate datetime, @enddate datetime, @accounttype int', @customeridinserts=n'insert @customeridlist values (304813)', @startdate='2013-01-01 00:00:00', @enddate='2014-12-31 00:00:00', @accounttype=4
can please set me right direction?
thanks
rick
this stored proc:
alter procedure dbo.rpt_customertransactionsummary ( @startdate datetime, @enddate datetime, @customerids report.integerlisttabletype readonly, @accounttype int ) begin set nocount on; select t.playerid, t.inserteddatetime, t.amount transactions t inner join @customerids c on t.playerid = c.value inserteddatetime between @startdate , @enddate , (@accounttype null or t.accounttype=@accounttype) end
Comments
Post a Comment