Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Thumbs up Unanswered: SQL Server Constraint20000------Please help

    Dear Friends,
    How do write the below dynamic update Query? I am new to this SQL Query.Its
    very very urgent requirements.
    If anyone knows please help me the below SQL query.
    I would appreciate any help.


    FieldName datatYpe

    EmpName - string
    EmpNo - int
    Sex - string
    Salary - Float
    DoB - datetime
    Address - string
    Remarks - string
    hours - int
    AvailableDate -dtatetime

    Passed Parameters: EmpNo or EmpName, sex, Salary, DOB, Address,
    Remarks,hours, AvailableDate

    Please note, this method is overloaded. Although either the EmpNo or
    EmpName is required in order to select the Employee record, one or more of
    the other variables may be passed.

    Return Parameters: ErrorCode

    Pseudo Code:

    1. Confirm that either the EmpNo or the EmpName exists in the database

    2. Confirm that at least one other parameter is passed

    3. Confirm that all passed parameters are of the correct data type

    4. Update the Employee Table using the Passed Parameters to modify the


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    create procedure dbo.UpdateEmployee (
    @EmpName varchar(255) = null,
    @EmpNo int = null,
    ... )
    if not exists (
    select * from dbo.EmployeeMaster where (EmpNo = @EmpNo or EmpName = @EmpName) begin
    raiserror ('Invalid Employee specified!', 15, 1)
    return (1)
    end else begin
    if @EmpNo is null
    select @EmpNo = EmpNo from EmployeeMaster where EmpName = @EmpName
    -- At this point we KNOW that @EmpNo has a value, - whether it was passed or we retrieved it ourselves

    -- Go ahead and check the validity of the rest of the variables
    -- ...
    -- Perform the UPDATE using EmpNo = @EmpNo, because most likely you have a UNIQUE constraint or index on it or a CLUSTERED index, - thus the query will be faster.

Posting Permissions

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