How to pass TVP to the proc within Openquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How to pass TVP to the proc within Openquery

Author

Topic

Ajdba
Starting Member

3 Posts

Posted - 2012-08-23 : 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

Selinacy
Starting Member

2 Posts

Posted - 2012-08-25 : 04:42:36

Much difficult

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources