Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: create store procedure

    I need your help again.
    I want to create a store procedure that add new employee name to employee table. Before insert i would like to check wheter there already has this employee name. if so, don't insert.

    i have two input parameters (@fname, @lname).
    Thanks.

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Searching by Name(s) Bad Idea

    how to do this? (don't) if you need to
    You can write an insert statement using not exists statement
    Insert into tbltest
    (lname, fname)
    select
    'Jones', 'John'
    where not exists
    (select 'x' from
    tbltest
    where
    lname = 'Jones'
    and fname = 'John')

    you can write and if statment setting a variable = count(*) of select statement using input values in the where clause and then evaluate the if

    set @var = 0
    set @var = (select count(*) from blah blah blah)

    if @var = 0 then
    blah
    else blah

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    As i create a addemployee asp.net form. I don't want the people add more than once. That is why i need check whether the employee name is already exists. What do you think i need to check? Thanks. I am familar with the store procedure syntax. I believe the store procedure is good for this, right?
    Many thanks.

  4. #4
    Join Date
    Mar 2004
    Posts
    660
    Can you help me to check the syntax. I got error that syntax error near then.
    thanks.


    CREATE PROCEDURE [AddNewEmployee]
    (@efname varchar(25),
    @elname varchar(25)
    )

    AS
    decare @var int

    set @var =0
    set @var =(SELECT count(*) FROM tblEmployee
    where EmployeeFName =@efname and EmployeeLName =@elname)
    if @var =0 then

    insert into tblEmployeeName
    (EmployeeFName, EmployeeLName)
    values (@efname,@elname)

  5. #5
    Join Date
    Mar 2004
    Posts
    660
    Thanks. i figured out. There is no then keyword. I still have a question. If the employee name exists in the table. I would like the user knows he or she can't access it. Do i need a return value or string? Thanks.

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by yyu
    Thanks. i figured out. There is no then keyword. I still have a question. If the employee name exists in the table. I would like the user knows he or she can't access it. Do i need a return value or string? Thanks.
    Sorry, can't figure out what u want..plz provide some more details..
    Joydeep

  7. #7
    Join Date
    Mar 2004
    Posts
    660
    Sure. I am creating a web application using asp.net. I have a form called addemployeename.aspx. This form allows us to add new employee. First i created a strore procedure in the previous. Then i want to call this store procedure in addemployee.aspx. In the store procedure, i tried to avoid to add more than one same emaployee name. If you add one more user. the store procedure won't insert the new record. But I would like it can return something so that i can display a message that the employee name you just added is alreday in the database. How could i do this?
    Thanks.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    CREATE PROCEDURE [AddNewEmployee]
    (@efname varchar(25),
    @elname varchar(25)
    )

    AS
    if exists (SELECT 1 FROM tblEmployee
    where EmployeeFName =@efname and EmployeeLName =@elname
    )
    return (1)

    insert into tblEmployeeName
    (EmployeeFName, EmployeeLName)
    values (@efname,@elname)
    return (@@error)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Mar 2004
    Posts
    660
    Thanks. i will try. The store procedure is amazing. I am going to try to run in asp.net. How could l received the error from web form? Please let me know if you use asp.net. Many thanks.

Posting Permissions

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