Post reply

Iterate through a table valued parameter

  • November 12, 2012 at 1:37 pm

    #270146

    Hi, I have a table valued parameter dbo.tabularVar of two columns (FirstName varchar(10), LastName varchar(10)).

    This is in an input parameter coming from a datatable in csharp, I will come with any number of records, HOW can I iterate through them?

    I would like to read the first FirstName, check if this FirstName is already registered in another table RegTable, if not, insert the entire record into the RegTable and do this same

    procedure for all the other records in the table valued parameter dbo.tabularVar. In programming, this is normally done with for loops.

    THanks!

  • November 12, 2012 at 2:23 pm

    #1558322

    What you're looking for is CURSOR or WHILE for loops in T-SQL.

    That said, they're the worst way to approach this problem.

    What you really want to do is look at the MERGE keyword. It'll allow you to update/insert depending on existance criteria in a single pass. You've already got the table variable, it'd be a shame not to use it well.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply