error next to select ? - 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)

error next to select ?

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-21 : 11:44:32

hi ,
when I run this :

SET @HistoryID = select max(substring(historyID,3,6)) + 1 AS HistoryID from callshistory
where historyid like 'HH%' order by historyid desc

I get an error saying :
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.

what is wrong here ?
thanlk you
Jamie

Skierm2
Starting Member

2 Posts

Posted - 2004-07-21 : 12:00:39

You don't need the "SElect"

You just need this
SET @HistoryID = max(substring(historyID,3,6)) + 1 AS HistoryID from callshistory
where historyid like 'HH%' order by historyid desc

chadmat
The Chadinator

1974 Posts

Posted - 2004-07-21 : 12:38:33

I would do it like this:

SELECT @HistoryID = max(substring(historyID,3,6)) + 1 AS HistoryID from callshistory
where historyid like 'HH%' order by historyid desc

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Kristen
Test

22859 Posts

Posted - 2004-07-21 : 14:00:51

As Skierm2 says your don't need the SELECT, but if you did it would have to be in ( parenthesis ) - which is what the error is refering to.

Kristen

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-21 : 20:04:27

I'd have to go with Chad on this one. Since you're pulling from a table, the SET command won't work unless you use the parentheses around the whole SELECT statement (as Kristen mentioned). But Chad's version is a little shorter.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-22 : 04:31:27

thanks gurus.
also this will work :

SET @HistoryID = (select top 1 max(substring(historyID,3,6)) + 1 AS HistoryID from callshistory
where historyid like 'HH%' order by historyid desc)

is this what you mean when you say parentheses ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-22 : 04:38:56

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-22 : 04:39:11

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 05:33:21

"is this what you mean when you say parentheses ?"

Yes. Here in the UK I would call them brackets, but I said "parenthesis" because I thought that was the American vernacular, but you probably aren't based in USA posting at 04:30 !!

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-22 : 05:39:37

thank you Kristen, no I am not in the US, I am based in the UK.
thanks for all the help !

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-22 : 06:43:36

hi, finally written a curosr that incorpates this but it takes over 4 mins to update 2000 records, seems slow to me :
can this be changed to improve performance :

declare crs_close cursor
for
select callID from calls WHERE calls.DateClosed is null
Open crs_close
declare @callID varchar(50), @HistoryID varchar(50)
print 'cursor to close calls'
print '------------------------------'
fetch next from crs_close into @callID
while @@fetch_status = 0 begin

SET @HistoryID = (select top 1 max(substring(historyID,3,6)) + 1 AS HistoryID from callshistory
where historyid like 'HH%' order by historyid desc)
SET @HistoryID = @HistoryID

INSERT CallsHistory (
HistoryID,
[Date],
FKey )
VALUES
(
'HH'+@HistoryID,
GetDate(),
@CallID
)
fetch next from crs_close into @CallID
end
close crs_close
deallocate crs_close

or is 4 mins ok ?
thank you.

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 07:04:48

I don't quite understand why you don't need a correlation between CALLS and CALLSHISTORY.

I suppose I would have epxected something like


INSERT dbo.CallsHistory
(
	HistoryID,
	[Date],
	FKey
)
SELECT	'HH' + MAX(SUBSTRING(CH.historyID, 3 ,6)) + 1,
	GetDate(),
	C.callID
FROM	dbo.callshistory CH
	JOIN dbo.calls C
		ON C.XXX = CH.YYY
WHERE	    CH.historyid LIKE 'HH%' 
	AND C.DateClosed IS NULL 
GROUP BY C.callID

and also that the CALLS record (with C.DateClosed IS NULL) would need to be chaged, otherwise it will just come up the next time this is run

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-22 : 07:35:03

oh yeah, I've changed the dateclosed to getdate().
so are you saying I don't needa cursor to do this ?
I've only just read about cursors, never used them in the past.
are they overrated ?

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 09:03:29

"overrated"? Hahahahahaha!

I think on here you will find them "un-rated".

Do it all set-based if you possibly can. Usually an order of magnitude faster, sometimes two orders of magnitude.

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-23 : 09:21:34

really, they are that bad ?
why do some books recommend them and actually say cursors are a fast way to loop through records.
is this simpily not true ?
or are they only effective with Oracle ?
thanks for any advice / information

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 09:35:04

Well, if you are accessing your data via ADO, say, then [as I understand it] ADO will be using a cursor to physically traverse the recordset. That's OK performancewise.

But if you are writing a SQL process which processes a number of records (update stock levels, or something like that) there is no good reason to use a Cursor unless what you want to do cannot be done "set based".

It's a common thing for people coming from programming in Basic or whatever to think in terms of FOR I = 1 TO 10 when they first start out with SQL, me included!, so they tend to use cursors 'coz it "feels right". Takes a while to get use to thinking out-of-the-box and into-the-set-method!

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-07-23 : 09:44:10

thanks Kristen.
I never use cursors , I was just reading through a SAMS book and they show examples of cursors, so thought I'd try them out.

thank you again for all the help.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-23 : 10:22:27

quote:
Originally posted by Kristen

"is this what you mean when you say parentheses ?"

Yes. Here in the UK I would call them brackets, but I said "parenthesis" because I thought that was the American vernacular, but you probably aren't based in USA posting at 04:30 !!

Kristen

Then what do you call these? [ and ]?

And what's with all the "s"'s by the way?

Denormalisation, optimisation, desalisation?

"Z" baby!

And Cursors have their place...it's just that they are so WIDELY misused...

And Oracle is a different animal....

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 10:58:16

( and ) = Brackets
[ and ] = Square Brackets
{ and } = Curly Brackets, or Braces

and the pound sign is £

and colour is spelt "colour"

and dates should be formatted 31-Dec-2004

and the bathroom is where the bath is kept.

Kris10

chadmat
The Chadinator

1974 Posts

Posted - 2004-07-23 : 12:15:31

quote:
Originally posted by Kristen

( and ) = Brackets
[ and ] = Square Brackets
{ and } = Curly Brackets, or Braces

and the pound sign is £

and colour is spelt "colour"

and dates should be formatted 31-Dec-2004

and the bathroom is where the bath is kept.

Kris10

And Football is played with 11 on each team, using your hands and scoring touchdowns.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources