SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 What is wrong with this query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ceema
Yak Posting Veteran

80 Posts

Posted - 09/06/2006 :  02:53:41  Show Profile  Reply with Quote
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  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/06/2006 :  03:14:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/06/2006 :  03:15:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Ouch me...



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ceema
Yak Posting Veteran

80 Posts

Posted - 09/06/2006 :  04:09:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000