Variables to make dynamic queries

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)

Variables to make dynamic queries

Author

Topic

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 09:01:50

Does somebody know why I get an syntax error by @tAantal?

DECLARE @tAantal INT
@tAantal = 1

SELECT max(SampleTime) FROM tblTempArray
WHERE SampleTime in (SELECT top @tAantal SampleTime FROM tblTempArray

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-30 : 09:03:44

because you can't use variables in top

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 09:04:20

TOP @var only available in SQL 2005. It is not available in 2000.

What are you trying to do here ?

it seems to me that you wanted


SELECT max(SampleTime) FROM tblTempArray

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-30 : 09:06:07

1st syntax error - SET keyword missing in variable initialization
2nd syntax error - TOP value needs to be constant
3rd syntax error - Missing right hand parenthesis for the subquery

Hope that helps.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 09:11:28

I want for example the 3rd record but this vary everytime

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 09:24:54

It vary because I set it into a while loop

DECLARE @aantal INT
DECLARE @tAantal INT

SET @aantal = (SELECT count(RecordID) FROM tblTempArray)
SET @tAantal = 1

WHILE(@tAantal < @aantal)
BEGIN

SET @sTime = ( SELECT max(SampleTime) FROM tblTempArray
WHERE SampleTime in (SELECT top @tAantal SampleTime FROM tblTempArray) )

SET @tAantal = (@tAantal+1
)
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 09:41:25

can you explain what are you trying to achieve here ?

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 09:48:44

It should be reads every record from the table tblTempArray. Based on the record values I have to calculate something

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 09:54:51

What kind of calculation ? example ? Care to elaborate more on this ? There might be easier way to do this.

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 10:10:11

I've an table with sampletimes. There are iterations from 1-64. For every iteration I need the 1 and 64 value

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 10:15:43

yes it works..

DECLARE @tAantal INT
SET @tAantal = 1

SELECT max(SampleTime) FROM tblTempArray
WHERE SampleTime in (SELECT top (@tAantal) SampleTime FROM tblTempArray)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 10:16:52

you want the top 64 samples ?

select top 64 SampleTime
from  tblTempArray
order by SampleTime desc

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 10:18:07

not jet..

it was mssql2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 10:23:38

what jet ?

You are using MS SQL 2005 ? Why posting in a 2000 forum ?

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 10:26:14

No, in every iteration 1-64 records there is one with the value '(empty)' others have the value 1-64, I need the timestamp of this record (with the value '(empty)') and the timestamps of the values 1 and 64

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 10:27:48

Sorry but I tested it into 2005, but finally it must in 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 10:33:27

quote:
Originally posted by Nowy

yes it works..

DECLARE @tAantal INT
SET @tAantal = 1

SELECT max(SampleTime) FROM tblTempArray
WHERE SampleTime in (SELECT top (@tAantal) SampleTime FROM tblTempArray)

The query in red, using TOP without ORDER BY does not guaranty you will get the SampleTime in the ordering that you required.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-30 : 10:34:08

[code]
DECLARE @tAantal INT
select @tAantal = 1

set rowcount @tAantal
SELECT SampleTime INTO #temp FROM tblTempArray order by SomeColumnOfYours
set rowcount 0

SELECT max(SampleTime)
FROM tblTempArray t1
WHERE EXISTS (SELECT * FROM #Temp where SampleTime = t1.SampleTime)
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 10:44:49

could you explain a little bit? What means 'select', 'rowcount' and '#temp'?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-30 : 11:00:00

BOL = Books Online = sql server help is your best friend.
#temp is a temporary table created in tempdb
Select selects the data you need or assigns a value to a variable
rowcount sets how many rows you want to return from your resultset
set rowcount 0 returns all rows.

are you using MS SQL Server or some other database engine??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Nowy
Yak Posting Veteran

57 Posts

Posted - 2007-05-30 : 11:20:04

MS SQL Server 2000

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources