/****** Object:  UserDefinedFunction [dbo].[cfn_split]    Script Date: 07/18/2012 09:48:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[cfn_split](@splitstr VARCHAR(max), @splitchar CHAR(1))
		RETURNS @split_elements TABLE (eleno int identity(1,1), 
						element varchar(max))
AS
-- Custom function to split a string based on the delimiter
-- Accepts a string to be splitted & a delimitter
-- Returns a table with all split elements sorted in chronological order
BEGIN
	DECLARE @pos INT

	SELECT @pos = CHARINDEX ( @splitchar, @splitstr, 0 )
	WHILE @pos <> 0
	BEGIN
		INSERT @split_elements(element) SELECT SUBSTRING(@splitstr, 1, @pos - 1)
		SELECT @splitstr = SUBSTRING(@splitstr, @pos + 1, 8000)
		SELECT @pos = CHARINDEX ( @splitchar, @splitstr, 0 )
	END
	INSERT @split_elements(element) VALUES(@splitstr)

	RETURN
END

quote:

---

Originally posted by LarryC74

For whatever reason it stops at 1786 values. There are a total of 1918 values that need to be entered.

Everyday life brings me back to reality

---

quote:

---

Originally posted by LarryC74

robvolk: they have filtered that out for some reason. some reports have them others don't.


visakh16: No...no special characters.

I may just end of up re-writing the entire thing. They use the format From tablea, tableb, tablec WHERE tablea.id = tableb.id AND tableb.id=tablec.id I like the 'JOINs' personally.

I've NEVER seen so many temp tables used in stored procedure before in my life....it's craziness.

Everyday life brings me back to reality

---

quote:

---

Originally posted by robvolk

Typically if I have to pass a long list of values I'll used fixed-width and binary. This works well for integers as they store in 4 bytes each and I don't have to parse a string. I've never used this for Reporting Services so I don't know if it can be done that way.

Only other option I can think of is to change the report to use 2 variables and split the list evenly, then modify the procedure to accept both. Again I'm not sure if this will work in Reporting Services.

---

Create PROCEDURE [Rep].[ContractedServicesbyProviderandSite] 

--	 Add the parameters for the stored procedure here

(
@catchment   NVARCHAR(MAX),
@ProviderID  NVARCHAR(MAX),
@Plan		 NVARCHAR(MAX),
@str_dt		 datetime ,
@end_dt		 datetime ,
@service  NVARCHAR(MAX),
@status   NVARCHAR(MAX),
@cspecific int
)	 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;



DECLARE 
@catchment_rep NVARCHAR(MAX)
set @catchment_rep =@catchment 

DECLARE 
@ProviderID_rep NVARCHAR(MAX)
set @ProviderID_rep =@ProviderID  

DECLARE 
@Plan_rep NVARCHAR(MAX)			
set @Plan_rep=@Plan

DECLARE 
@str_dt_rep datetime			
set @str_dt_rep=@str_dt--'1/1/2012' 

DECLARE 
@end_dt_rep datetime			
set @end_dt_rep =@end_dt--'6/30/2012' 

DECLARE 
@srvc_id NVARCHAR(MAX)
set @srvc_id=@service 

DECLARE 
@Avail_status NVARCHAR(MAX)
set @avail_status=@status

DECLARE 
@clientspecific Int
set @clientspecific=@cspecific

    -- Insert statements for procedure here


SELECT 
distinct 
X.Provider 
,X.ProviderID
,X.available_id
,X.available_desc
,X.SiteID
,X.Site 
,X.SiteAddress 
,x.City 
,x.State 
,x.Zip 
,X.[Plan] 
,X.ContractedService 
,x.clientspecific
,x.ServiceCode
,x.Srvc_id
,x.EffectiveDate 
,x.EndDate 
FROM (

Select 
From table1 t1, table2 t2 , table3 t3
Where
t1.id = t2.id
and t2.id = t3.id


)X 


WHERE 
(convert (nvarchar,key_Id) in (SELECT element FROM dbo.cfn_split(@catchment_rep,',')))
AND 
(convert (nvarchar,ProviderID) in (SELECT element FROM dbo.cfn_split(@ProviderID_rep,',')))
AND 
(convert (nvarchar,ins_id) in (SELECT element FROM dbo.cfn_split(@Plan_rep,',')))
AND 
(case when @srvc_id <> -1 and (convert (nvarchar,srvc_id) in (SELECT element FROM dbo.cfn_split(@srvc_id,','))) then 1
     when @srvc_id = -1 then 1  
  end)=1
AND clientspecific = @clientspecific