i have a table which is having size and pieces information
Size, pieces, sizeID, sizecombID
S, 1, 1, 1
M, 3, 2, 1
L, 3, 3, 1
XL, 1, 4, 1
I have two questions :-
1. If user enters the same information i.e. same sizes and same pieces then my stored procedure should not add these four records, instead it should return existing value of SizecombID(which stands for size combination ID)
2. If a totally new information is entered then that should be added in the table and sizecombID should be updated with highest value + 1. So for example if there is only one record in table as shown above then once a new combination is entered, my stored procedure should return 2..
can anybody help on this...
One possibility:
Code Snippet
SELECT SizeCombID
FROM MyTable
WHERE ( Col1 = NewValue1
AND Col2 = NewValue2
AND Col3 = NewValue3
)
IF ( @.@.ROWCOUNT = 0 )
INSERT INTO MyTable
( Col1,
Col2,
Col3
)
VALUES
( NewValue1,
NewValue2,
NewValue3
)
|||Actually the code snippet is only checking 1 record which is not the case.
1. I will be sending all the entered records to my stored procedure, what is the parameter to be used for sending multiple records
2. All records entered by user are to be checked. In exmaple which was provided, user has entered four sizes so it will be four records. All four records are to be checked.
3. Can you also tell how i can increment the sizecombid automatically to generate next value. Before Insert statement, stored procedure should get the last sizecombID value, add 1 in it and then add that value also to SizecombID.
Pls advs..
|||1.Passing Multiple Records:
Use XML string, you can get back those records in same structure you want.
There is no limitation since you can use either text or ntext as parameter datatype.
(Ex)
Code Snippet
Declare @.Param as Varchar(8000);
Select @.param = '<Root>
<Row Size="S" pieces="1" sizeID="1" sizecombID="1"/>
<Row Size="M" pieces="3" sizeID="2" sizecombID="1"/>
<Row Size="L" pieces="3" sizeID="3" sizecombID="1"/>
<Row Size="XL" pieces="1" sizeID="4" sizecombID="1"/>
</Root>'
Declare @.iDoc as Int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.param;
SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)
EXEC sp_xml_removedocument @.iDoc;
2. You can store the xml result in Table variable and you can validate those with your main tables.
(ex)
Code Snippet
Declare @.Input table
(
Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int
);
insert Into @.Input
SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)
3. This can be achived Max(id) + 1 but you have to take care the concurrent users issue.
|||appreicate your reply on this, i have few more questions :-
1. Is xml the only way to acheive this, can't I pass a set of rows directly to stored procedure without using xml.
2. once all the records are available inside stored proc, do i have to loop through all records using cursor or temp table or i can get the results directly through a sql statement
3. I also thought of Max(id) but bcos of concurrency issues, i was hesitant. Can you suggest any other way ? i just want the value unique not necessarily it should be in sequence (i don't want to use guid)
4. Do you think that i should change my table structure to make it as a xml field for sizes instead of multiple rows, just wanted to know some suggestion on that..
5. how do u get these code snippet blocks in your post, 
1. No SQL Server wont support parameter as array value - it should be csv or xml
2. You can get the results directly from the SQL, most of the cursor used batches we can rewrite using SQL queries.
3. Then you can have a lookup table to fetch the unique id (SizeCombo Master table with Identity value) insert the new sizecombo on master table then use the @.@.IDENTITY / SCOPE_IDENTITY on your other detailed table.
4. Don't store your data as XML it will be very difficult to maintaine & manupulations , store the data as rows
5. Its there in the forum text editor itself (next to the Smily Icon with green color {})
Hopefully last question..
Regarding answer to point 2, can u pls provide me the sql query which can search and return sizecombID from a table...
