CREATE PROCEDURE mn
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top @sum from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO执行:
USE ASPNET --数据库
GO
exec mn @n=3,@m=7结果显示:@sum有语法错误!
请问大家这个怎么解决,问题出在哪!
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top @sum from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO执行:
USE ASPNET --数据库
GO
exec mn @n=3,@m=7结果显示:@sum有语法错误!
请问大家这个怎么解决,问题出在哪!
CREATE PROCEDURE mn
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '+convert(varchar,@sum)+' from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO
这样?
CREATE PROCEDURE mn
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
select @sum=(case when count(*)>=@sum then 1 else 0 end) from score
--set @sql='select top '+convert(varchar,@sum)+' * from score'
--exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO
CREATE PROCEDURE mn
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '+@sum+' from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '+convert(varchar,@sum)+' from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO原因:你定义的@sql是VACHAR型的,那你'select top @sum from score' 这个里面的都要是varcahr型的,所以需要转化一下!
@n int,
@m int
asdeclare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '+convert(varchar,@m)+ ' * from score where id not in(select top ' +convert(varchar,@n)+' id from score order by id asc) order by id asc'
--少了*,这里假设有id列
begin tran
print @sql
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO
CREATE PROCEDURE mn
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '+cast(@sum as varchar(100))+' * from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO
1.记得加星号
2.top后面要有空格
3.@sum要进行类型转换
-----------------------------------
http://www.jiemengwu.com/ 解梦屋 http://www.phpzy.com/php/ 绿色php资源
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '+cast(@sum as varchar(100))+' * from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GO1.记得加星号
2.top后面要有空格
3.@sum要进行类型转换8楼不错,支持.
但是楼主想这样写也不是不可以,你可以参考下sp_executesql()的用法
建议楼住不要直接用exec,最好用sp_executesql()来执行动态查询http://bbs.51cto.com/thread-798970-1.html
这是我做过的一篇测试,你可以看下
@n int,
@m int
as
begin tran
declare @sql varchar(100),@sum int
set @sum=@m-@n+1
set @sql='select top '''+Convert(varchar,@sum)+''' from score'
exec (@sql)
if(@sum>0)
commit tran
else
rollback tran
GOUSE ASPNET --数据库
GO
exec mn @n=3,@m=7
(
@n int,
@m int
)
as
Begin tran
declare @sum int,@sql varchar(1000)
set @sum = @m - @n + 1
set @sql ='select top '+ CAST(@sum as varchar(10))+' * from score'
exec (@sql)
print @sql
if @sum < 1
rollback tran
else
commit tran
exec mn 3,7