How can I pass table row as a in parameter to stored proc

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

  • 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

  • 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

  • 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


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • 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

  • 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?

  • Create table type.

    http://msdn.microsoft.com/en-us/library/ms175007.aspx

    and use that as output parameter

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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?

  • 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