How to loop each id in sql server 2008

  • Hi All,

    From Front End i select some checkboxes it comes to database with comma separation like '1,2,3'.

    Now i want to check those '1,2,3' those id's are exixts/not in some other table.

    If it exists i want to update (OR) if not exists i want to Insert.

    I already done like this if only one id comes from front end there was no problem i can do.

    But if it get like this '1,2,3' how i want check that means how to loop this.

    there is no For loop concept in SQL SERVER.

    Here Id's are String.

    Regards,

    VenkiDesai.

  • You need to use a delimited splitter like the one developed by Jeff Moden and the ssc community. Start by reading this article and the discussion that goes with it.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

  • The splitter that Lynn mentions is awesome at splitting strings. However it sounds like you are looping in the front end to get the selected values? Assuming that you are using 2008 you could also look at passing in a table valued parameter instead. In the front end instead of building a comma separated list, you add rows to a dataset and pass that dataset to sql as a parameter. SQL can now treat that like a table.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • CELKO (7/31/2012)


    >> From Front End I select some checkboxes it comes to database with comma separation like '1,2,3'. <<

    Check boxes? There are no such things in SQL You missed the concept of a tiered architecture that has a presentation layer. In the front end in a properly designed application, you would validate the input and make sure that the data base is getting only First Normal Form (1NF). In short, you should never pass that list. to the DB layer.

    >> Now I want to check those '1,2,3' those id's are exists /not in some other table. If it exists I want to update (OR) if not exists I want to Insert. <<

    Your narrative described is a MERGE statement.

    >> there is no For loop concept in SQL SERVER.<<

    Correct! That is why we build a set and not kludge a parser. This is a job for the long parameter list which is safer and faster than lapsing back into procedural code.

    The main advantages of the long parameter lists are:

    1) The code is highly portable to any Standard SQL. One product bigots need to get over the idea that they are never going to see more than one SQL engine in their careers.

    2) The code is pure native SQL and not an external procedural language. The people that maintain it can be SQL programmers. There will be no need to send your C# programmer to F# classes next year to keep up.

    3) The optimizer will treat them like any other parameter. They can be sniffed. You can use a RECOMPILE option and get the best performance possible each time the procedure runs.

    4) The compiler will treat them like any other parameter. You get the expected error messages and conversion. Frankly, I have never seen anyone who used one of the other techniques write RAISERROR() calls to return the same error messages as the compiler.

    5) It is easy to generate the code with a text editor. Think about how hard it is to write in a CLR language you don't know or to write a loop in T-SQL. I just cut & paste a skeleton with 250 parameters then cut off what I need. I am going to show a list of five parameters in my skeleton code to save space.

    Since you did not give us DDL, code or even tables names, here is a skeleton procedure.

    CREATE PROCEDURE Vague_Something

    (@p1 CHAR(10) = NULL,

    @p2 CHAR(10) = NULL,

    @p3 CHAR(10) = NULL,

    @p4 CHAR(10) = NULL,

    @p5 CHAR(10) = NULL)

    MERGE INTO Target

    USING (SELECT in_something_id

    FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5))

    AS X(in_something_id)

    WHERE in_something_id IS NOT NULL)

    ON Target.somethint_id = X.somethint_id

    WHEN MATCHED

    THEN UPDATE ..

    WHEN NOT MATCHED

    THEN INSERT ..;

    I am amazed that old hands still do not think in sets and fake mag tape with sequential processing.

    Why in the world would you suggest using a long parameter list instead of table valued parameters? This whole concept simply goes against normal form. Now you have conceptually converted rows into columns. Let's say you build your stored proc with a list of 50 and suddenly you need 51. You have to go back and edit both the calling application AND the stored proc. Talk about being stuck in the past. A table valued parameter allows you the flexibility to pass any number of rows with zero change to any code.

    I certainly understand your point about a person not being stuck with a single RDBMS but calling these people bigots is a bit harsh. Yes it is useful for a person to know and understand multiple RDBMS in their career but any given application is not going to be switching back and forth on a whim. It goes back to the old rhetoric of why would you not use the tools at hand given the RDBMS of the system?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (7/31/2012)


    The splitter that Lynn mentions is awesome at splitting strings. However it sounds like you are looping in the front end to get the selected values? Assuming that you are using 2008 you could also look at passing in a table valued parameter instead. In the front end instead of building a comma separated list, you add rows to a dataset and pass that dataset to sql as a parameter. SQL can now treat that like a table.

    +10

    Unless the front end is java.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CELKO (7/31/2012)


    ...

    The main advantages of the long parameter lists are:

    1) The code is highly portable to any Standard SQL. One product bigots need to get over the idea that they are never going to see more than one SQL engine in their careers.

    ...

    Interesting use of the word bigot. I know what it means, but I decided to look it up any ways:

    1. intolerant person: somebody with strong opinions, especially on politics, religion, or ethnicity, who refuses to accept different views

    Seems like if the shoe fits.

Viewing 6 posts - 1 through 5 (of 5 total)

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