incorrect syntax in this stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

incorrect syntax in this stored procedure

Author

Topic

emmy_23
Starting Member

2 Posts

Posted - 2009-12-17 : 08:49:49

hello there,
i'm making stored procedure for inserting data into database if..same data exists i'll make update.
this is my syntax
create procedure sp_attendance5 @EnrollNo int=null,@name1 varchar(20)=null,
@datetime1 datetime=NULL,@check1 varchar(20)=null,@num int= null
as
begin
if @datetime1>=dateadd(dd,DATEDIFF(dd,0,getdate ()),0)
insert into attendance(EnrollNo,name1,datetime1,check1) values (@EnrollNo,@name1,@datetime1,@check1)
else
if exists(update attendance set (@EnrollNo=EnrollNo,@name1=name1,@datetime1=datetime1,@check1=check1,@num=num)--where num=@num)
--where max(@datetime1)=dateadd(dd,DATEDIFF(dd,0,getdate()),0) and @datetime1=convert(varchar(10), getdate(), 108) )
end
go

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-17 : 09:09:29

Well there is already a parenthasis missing

if exists(update attendance set (@EnrollNo=EnrollNo,@name1=name1,@datetime1=datetime1,@check1=check1,@num=num))--

^ Thats if you've commented out the entire WHERE clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 09:37:41

Also if exists(update...) is not a valid command in SQL Server

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 09:39:48

I think the code should be

create procedure sp_attendance5 @EnrollNo int=null,@name1 varchar(20)=null,
@datetime1 datetime=NULL,@check1 varchar(20)=null,@num int= null
as
begin
if @datetime1>=dateadd(dd,DATEDIFF(dd,0,getdate ()),0) 
insert into attendance(EnrollNo,name1,datetime1,check1) values (@EnrollNo,@name1,@datetime1,@check1)
else
        Begin
	update attendance set EnrollNo=@EnrollNo,name1=@name1,datetime1=@datetime1,check1=2check1,num=@num where num=@num
	end
end
go

Madhivanan

Failing to plan is Planning to fail

emmy_23
Starting Member

2 Posts

Posted - 2009-12-19 : 05:31:29

Hello madhivanan,
i should use if exists or comparing the data exists in the database with today's date.as,after inserting the data in the database.i'm selecting it from the visual studio and i don't want it to be redundant.
i tried to make it like this..
create procedure sp_attendance5 @EnrollNo int=null,@name1 varchar(20)=null,
@datetime1 datetime=NULL,@check1 varchar(20)=null,@num int= null
as
begin
if (@datetime1>=dateadd(dd,DATEDIFF(dd,0,getdate()),0))
insert into attendance(EnrollNo,name1,datetime1,check1) values (@EnrollNo,@name1,@datetime1,@check1)
else
begin
if (@datetime1=@datetime1)
update attendance set EnrollNo=@EnrollNo,name1=@name1,datetime1=@datetime1,check1=@check1 where num=@num

end
end
go

it didn't work.i'm grasping those inserted data from a buffer from a finger print device.
please,help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources