Here is what I am thinking of doing; how about breaking the queries into three parts as below-

SELECT @listStr1 = SUBSTRING((COALESCE(@listStr+',' ,'')),1,6000) + Incident FROM @IncidentGIS

SELECT @listStr2 =...

aghhhh..that makes sense.....

can you suggest any alternate in this scenario...

Both @listStr, @Years are nvarchar(max)

I am not understanding if all my string variables are nvarchar(max) , then what could be reason for getting the error...really confused..

Basically, this is the sql string I am trying to pass where I am getting the error;

@sql_str_gis nvarchar(max)

@lstStr nvarchar(max)

SELECT @sql_str_gis = N'SELECT IncidentIdNbr, incident_year, incident_date, incident_time ,county_desc, routetype_desc, b.route_number,

...

Thanks Lowell.

But this @Years(2001,2002,2005,2008) values are passed by the users thru stored proc and years can be different every time; in this case, do you think I can pass...

Can I use REPLACE function to get rid of quotation mark around the year

One more thing I need to add that in the #year table, the years get inserted like this:

'2000', '2001','2002'

All I need to do is to take out the tick...

I have already a table where the years gets inserted and I would like to parse it to show like this: (2000,2001)

CREATE TABLE #Years (Year varchar(6))

...

yes you are right , I am getting the following error.

Msg 7312, Level 16, State 1, Procedure GEARSAnalysisReportsGIS2, Line 589

Invalid use of schema or catalog for OLE DB provider...

Thanks Matt for your suggestions. But when I tried to use without the openquery, it was giving taking very long time and timing out; I was using as below:

...

DECLARE @IncidentTVP TABLE

(IncidentID INT)

SET @Statement = 'SELECT distinct b.GIS_INCIDENT

FROM...

One more thing I need to add that I am trying to use these TVP as a parameter in my where clause if this is possible at all.

Thanks

How do I create the table valued parameter in this case; I have one table called incident and it has one column ( incidentID int);somehow in my stored proc...

Thanks a lot for all your help