SQLTeam.com | Weblogs | Forums

I need to check whether username,modified date and id fields with values and remaining all fields with null values are there in my table,then i need to return that id else perform insert operation

tsql
sql2008

#1

CREATE PROCEDURE [dbo].[newrough]
(

@ConsultantName varchar(50) ,
@ClientName varchar(50) ,
@StartDate varchar(50),
@PositionName varchar(20) ,
@Location varchar(20) ,
@Job_Status varchar (20),
@BenchMarketing varchar(1) ,
@Placement varchar(1),
@CompanyName varchar(20),
@Durations varchar(20),
@DurationofProject varchar(10),
@Last_Updated_Date nvarchar(50),
@Rec_Name varchar(50),
@id int output
--@out int out
)
AS
BEGIN

SET NOCOUNT ON

IF EXISTS( )
_ BEGIN_
_ INSERT INTO()_
_ ENd_
ELSe
BEGIN
INSERT INTO tbl_Empincentivenew1
OUTPUT INSERTED.id
values(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@Last_Updated_Date,NULL,@Rec_Name)
--DEFAULT VALUES
SET @id=SCOPE_IDENTITY()
RETURN @id
END

END


#2

IF EXISTS (SELECT * FROM tbl_Empincetivenew1 WHERE username = @var1 AND modified_date = @var2 AND id = @var3 AND column4 IS NULL AND column5 IS NULL AND ........)

But I wonder if this would be better handled by adding a unique index/constraint across those columns, then just do the INSERT and handle the exception that will occur if you try to insert the same row.