How do you know what to set token_status to in Tab_Token_Details?
Is you current process doing an insert into Tab_Token_Master, then looping through and inserting the correct number of rows into Tab_Token_Details or are you reading Tab_Token_Master and then inserting into Tab_Token_Details?
For the first scenario you could do it in one stored procedure like this:
CREATE PROCEDURE create_tokens
(
@transid VARCHAR(10),
@token_starts_from SMALLINT,
@tokens_ends_to SMALLINT,
@token_value SMALLINT
)
AS
BEGIN TRANSACTION token_master
INSERT INTO Tab_Token_Master
(
transid,
token_starts_from,
token_ends_to,
token_value
)
VALUES
(
@transid,
@token_starts_from,
@token_ends_to,
@token_value
)
COMMIT TRANSACTION token_master
BEGIN TRANSACTION token_details
-- uses WayneS's code so you still need to lookup tally table
insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)
select
@Transid,
N,
@token_value ,
'A' -- or whatever your status field should be
from
dbo.Tally
where N between @token_starts_from AND @tokens_ends_to
COMMIT TRANSACTION token_details
RETURN
For the second scenario you just need to remove the insertion into Tab_Token_Master
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question