Select Top @parm * : issue - 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)

Select Top @parm * : issue

Author

Topic

KenA
Starting Member

28 Posts

Posted - 2005-12-28 : 12:31:39

In a procedure like:

===========================================
create procedure dbo.[sp_SelectSomething]
(
@NumReg int
)
as
begin

select top @NumReg * from Table1

end
============================================

I cannot use the @NumReg in the select statement.

How could I fix it?

»»» Ken.A

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 13:04:40

Here's one way:

set rowcount @NumReg
select <explicitColumnList> from Table1
set rowcount 0

Be One with the Optimizer
TG

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-12-28 : 13:20:37

try this
alter procedure dbo.[sp_SelectSomething]
(
@NumReg int
)
as
begin
declare @sql nvarchar(1000)
set @sql = 'select top ' + convert(varchar,@NumReg) + ' * from table1'
execute sp_executesql @sql

end

KenA
Starting Member

28 Posts

Posted - 2005-12-28 : 17:24:43

That´s good ... thanks :-)

»»» Ken.A

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 01:13:22

TG's approach avoids Dynamic SQL. Make use of it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources