SQL Server - how to check for record exists

Asked By rama chandran on 23-Sep-11 01:58 AM
Hi,

this is my trigger,

create trigger test on tblFromtest
after insert
as
begin
insert into tbltest(empid,empname,salary,dob) 
select * from tblFromtest
truncate table tblFromtest
end

inside this trigger i want to check, if the tbltest having records or not, if not exists then only the values should insert, otherwise dont insert.
Devil Scorpio replied to rama chandran on 23-Sep-11 02:18 AM
Hi Rama,

By using If Exists u can check the existence of record in database

Here is the query

if Exists(select * from sys.columns where Name = N'columnName'   
            and Object_ID = Object_ID(N'tableName')) 
 
begin 
 
    -- Column Exists 
 
end 
aneesa replied to rama chandran on 23-Sep-11 02:19 AM
01.CREATE TRIGGER CHECK_EXISTS
02.ON EXAM_SUBJECT
03.AFTER INSERT
04.as
05.begin
06.BEGIN TRAN
07.IF NOT EXISTS(SELECT * FROM EXAM_SUBJECT WHERE SUBJECTNAME=(SELECT SUBJECTNAME FROM inserted))
08.BEGIN
09.COMMIT
10.END
11.ELSE
12.ROLLBACK
13.RAISERROR('ALREADY EXIST',16,1)
14. 
15.END

aneesa replied to rama chandran on 23-Sep-11 02:30 AM
01.CREATE TRIGGER CHECK_EXISTS
02.ON EXAM_SUBJECT_1 --->TABLE1 -->tblFromtest
03.AFTER INSERT
04.as
05.begin
06.BEGIN TRAN
07.IF NOT EXISTS(SELECT * FROM EXAM_SUBJECT_2 WHERE SUBJECTNAME=(SELECT SUBJECTNAME FROM inserted)) -- EXAM_SUBJECT_2 -->tbltest
08.BEGIN
09.COMMIT
10.END
11.ELSE
12.ROLLBACK
13.RAISERROR('ALREADY EXIST',16,1)
14.
15.END

smr replied to rama chandran on 23-Sep-11 02:48 AM
HI
This is not necessarily a practical example, but let’s say that we have a table called “Users” which has three fields:  “UserID”, “FirstName”, and “LastName”.  If a record already exists with the specified UserID, simply update it with the new @FirstName and @LastName values.  If it does not exist, create a new record with those values.

CREATE PROCEDURE dbo.spAddUserName
   (
   @UserID AS int,
   @FirstName AS varchar(50),
   @LastName AS varchar(50)
   )
AS
   BEGIN
      DECLARE @rc int   
 
      UPDATE [Users]
       SET FirstName = @FirstName, LastName = @LastName
       WHERE UserID = @UserID  
 
      /* how many rows were affected? */
      SELECT @rc = @@ROWCOUNT   
 
      IF @rc = 0
         BEGIN
          INSERT INTO [Users]
                (FirstName, LastName)
             VALUES (@FirstName, LastName)
         END    
 
   END
dipa ahuja replied to rama chandran on 23-Sep-11 03:33 AM
Untitled document
ALTER PROCEDURE dbo.spWithReturnValue    
    (
    @Email varchar(50),
    @exists int=1 output
    )
    
AS
    /* SET NOCOUNT ON */    
 
     IF EXISTS (SELECT Email FROM users WHERE Email = @Email)  
     select @exists       
       
    ELSE
      set @exists=0
      select @exists        
RETURN
 
 
Use in .CS Code:
 
protected void btnCheck_Click(object sender, EventArgs e)
{
   string email = TextBox1.Text;
   int exist = 0;
   string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
   SqlConnection conn = new SqlConnection(ConnString);
 
   conn.Open();
   SqlCommand comm = new SqlCommand("spWithReturnValue", conn);
   comm.Parameters.AddWithValue("Email", email);
   comm.CommandType = CommandType.StoredProcedure;
 
   //returning the output
 
   exist = int.Parse(comm.ExecuteScalar().ToString());
   if (exist == 1)
   {
     LblResult.Text = "<font color='green'>Email Exist in table</font>";
   }
   else
   {
     LblResult.Text = "<font color='red'>Email Not Exist</font>";      
  }
}