|
Ajdba
Starting Member
USA
3 Posts |
 Posted - 08/23/2012 : 10:53:52
|
DECLARE @IncidentTVP TABLE
(IncidentID INT)
SET @Statement = 'SELECT distinct b.GIS_INCIDENT
FROM dbo.GEARSRouteIncidents b ,
dbo.GEARSRouteList c
WHERE c.REQUESTOR = @Requestor
and b.ROUTELIST_RECORD_ID = c.RECORD_ID
and c.REPORT_SOURCE = @ReportSource'
INSERT @IncidentTVP EXECUTE SP_EXECUTESQL @STATEMENT
My challenge is how to incorporate/pass this TVP as a parameter within openquery as below; any suggestions would be appreciated:
SELECT @sql_str_gears = N'SELECT IncidentIdNbr , incident_year, incident_date, incident_time ,county_desc, routetype_desc,intersection_rttype_desc,
intersection_route, InjuriesNbr_Total, FatalitiesNbr_Total, totalNbrOfVehicle, firstharmfulevent_desc, mannerofcollision_desc,
locationatimpact_desc, lightcondition_desc,surfacecondition_desc, RampSection, directionnmovement1_desc, directionnmovement2_desc,
directionnmovement3_desc,vehiclemaneuver1_desc, vehiclemaneuver2_desc,
vehiclemaneuver3_desc, PDF_LINK, null as ORIGINALROUTE, RCLINK
FROM VW_GEARS_REPORT_DATA
WHERE IncidentIdNbr in (select * from @IncidentTVP)
and incident_year in ' + @Years + '
and RCLINK IS NOT NULL
and LatDecimal IS NOT NULL
and LongDecimal IS NOT NULL'
SELECT @sql_str_gears = N' SELECT * from OPENQUERY(EDWGEARS, ''' + REPLACE(@sql_str_gears, '''', '''''') + ''')'
SELECT @sql_str_gears
INSERT #gearsdata (ACC_ID,
ACC_YEAR,
ACC_DATE,
ACC_ATIME,
COUNTY_DESC,
ROUTE_DESC,
INTRSCT_RT_TYPE,
INTRSCT_RT,
ACC_TNI,
ACC_TNF,
ACC_TNV,
HARMFULEVENT_DESC,
COLLISION_DESC,
LOCIMPACT_DESC,
LIGHT_DESC,
SURFACE_DESC,
RAMPSECTION_ID,
D1,
D2,
D3,
VM1,
VM2,
VM3,
PDF_LINK,
ORIGINALROUTE,
RCLINK)
EXEC sp_ExecuteSQL @sql_str_gears
END
select * from #gearsdata
Thanks |
|
How to pass TVP to the proc within Openquery
