i trying run stored procedure in sql server , i'm getting 0 results. had running fine (attached ssrs) users requested multiple value input providername parameter , realized in on head. contacted our vendor provided knowledgebase article copied , pasted right in. see below...
alter procedure [dbo].[test] (@dstartdate datetime ,@denddate datetime ,@nprovidername varchar(max) ,@nallprovidername varchar(1) = 'n') begin declare @dstart datetime = convert(datetime,convert(date,@dstartdate)) ; declare @dend datetime = dateadd(ms,-3, dateadd(day,1,convert(datetime,convert(date,@denddate)))) declare @cprovidername varchar(max) = @nprovidername declare @tprovidername table (pcpid varchar(max) not null); if upper(@nallprovidername) = 'n' begin insert @tpcpname ( pcpid ) select ltrim(rtrim(item)) [dbo].[auto_split]('|',@nprovidername ) ; end; select ... ([testmnemonic] = 'glu' or [testmnemonic] = '%ha1c') , [status] != 'dis cli' , [textline] not null , [datetime] between @dstart , @dend , (upper(@nallprovidername) = 'y' or [pcpid] collate database_default in (select pcpid @tprovidername ) ) ; end so if comment out last 4 lines of code runs fine. it's in last bit (or @ top?) i'm hoping quick fix, , appreciated!
thanks!
i'm curious collate statement @ bottom. system default , need when comparing against temp table made?
without collate
or [pcpid] in (select pcpid @tprovidern 