帮忙看一下这个存储过程哪里有错？？在线

[songle912]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[Song_getForHtml_sp]
	@m int,
	@n int
AS
BEGIN
	SET NOCOUNT ON;

select   top   @n-@m+1  songid 
  from   (select   top   @n    songid   from   song   order   by   songid asc)   a   
  order   by   songid   desc 


END
GO

调试通过不了，请问哪里出错了？如果把@n-@m-1和@n换成数字就没错了，怎么回事呢？

[dawugui]

/*
在TOP后面使用变量
(爱新觉罗.毓华  2008-01-02  广东深圳)
*/

--SQL SERVER 2005 的写法
use adventureworks
go

DECLARE @Percentage int
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name

/*
Name
----------------------
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
BB Ball Bearing
Bearing Ball
Bike Wash - Dissolver

(6 行受影响)
*/

-----------------------------------
--SQL SERVER 2000 的写法
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)

declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)

drop table a
/*
id          
----------- 
1
2
*/

[水族杰纶]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[Song_getForHtml_sp]
@m int,
@n int
AS
BEGIN
SET NOCOUNT ON;
declare @num
set @num=@n-@m+1
exec('select top '+ @num + 'songid
from (select top '+ @n + ' songid from song order by songid asc) a
order by songid desc ')

END
GO

[zlp321002]

--sql 2005 
create PROCEDURE [dbo].[Song_getForHtml_sp]
    @m int,
    @n int
AS
BEGIN
    SET NOCOUNT ON;

select   top  ( @n-@m+1 ) songid 
  from   (select   top (  @n )    songid   from   song   order   by   songid asc)   a   
  order   by   songid   desc 
END
GO
--sql 2000
alter PROCEDURE [dbo].[Song_getForHtml_sp]
    @m int,
    @n int
AS
BEGIN
    SET NOCOUNT ON;
    declare @sql nvarchar(2000)
    set @sql='
select   top  ( '+ltrim(@n)+'-'+ltrim(@m)+'+1 ) songid 
  from   (select   top (  '+ltrim(@n)+' )    songid   from   song   order   by   songid asc)   a   
  order   by   songid   desc '
  exec (@sql)
END
GO

[duanzhi1984]

TOP 后面不能跟参数.

系统无法判断TOP后面的值是否符合要求,因为TOP后面不能为负数..

[songle912]

真厉害，小弟还有两个问题，第一，percent的作用是什么呢？
第二，为什么要cast为char呢？不是要int的吗？

[duanzhi1984]

只有用动态语句

[dawugui]

[Quote=引用 5 楼 songle912 的回复:]
真厉害，小弟还有两个问题，第一，percent的作用是什么呢？
第二，为什么要cast为char呢？不是要int的吗？
[/Quote]
动态语句使用的是个串.

[水族杰纶]

SQL code动态sql语句基本语法
1 :普通SQL语句可以用Exec执行

eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL

eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格

当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名

declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错



declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确

3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num