SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to loop each id in sql server 2008


How to loop each id in sql server 2008

Author
Message
venkidesaik
venkidesaik
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 73
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39133 Visits: 38518
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/

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25960 Visits: 17519
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25960 Visits: 17519
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32116 Visits: 18551
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

Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39133 Visits: 38518
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search