Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to loop each id in sql server 2008 Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 3:02 AM
Points: 25, Visits: 72
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.
Post #1337714
Posted Tuesday, July 31, 2012 1:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 20,739, Visits: 32,526
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/



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)
Post #1337716
Posted Tuesday, July 31, 2012 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1337897
Posted Tuesday, July 31, 2012 10:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:27 PM
Points: 1,945, Visits: 3,068
>> 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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1338017
Posted Tuesday, July 31, 2012 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1338035
Posted Tuesday, July 31, 2012 12:02 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 17,825, Visits: 15,758
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
Post #1338080
Posted Tuesday, July 31, 2012 2:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 20,739, Visits: 32,526
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.



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)
Post #1338161
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse