Author |
Topic |
ceema
Yak Posting Veteran
80 Posts |
Posted - 2006-09-06 : 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 isCREATE 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 awhere -- 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)EndGO |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-06 : 03:14:27
|
[code]Select @sql='select SRegNo as RegNo,SEnglishName as Name,SClass,sDateofbirth as DateofBirth,Day(sDateofBirth)-Day(getdate()) as Noofdays from Student awhere-- Find next birthday after todaycasewhen 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-- Tomorrowdateadd(dd,datediff(dd,0,getdate()),0) and-- Tomorrow plus 6 daysdateadd(dd,datediff(dd,0,getdate())' + @NoDay + ',0) Order By ' + @odBy [/code]Chirag |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-06 : 03:14:49
|
-- Tomorrow plus 6 daysdateadd(dd,datediff(dd,0,getdate())+' + cast(@NoDay as varchar) + ',0) Order By ' + @odBy Do you really need Dynamic SQL for this?Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-06 : 03:15:23
|
Ouch me...Peter LarssonHelsingborg, Sweden |
|
|
ceema
Yak Posting Veteran
80 Posts |
Posted - 2006-09-06 : 04:09:48
|
Thank you, Yes,it gave me the solution,it's working fine,Thank you chirag too.RegardsCeemaquote: Originally posted by Peso -- Tomorrow plus 6 daysdateadd(dd,datediff(dd,0,getdate())+' + cast(@NoDay as varchar) + ',0) Order By ' + @odBy Do you really need Dynamic SQL for this?Peter LarssonHelsingborg, Sweden
|
|
|
|
|
|