Variable in SP - 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)

Variable in SP

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-18 : 08:42:27

I want a variable to determine the top xx amount of rows in a table by inputing a variable in the SP.

here is my code:

Create Proc SP_TEST

@Top_Quantity numeric
as
select top @Top_Quantity from TABLE

Do I need to write the SP in as string and exec the string?

Thanks in advance.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-18 : 08:48:05

Make use of SET ROWCOUNT n.

Create Proc SP_TEST
@Top_Quantity int
as
Set Rowcount @Top_Quantity
select Quantity from TABLE
Set Rowcount 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 08:48:09

No, not with SQL Server 2005. Just put paranthesis around the TOP value.

select top (@Top_Quantity) * from TABLE

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-18 : 08:48:24

Yes.

Or you can select all the rows into a new table with an additional identity column and then select from the new table filtering on everything with an identity column <= @Top_Quantity.

The latter method could get nasty if it is not kept under control.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-18 : 08:50:50

quote:
Originally posted by Peso

No, not with SQL Server 2005. Just put paranthesis around the TOP value.

select top (@Top_Quantity) * from TABLE

Peter Larsson
Helsingborg, Sweden

I didn't know that.
That can be quite usefull - Thanks Peter.

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 10:13:39

quote:
Originally posted by ditch

quote:
Originally posted by Peso

No, not with SQL Server 2005. Just put paranthesis around the TOP value.

select top (@Top_Quantity) * from TABLE

Peter Larsson
Helsingborg, Sweden

I didn't know that.
That can be quite usefull - Thanks Peter.

Duane.

SQL Server 2005 avoided Dynamic SQL with TOP @var

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 10:17:45

SET ROWCOUNT or TOP are meaningless until the resultset set is ordered by any column

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-19 : 00:56:00

>>SQL Server 2005 avoided Dynamic SQL with TOP @var

Did sql server 2005 avoid it? or are you trying to suggest that I should avoid using it.

I don't see how it could cause any problem. For one - it is no longer Dynamic (There is no Exec String being built up).
And if it is obviously ordered how it is required - then it should definately be pretty helpful.

Or wait......... Should I do it in the front end?

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 01:06:55

Did you also notice my asterisk "*" in the query?
Aside using TOP operator, you also must decide which columns to fetch.

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-19 : 01:11:55

Yip I did notice the asterisk - It is like using any top operator in a query just the variable in () Thats easy to understand. What i really want to know though - is will the query be treated like a Dynamic query or rather like any other query that uses a variable.

If it is not a dynamic query then I don't see why it is "bad practice".

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 01:20:27

It is not a bad practice to use this in SQL Server 2005.
There is a reason why MS included the operator, right?
It is treated as any other query with variable.

It was to OP (dnf999) I was referring to with the asterisk.

Madhivanan was making a point with the ORDER BY clause. If no ORDER BY clause is present, the TOP (x) records are not guaranteed to be the same for every run.

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-19 : 01:30:53

Thanks for that Peso - Thats what I thought.

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-19 : 22:51:42

quote:
Originally posted by ditch

Yip I did notice the asterisk - It is like using any top operator in a query just the variable in () Thats easy to understand. What i really want to know though - is will the query be treated like a Dynamic query or rather like any other query that uses a variable.

If it is not a dynamic query then I don't see why it is "bad practice".

Duane.

What I meant is Select Top @var wont work in sql server 2000 until you use Dynamic sql but Top @var is possible in sql server 2005 without the need of Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-19 : 22:54:35

<<
Or wait......... Should I do it in the front end?
>>

Yes. If you want to show data in list box set the sorted property to true then you dont need order by

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources