Author |
Topic |
scottbak
Starting Member
16 Posts |
Posted - 2007-11-11 : 10:50:10
|
I am getting:Server: Msg 170, Level 15, State 1, Procedure alldates, Line 1Line 1: Incorrect syntax near 'd1'.Server: Msg 170, Level 15, State 1, Procedure alldates, Line 4Line 4: Incorrect syntax near 'do'.when I try this:drop procedure alldates;create procedure alldates (d1 date, d2 date)returns (thedate date)as beginwhile (d2 > d1) dobegin thedate = d1; d1 = d1 + 1; suspend;endend;Help! I am trying to get all the dates between a range in 2 sql columns. If there is a better way, it would be a huge help! I thought I found a solutionwith CROSS APPLY, but found it was SQL 2005! Scott |
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 11:03:05
|
Are you trying to return actual column values or just create the dates between two ranges?It isn't clear what you are doing, but it is clear you haven't looked at simple function or procedure creation. You must have variables passed as @d1 datetime,@d2 datetimeprocedures don't need a RETURNS statement, functions do..Again, not sure "what" it is you are trying to do this for, can you clarify what the desired result is needed to do?See this and the outputCreate procedure alldates (@d1 datetime, @d2 datetime)as Create TABLE #dates (mydate datetime not null) Declare @days int,@start int Select @Days = datediff(d,@d1,@d2) Select @start = 0 While @start <=@days Begin Insert Into #dates(mydate) Select dateadd(d,@start,@d1) Select @start = @start + 1 endSelect * from #datesdrop table #datesGOExec alldates '1/1/2007','1/15/2007' |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-11 : 11:03:34
|
your procedure is all wrong1. variable need to declare with @, eg @d1 date2. syntax for create procedure is wrong. You are mixing function syntax with procedure. Or are you trying to create a function ?3. T-SQL does not have while ... do4. . . . think you better use this instead http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE KH[spoiler]Time is always against us[/spoiler] |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 18:22:17
|
I have changed the whole query to:USE reservationsDECLARE @myTable TABLE (thedate smalldatetime, ag_year int, ag_month smallint, ag_day varchar(10), ag_message varchar(10), ag_bgcolor varchar(10), ag_action varchar(10), ag_boxit varchar(10), ag_fgcolor varchar(10), ag_bgimg varchar(10), ag_html varchar(10))declare @StartDate smalldatetimedeclare @EndDate smalldatetimedeclare @Days intdeclare @CurrentDay intdeclare @year intdeclare @month smallintdeclare @day varchar(10)set @StartDate = (select checkin from bookings)set @EndDate = (select checkout from bookings)set @Days = DATEDIFF (day, @StartDate , @EndDate+1 )set @CurrentDay = 0while @CurrentDay < @Daysbegininsert @myTable (thedate, ag_year, ag_month, ag_day, ag_message, ag_bgcolor, ag_action, ag_boxit, ag_fgcolor, ag_bgimg, ag_html) values (dateadd(dd, @CurrentDay, @StartDate), year(dateadd(dd, @CurrentDay, @StartDate)), month(dateadd(dd, @CurrentDay, @StartDate)), day(dateadd(dd, @CurrentDay, @StartDate)), 'Reserved', 'red', '', 'TRUE', '', '', '')set @CurrentDay = @CurrentDay + 1endselect * from @myTablebut I am getting this error:Server: Msg 512, Level 16, State 1, Line 15Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.how can I fix the above query to avoid the error?Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 18:27:31
|
The problem is here:set @StartDate = (select checkin from bookings)set @EndDate = (select checkout from bookings)So which value do you want to grab from bookings?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 18:50:32
|
I cehcked out the F_TABLE_DATE and I like that and it's fast! Good call. But I am getting an error passing 2 columns to the start and end dates.Here is what I have but I get errors:declare @start datetime, @End datetimeset @Start = (select checkin from bookings)set @End = (select checkout from bookings)select *from dbo.F_TABLE_DATE (@Start,@End )order by 1Error:Server: Msg 512, Level 16, State 1, Line 2Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Server: Msg 512, Level 16, State 1, Line 3Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Server: Msg 245, Level 16, State 1, Procedure F_TABLE_DATE, Line 723Syntax error converting the varchar value '******************************************************************** Error in function F_TABLE_DATE:* @FIRST_DATE cannot be null*******************************************************************' to a column of data type int.Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 18:53:33
|
Did you even see my post? You have to decide which value from bookings that you want to put into @Start and @End.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 18:54:26
|
So which value do you want to grab from bookings?I am trying to get all the dates between checkin and checkout for each row. but as soon as I have more than 1 row it failes with that errorScott |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 18:55:19
|
I was typing while you must of posted. sorry I didn't see it right away. :)Scott |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 18:57:36
|
Here is my table: Checkin Checkout adults children 11/30/2007 12/10/2007 2 0 12/18/2007 12/26/2007 2 0Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 18:59:11
|
You need to add a WHERE clause to those queries so that it knows which checkin and which checkout you want.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 19:01:24
|
I want to pull all dates between checkin and checkout in each row and display all of the results in one.Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 19:03:45
|
You can't do that with your current code. Perhaps you should start over and explain your problem in words rather than with your broken T-SQL. It is much easier for us to help when we can visualize the problem with sample data provided by the OP.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 19:07:14
|
Sorry for the lack of clarity.Here is what I wish to accomplsh:I have this table:Checkin Checkout adults children11/30/2007 12/10/2007 2 0 12/18/2007 12/26/2007 2 0etc..and I wish to pull all of the dates between the checkin and checkout columns for each row and pull the combined information.11/30/200712/1/200712/2/2007etc... then12/18/200712/19/200712/20/2007etc...Thanks for your help.Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 19:12:25
|
Your sample data is not helping make your problem clear. Where are you getting those dates from? Not the Bookings table data, but that other data you posted.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 19:15:37
|
Are you sure you need to repeat identical information for each row for this? What is going to happen to the subsequent data set? (am presuming you will keep the reservation ID or something at each row as well?)Perhaps there is a much simpler way to get the actual desired intent accomplished. As it stands you would have to cursor through the rows in the reservations table and pass the start and end dates through that procedure to populate this temp table. My guess is you are going to "do something" with that table once it is populated, and that there is another way to fry that fish. Poor planning on your part does not constitute an emergency on my part. |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 19:19:26
|
I am querying this infomation to post to a booking calendar on the web page. It will pull the dates from the table and post to the web that those days are no longer available.Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 19:21:36
|
quote: Originally posted by scottbak I am querying this infomation to post to a booking calendar on the web page. It will pull the dates from the table and post to the web that those days are no longer available.Scott
I'm not sure how that helps us with your problem. Could you explain?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 19:22:29
|
the dates are coming from the people posting to the web page. all data is populated once a credit card is processed succefully. Then I need to show all the dates that are reserved on the calendar. thus the need to pull all dates in between each checkin and checkout date for each row and put to the calendar. (plus I need all the other rows listed.ag_year, ag_month, ag_day, ag_message, ag_bgcolor, ag_action, ag_boxit, ag_fgcolor, ag_bgimg, ag_html)Scott |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-12 : 19:25:10
|
Well hopefully someone else can help you as I don't have time to go back and forth with this. If you'd like to see how properly to post a question, check out an old post of mine: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516Notice how quickly I got answers when I posted it this way. My problem was pretty complex too.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
scottbak
Starting Member
16 Posts |
Posted - 2007-11-12 : 19:31:32
|
All I need is the dates between the checkin and checkout columns in the table. What else do you need to understand? I have a query listed above that pulls fine for one column but not for more than one column. someone post above the use of F_TABLE_DATE and I installed that and the problem I have now is how to get it to pull the checkin and checkout columns from the table for EACH ROW. What difference does it make where any other data comes from?Scott |
|
|
Next Page
|