>> 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))
WHERE in_something_id IS NOT NULL)
ON Target.somethint_id = X.somethint_id
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)