Click here to Skip to main content
15,885,985 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi friends
This is my stored procedure
SQL
alter procedure sp_retreivesmsvalue(@stid int,@currenttime datetime)
as
declare @receiverid int
declare @time datetime
declare @time1 time(7)
declare @date varchar(10)
set nocount on
begin
--set @time=@currenttime
select @receiverid=Contact_id from Student_Contact_details where Student_id=@stid
SELECT @time= Convert(char(2),DATEPART(hour,@currenttime)) + ':'+convert(char(2),datepart(mi,@currenttime)) + ':' + convert(char(2),datepart(S,@currenttime))
set @time1=CONVERT(time(7),@time)
declare @intimemorning1 time(7),@intimemorning2 time(7)
declare @outtimeevening1 time(7),@outtimeevening2 time(7)
declare @outtimenoon1 time(7),@outtimenoon2 time(7)
declare @workingdaytype varchar(50)
set @intimemorning1='06:00:00'
set @intimemorning2='09:30:00'
set @outtimeevening1='16:15:00'
set @outtimeevening2='23:30:00'
--select  DATEADD(D,0,DATEDIFF(D,0,GETDATE()))--from Workingday_details
SELECT @date= CONVERT(date,@currenttime,111) from Workingday_details where Working_date=@currenttime
select @workingdaytype= Workingday_type from Timetemplate_details where Time_id in(select Time_id from Workingday_details where Working_date=@date)
if @workingdaytype='full day'
begin
if @time1 between @intimemorning1 and @intimemorning2
begin
select m.Receiver_id,t.Template,t.Template_id,k.Phone_number from Message_configuration_details m inner join Message_template_details t on m.Template_id=t.Template_id inner join Student_Contact_details k on m.Receiver_id=k.Contact_id where t.Template_id='201' and m.Receiver_id=@receiverid

end
if @time1 between @outtimeevening1 and @outtimeevening2
begin
select m.Receiver_id,t.template,t.Template_id ,k.Phone_number from Message_configuration_details m inner join Message_template_details t on m.Template_id=t.Template_id inner join Student_Contact_details k on m.Receiver_id=k.Contact_id where t.Template_id=202 and m.Receiver_id=@receiverid
end


--SELECT CONVERT(VARCHAR(10),GETDATE(),103)
--select CONVERT(date,getdate(),111)
end
end


i just pass datetime value from front end c# but how can i run from ssms.because when using getdate() for input parameter it doesnt accept.how can i solve this problem?
Posted
Comments
[no name] 8-Sep-12 13:47pm    
"it doesnt accept", and what does that mean? If you run this in SSMS then it will ask you for the parameters. You have not told us what the problem even is to be able to answer.
Sandeep Mewara 8-Sep-12 13:51pm    
Yes you can. Now what is the issue that you are facing?
baskaran chellasamy 8-Sep-12 14:27pm    
thanks for reply. I solve the problem myself.i just pass datevalue to input paramters. its work fine now

Answered only to remove from unanswered list - solved by OP.
 
Share this answer
 
SQL
declare @myvar datetime
select @myvar = getdate()
exec myproc @myvar


By the way, you should never start the name of a stored procedure of your own with 'sp_'. SQL Server thinks this means 'system procedure', and looks for it in all schemas in database MASTER before looking in your own database, thus generating needless overhead and degrading performance.

Hope this helps,

Pablo
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900