Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER procedure [dbo].[sp_insertstaffattendence](      
@staffid varchar(50),      
@name varchar(50),      
@date datetime,      
@morningstatus char,      
@eveningstatus char,    
@type varchar(20)    
) as      
begin      
if(@type = 'insert')    
begin    
declare @subject as varchar(50), @acadamicyear as varchar(20), @stmt as varchar(max)     
select @subject=Subject from sc_staff_tbl where StaffId=@staffid 
set @acadamicyear = (select presentacadamicyear from sc_school_general) 
set @stmt= 'insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('+@staffid+','+@name+','+@date+','+@morningstatus+','+@eveningstatus+','+@subject+',''Y'')'
select @stmt
--exec ('insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('+@staffid+','+@name+','+@date+','+@morningstatus+','+@eveningstatus+','+@subject+',''Y'')')    
end    
else    
begin    
exec('update sc_staffattendence'+@acadamicyear+' set Morningstatus='+@morningstatus+',Eveningstatus='+@eveningstatus+' where StaffId='+@staffid+' and year(Date)=year('+@date+') and month(Date)=month('+@date+') and day(Date)=day('+@date+')')    
end      
end
Posted 5-Jun-12 17:10pm
Edited 5-Jun-12 17:39pm
VJ Reddy38.6K
v2
Comments
VJ Reddy at 5-Jun-12 22:39pm
   
Edit: pre tag for SQL code added.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

At first glance, @date seems to be empty. Make sure you are defining this value somewhere in the procedure.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

thank you i solved.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
alter procedure [dbo].[sp_insertstaffattendence](      
@staffid varchar(50),      
@name varchar(50),      
@date datetime,      
@morningstatus char,      
@eveningstatus char,    
@type varchar(20)    
) as      
begin      
if(@type = 'insert')    
begin    
declare @subject as varchar(50), @acadamicyear as varchar(20), @stmt as varchar(max)     
set @subject='demo'
set @acadamicyear = '2012_2013' 
set @stmt= 'insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('''+@staffid+''','''+@name+''','''+convert(varchar(50),@date)+''','''+@morningstatus+''','''+@eveningstatus+''','''+@subject+''',''Y'')'
select @stmt
--exec(@stmt)
--exec ('insert into sc_staffattendence'+@acadamicyear+'(StaffId,Name,Date,Morningstatus,Eveningstatus,Subject,status) values('''+@staffid+''','''+@name+''','''+convert(varchar(20),@date)+''','+@morningstatus+','+@eveningstatus+','+@subject+',''Y'')')    
end    
else    
begin    
exec('update sc_staffattendence'+@acadamicyear+' set Morningstatus='+@morningstatus+',Eveningstatus='+@eveningstatus+' where StaffId='+@staffid+' and year(Date)=year('+@date+') and month(Date)=month('+@date+') and day(Date)=day('+@date+')')    
end      
end
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 230
1 PIEBALDconsult 150
2 DamithSL 125
3 Andreas Gieriet 90
4 Jochen Arndt 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 6 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100