| Author |
 Topic  |
|
|
ceema
Yak Posting Veteran
80 Posts |
 Posted - 09/06/2006 : 02:53:41
|
Hello,
I have a query like the following, but with asp.net it's giving the error like 'Must declare the variable '@NoDay'. '
Query is
CREATE PROCEDURE sp_StudentBDaySort
@SchoolID Nvarchar(10)=Null,
@OdBy NVarchar(50)=Null,
@Mode NVarchar(10)=Null,
@NoDay int=Null
AS
Begin
Declare @sql as nvarchar(2000)
Declare @today datetime
Select @sql='select SRegNo as RegNo,SEnglishName as Name,SClass,sDateofbirth as DateofBirth,Day(sDateofBirth)-Day(getdate()) as Noofdays from Student a
where
-- Find next birthday after today
case
when dateadd(yy,datediff(yy,sDateofBirth,getdate()),sDateofBirth) >
dateadd(dd,datediff(dd,0,getdate()),0)
then dateadd(yy,datediff(yy,sDateofBirth,getdate()),sDateofBirth)
else dateadd(yy,datediff(yy,sDateofBirth,getdate())+1,sDateofBirth)
end between
-- Tomorrow
dateadd(dd,datediff(dd,0,getdate()),0) and
-- Tomorrow plus 6 days
dateadd(dd,datediff(dd,0,getdate())+@NoDay ,0) Order By ' + @odBy
exec (@sql)
End
GO
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/06/2006 : 03:14:27
|
Select @sql='select SRegNo as RegNo,SEnglishName as Name,SClass,sDateofbirth as DateofBirth,Day(sDateofBirth)-Day(getdate()) as Noofdays from Student a
where
-- Find next birthday after today
case
when dateadd(yy,datediff(yy,sDateofBirth,getdate()),sDateofBirth) >
dateadd(dd,datediff(dd,0,getdate()),0)
then dateadd(yy,datediff(yy,sDateofBirth,getdate()),sDateofBirth)
else dateadd(yy,datediff(yy,sDateofBirth,getdate())+1,sDateofBirth)
end between
-- Tomorrow
dateadd(dd,datediff(dd,0,getdate()),0) and
-- Tomorrow plus 6 days
dateadd(dd,datediff(dd,0,getdate())' + @NoDay + ',0) Order By ' + @odBy
Chirag |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/06/2006 : 03:14:49
|
-- Tomorrow plus 6 days
dateadd(dd,datediff(dd,0,getdate())+' + cast(@NoDay as varchar) + ',0) Order By ' + @odBy
Do you really need Dynamic SQL for this?
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 09/06/2006 03:15:06 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/06/2006 : 03:15:23
|
Ouch me...
Peter Larsson
Helsingborg, Sweden |
|
|
|
ceema
Yak Posting Veteran
80 Posts |
Posted - 09/06/2006 : 04:09:48
|
Thank you,
Yes,it gave me the solution,it's working fine,
Thank you chirag too.
Regards
Ceema
quote:
Originally posted by Peso
-- Tomorrow plus 6 days
dateadd(dd,datediff(dd,0,getdate())+' + cast(@NoDay as varchar) + ',0) Order By ' + @odBy
Do you really need Dynamic SQL for this?
Peter Larsson
Helsingborg, Sweden
|
|
|
| |
Topic |
|
|
|
What is wrong with this query
