Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 What is wrong with this query

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 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
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 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
[/code]

Chirag
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 03:15:23
Ouch me...



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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.

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

Go to Top of Page
   

- Advertisement -