August 22, 2012 at 3:53 pm
Hi ,
I have incidents number of 7 character long and which are storing in table as below
1880992
2427572
1680575
2267755
2504343
2476359
2003648
1941562
2038118
1847247
304757
111610
495817
1482745
1822203
3396649
1475775
Is it possible to send these numbers as in parameters to stored proc; this number of columns can be really large. Is there any other way I can handle this.
Appreciate your help.
Thanks
Aj
August 22, 2012 at 4:05 pm
Hopefully, you mean "rows" and not "columns".
Why not just have your stored procedure read the values directly from the table instead of you trying to pass them to it?
--Jeff Moden
August 22, 2012 at 5:51 pm
You may also consider using table-valued parameters.
Pinal Dave has a good intro to how they are used:
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 22, 2012 at 9:05 pm
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 I need to pass this incidentID as a TVP
Can somebody give me an example.
Thanks
Aj
August 22, 2012 at 9:42 pm
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
August 22, 2012 at 9:46 pm
Ajdba (8/22/2012)
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
If you are using TVP in your where clause then use it as a sub-query to get all the values
SELECT col1,col2,....
FROM table
WHERE col1 IN (SELECT value FROM @tvp)
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
August 23, 2012 at 8:04 am
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
August 23, 2012 at 10:39 am
Based on the code you've posted - why would any of that be in a dynamic SQL statement. There's nothing requiring a dynamic structure in there, if you were to set up the @years as a TVP as well as the ID's. Building out the string just complicates matters unnecessarily and will make it harder to maintain.
You could build a stored procedure that executes the select statement you have been building dynamically, and use that in the insert statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 23, 2012 at 10:50 am
Create table type.
http://msdn.microsoft.com/en-us/library/ms175007.aspx
and use that as output parameter
August 23, 2012 at 12:35 pm
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:
INSERT INTO #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)
select a.IncidentIdNbr, a.incident_year, a.incident_date, a.incident_time ,a.county_desc, a.routetype_desc, a.intersection_rttype_desc,
a.intersection_route, a.InjuriesNbr_Total, a.FatalitiesNbr_Total, a.totalNbrOfVehicle, a.firstharmfulevent_desc, a.mannerofcollision_desc,
a.locationatimpact_desc, a.lightcondition_desc, a.surfacecondition_desc, a.RampSection, a.directionnmovement1_desc, a.directionnmovement2_desc,
a.directionnmovement3_desc, a.vehiclemaneuver1_desc, a.vehiclemaneuver2_desc,
a.vehiclemaneuver3_desc, a.PDF_LINK, null as ORIGINALROUTE, a.RCLINK
FROM EDWGEARS..GT_GEARS.VW_GEARS_REPORT_DATA a
inner join @IncidentTVP b on a.IncidentIdNbr = b.IncidentID
I am not sure why it is not liking EDWGEARS..GT_GEARS.VW_GEARS_REPORT_DATA conventions;
Thanks
August 23, 2012 at 12:42 pm
Ajdba (8/23/2012)
I am not sure why it is not liking EDWGEARS..GT_GEARS.VW_GEARS_REPORT_DATA conventions;
LinkedServerName.DatabaseName.SchemaName.TableName.
i doubt very much you have a schema name named GT_GEARS
it's much more likely that it should be EDWGEARS.GT_GEARS..VW_GEARS_REPORT_DATA
or explicitly name the schema: EDWGEARS.GT_GEARS.dbo.VW_GEARS_REPORT_DATA
Lowell
August 23, 2012 at 12:49 pm
If the values are already in a table, why are you passing them to a stored proc. Why not just reference the table in the proc? This would save you a lot of trouble...
Jared
CE - Microsoft
August 23, 2012 at 12:58 pm
Lowell (8/23/2012)
Ajdba (8/23/2012)
I am not sure why it is not liking EDWGEARS..GT_GEARS.VW_GEARS_REPORT_DATA conventions;LinkedServerName.DatabaseName.SchemaName.TableName.
i doubt very much you have a schema name named GT_GEARS
it's much more likely that it should be EDWGEARS.GT_GEARS..VW_GEARS_REPORT_DATA
or explicitly name the schema: EDWGEARS.GT_GEARS.dbo.VW_GEARS_REPORT_DATA
+1
As a note - most linked servers I've had to deal with really really don't like it when you don't explicitly answer ALL parts of the 4-part notation.
Also - (OP) I think you're runing into issues because you're defining the table parameter on your local server, then querying "across the wire" using the local table parameter: this will confuse the engine (usually making it pull the entire table over the wire and forcing it to filter on the local server, often without leveraging any indexing on the remote tables).
If you were to define the stored procedure on the remote server, and pass the table parameter to it, you should get a LOT better performance.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 23, 2012 at 1:08 pm
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 "OraOLEDB.Oracle" for linked server "EDWGEARS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply