Click here to Skip to main content
12,508,185 members (54,730 online)
Rate this:
 
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 16:10pm
Updated 5-Jun-12 16:39pm
VJ Reddy38.8K
v2
Comments
VJ Reddy 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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

'''+convert(VARCHAR(20),GETDATE()(or)what you want convert ,101)+'''

This is used to convert datetime to varchar
  Permalink  
v2
Comments
CHill60 20-Feb-15 8:57am
   
Question was asked and resolved over 2 years ago. This does not anything startling to the solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160927.1 | Last Updated 20 Feb 2015
Copyright © CodeProject, 1999-2016
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