Incorrect syntax errors - SQL Server Forums

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

Transact-SQL (2000)

Incorrect syntax errors

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 1
Line 1: Incorrect syntax near 'd1'.
Server: Msg 170, Level 15, State 1, Procedure alldates, Line 4
Line 4: Incorrect syntax near 'do'.

when I try this:

drop procedure alldates;
create procedure alldates (d1 date, d2 date)
returns (thedate date)
as begin
while (d2 > d1) do
begin
thedate = d1;
d1 = d1 + 1;
suspend;
end
end;

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 datetime

procedures 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 output



Create 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
		
	end

Select * from #dates

drop table #dates
GO

Exec 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 wrong
1. variable need to declare with @, eg @d1 date
2. 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 ... do
4. . . .

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 reservations
DECLARE @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 smalldatetime
declare @EndDate smalldatetime
declare @Days int
declare @CurrentDay int
declare @year int
declare @month smallint
declare @day varchar(10)
set @StartDate = (select checkin from bookings)
set @EndDate = (select checkout from bookings)
set @Days = DATEDIFF (day, @StartDate , @EndDate+1 )
set @CurrentDay = 0
while @CurrentDay < @Days
begin
insert @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 + 1
end

select * from @myTable

but I am getting this error:
Server: Msg 512, Level 16, State 1, Line 15
Subquery 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 datetime
set @Start = (select checkin from bookings)
set @End = (select checkout from bookings)
select *
from
dbo.F_TABLE_DATE (@Start,@End )
order by 1

Error:
Server: Msg 512, Level 16, State 1, Line 2
Subquery 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 3
Subquery 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 723
Syntax 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 error

Scott

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 0

Scott

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 children
11/30/2007 12/10/2007 2 0
12/18/2007 12/26/2007 2 0
etc..

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/2007
12/1/2007
12/2/2007
etc... then
12/18/2007
12/19/2007
12/20/2007
etc...

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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=42516

Notice how quickly I got answers when I posted it this way. My problem was pretty complex too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources