SQL Server Performance

Problem inserting application logic into stored pr

Discussion in 'Performance Tuning for DBAs' started by babak62, Jul 13, 2004.

  1. babak62 New Member

    I have a stored procedure which looks like this;
    CREATE PROCEDURE DiamondsByTable
    (@pf_id varchar (100),
    @item_id char(2),
    @name varchar(255),
    @description varchar(255),
    @list_price int,
    @image_file varchar(255),
    @cost_price int,
    @sale_price int,
    @sale_start datetime,
    @sale_end datetime,
    @status int,
    @Lab varchar(30),
    @Cut varchar(20),
    @Color char(2),
    @Clarity char(3),
    @Weight char(5),
    @Diameter1 char(10),
    @Diameter2 char(10),
    @Height char(10),
    @Depth char(10),
    @Table char(10),
    @Crown char(10),
    @pavillion char(10),
    @Gridle char(15),
    @polish char(15),
    @Symmetry char(10),
    @fluorescence char(10),
    @Cutlet char(10),
    @Appraisal char(10),
    @Comment varchar(500),
    @dept_id int,
    @parcel char(15))


    INSERT INTO diamonds_product (pf_id, item_id, name, description, list_price,image_file, cost_price,sale_price,
    sale_start, sale_end, status)
    VALUES (@pf_id, @item_id, @name, @description, @list_price,@image_file, @cost_price,@sale_price,
    @sale_start, @sale_end, @status)

    INSERT INTO diamonds_table (pf_id, lab, cut, color, clarity, weight, diameter1, diameter2, height,
    Depth, [table], crown, pavillion, Gridle, polish, symmetry, fluorescence, cutlet, appraisal, comment)
    VALUES (@pf_id, @lab, @cut, @color, @clarity, @weight, @diameter1, @diameter2, @height,
    @Depth, @table, @crown, @pavillion, @Gridle, @polish, @symmetry, @fluorescence, @cutlet, @appraisal, @comment)

    INSERT INTO diamonds_dept_prod (pf_id, dept_id)
    VALUES (@pf_id, @dept_id)

    INSERT INTO diamonds_parcel (pf_id, parcel)
    VALUES (@pf_id, @parcel)

    is there anyway to replace the third insert statement with this?? I don't know the correct syntax. Thanks for the help. the catch is sometimes a product should belong to two or more departments so if there is more than one value for pf_id I want to add one more row in the diamonds_dept_prod the third insert satement just insert one row and if there is another value for dept_id it will igore it. If you can tell me how to tailor the syntax I really appreciate that because syntax is always my problem.

    if Request("dept_id").Count <> 0 then
    for index = 1 to Request("dept_id").Count
    dept_id = Request("dept_id")(index)
    if dept_id < 0 or dept_id > 20000 then
    errorList.Add "dept_id must be a between 0 and 20000"
    sqlText = "INSERT INTO diamonds_dept_prod VALUES :)1, :2)"
    sqlText = Replace(sqlText, ":1", dept_id)
    sqlText = Replace(sqlText, ":2", "'" & Replace(pf_id,"'","''") & "'")
    Set cmdTemp = cycleADOConnection()
    cmdTemp.CommandText = sqlText
    rsDept.Open cmdTemp, , adOpenStatic, adLockOptimistic
    end if
    end if

Share This Page