Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unhappy Unanswered: Insert multiple rows using a stored procedure

    I'm writing a Intranet web application to allow users to add presentation files to a web site for others to download. The presentations are to be grouped by categories, however I want them to be able to create additional categories if needed. I have created two tables.

    Table 1 - PresentationCategories
    Table 1 Fields - ID, Category

    Table 2 - PresentationFiles
    Table 2 Fields - ID, Name, Description, Filename, Filesize, CategoryID

    On my web page I want to call a stored procedure to insert records into the PresentationFiles table. I have check boxes on the web form for all the possible categories that exist. A user can check each category that this presentation applies too.

    In my stored procedure, how do I accomplish inserting a record for each category that is selected on the web form?

    I'm guessing that I'll need to pass the categoryID's parameter into the procedure as a delimited string and then process this string for each categoryID and insert records into the PresentationFiles table using a While loop. I'm just not clear on how this is accomplished.

    Any advice on how to do it differently or other resources that you can point me to is very much appreciated.
    Last edited by athoma00; 09-03-04 at 13:22.

  2. #2
    Join Date
    Sep 2004
    Posts
    4
    Hi,
    What i would suggest here is that, pass the values of the checkboxes from your web page to your stored procedure in just a single variable delimited by a comma or whatever delimiter you wish to use.
    Within the Stored Procedure you can retrieve the value of all the variables with what ever delimiter you have passed.
    Ex: if you pass 1,2,3 from your web form, in the stored procedure you can run a loop and store these values in a table variable. I am pasting the code here for your ease.
    In my code i have used an asterix("*") as a delimiter. You can use any character u wish. please let me know if this helps.

    set nocount on
    DECLARE
    @NTAccount varchar(8000),
    @NTAccountNames varchar(8000)

    Select @NTAccountNames = '1*2*3*56'


    Declare @TempNTAccount table (NTAccount nvarchar(1000))
    Declare @lenNTAccount varchar(8000), @i int,@NTNames varchar(8000)

    Select @NTAccountNames = ltrim(rtrim(@NTAccountNames))
    Select @LenNTAccount = len(ltrim(rtrim(@NTAccountNames)))
    select @i=1
    Select @NTNames =''
    Select @NTAccount = null

    while (@i) <= @LenNTAccount
    Begin

    Select @NTAccount = SubString(@NTAccountNames,@i,1)

    --Here if we encounter a astreix it denotes one NT Account and therefore we Insert it in the table variable.
    if ((@NTAccount = '*' or @i = @LenNTAccount) and @NTAccount is not null and @NTAccount <> '' )
    Begin

    --The statement below checks of the end of the string is encountered and if the last character is not
    -- an asterix then the last NTAccountName would be concatenated.

    if (@i = @LenNTAccount and @NTAccount <> '*' and @NTAccount is not null and @NTAccount <> '' and ascii(convert(varchar(10),@NTNames)) is not null)
    Begin
    Select @NTNames = @NTNames + @NTAccount
    End
    --Before inserting a check is performed if the account name does not already exist.
    If not exists(Select 1 from @TempNTAccount where ltrim(rtrim(NTAccount)) = ltrim(rtrim(@NTNames)))
    Begin
    IF (ascii(convert(varchar(10),@NTNames))) is not null
    BEGIN
    Insert into @TempNTAccount
    (NTAccount)
    Values(rtrim(ltrim((@NTNames))))
    Select @NTNames=''
    END
    End


    End

    --We concatenate till we encounter an asterix and concatenate @NTNames with @NTAccount to get the name of the
    -- Account Name.
    Else

    Begin
    if (@NTAccount <> '*' and @NTAccount is not null and @NTAccount <> '')
    Select @NTNames = @NTNames + @NTAccount
    End

    --The @i variable is incremented by one to get the next character from the @NTAccountNames.
    select @i = @i + 1


    End

    Select * from @TempNTAccount
    Last edited by armurthy; 09-06-04 at 06:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •