April 21, 2008 at 12:54 pm
Hi,
I'm working on a procedure that insert records into a master table and a detail table. I have one record for the master table and one or more than one records for the detail table. When there is a multiple records for the detail table, I use a 'while' clause with incrementing a variable suffix stop the while clause. The logic would be like below;
I have a parameter indicating how many detail record.
@howManyRecord and four more parameters for the values; @value1, @value2, @value3, and @value4.
and declare a counter @tintCounter and set @tintCounter = 1
For the while clause, it test the condition
while @tintCounter <= @howManyRecord
begin
-- in here I'd like to increment a number
insert into tblValues
(intID, detailvalue)
values
(@intID, @value + @tintCounter -->??this is my question. how to manipulate the variable to increment the ending number
set @tintCounter = @tintCounter + 1
end
I'd appreciate your help. Thank you.
April 21, 2008 at 1:54 pm
I am making a couple assumptions here:
1. the @intId is the primary key from the Master Table
2. You will have a max of 4 child records to the one parent record.
If this is true, why do want to use a while loop. I would use 4 If blocks and check to see if the appropriate child value is null or not. If the appropriate child value is not null, then conduct the insert statement. If it is null, then skip to the next check.
IF @Value1 IS NOT NULL
INSERT INTO ChildTable VALUES (@intId, @Value1)
IF @Value2 IS NOT NULL
INSERT INTO ChildTable VALUES (@intId, @Value2)
etc...
IF @Value4 IS NOT NULL
INSERT INTO ChildTable VALUES (@intId, @Value4)
Dave Novak
April 22, 2008 at 10:22 am
Thank you for your response, Dave.
I'm attempting to manipulate the variable name to avoid hard coding. So far your second assumption is valid, but it can be changed down the road.
Is there any way to program 'variableName' + 'i (incrementing number)' in T-sql and I can get to the value in the variable, or is it even possible? I'm looking for a correct word or phrase for this; changing the stored procedure in run time?
I'd appreciate your help. Thank you.
Dong
April 22, 2008 at 10:32 am
Doug,
There is no way that I know of to do what you want to acheive, which is to dynamically change the variable name and use the same insert statement for the child table.
However there is another way to acheive this.
Instead of passing in each child value as its own input parameter, why not pass them all in one parameter as ',' delimited array.
Then use a while loop to parse the array and insert the value that way. This way you are not tied a set number of child values and have to change this in the future if this changes.
The only stipulation would be the length of the varchar inpur parameter for this array. You would need to make it big enough to handle all the combined child values in one single array.
You can use CHARINDEX to find the delimiter that divides each value and use SUBSTRING to get the pertinant info out of the array to insert into your child table.
Dave
April 22, 2008 at 11:20 am
It's a little disappointing but thank you. I'll try your solution. Thank you.
April 22, 2008 at 11:37 am
Good luck. Let me know if you need any help.
Dave Novak
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply