create procedure usp
@n int
AS
begin
select top @n from a
endMsg 102, Level 15, State 1, Procedure usp, Line 5
Incorrect syntax near '@n'.怎么改,谢谢
@n int
AS
begin
select top @n from a
endMsg 102, Level 15, State 1, Procedure usp, Line 5
Incorrect syntax near '@n'.怎么改,谢谢
1,sql2k中, top 后只能跟常量
2,没有写字段列表.
set @sql = 'select top ' + cast(@n as varchar(10))+' from a'
exec(@sql)
@n int
AS
begin
exec('select top ' + cast(@n as varchar(10) + ' * from a ')
end
go
set @sql = 'select top ' + cast(@n as varchar(10))+' * from a'
exec(@sql)*可换为字段列表
@n int
AS exec('select top '+@n+' * from a')
go
-----------------------------------
直接在exec('')里写的话,不可以用函数如果要用的话,需要放到变量里
declare @sql varchar(100)
set @sql='select top '+rtrim(@n)+' * from a'
exec(@sql)
------------------------------------------------------------------------------------
感谢这位朋友又提醒一遍,老是忘.
@ref_id varchar(20),
@entrance_ciu char(5),
@car_type_code char(5),
@parking_type char(1),
@card_type_code int,
@access_id varchar(20),
@entrance_time datetime,
@Re nvarchar(100),
@last_update datetime,
@last_update_by varchar(20)
AS
declare @device_type_code char(5)
begin
EXEC ('select top 1 @device_type_code=device_type_code * from (select top ' + @card_type_code + ' * from DEVICE order by device_type_code asc)DEVICE order by device_type_code desc') insert into ENTRANCE_LOG (ref_id,entrance_ciu,car_type_code,parking_type,device_type_code,access_id,entrance_time,Re,last_update,last_update_by) values (@ref_id,@entrance_ciu,@car_type_code,@parking_type,@device_type_code,@access_id,@entrance_time,@Re,@last_update,@last_update_by)
end
存储通过,但是执行有问题
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
Msg 515, Level 16, State 2, Procedure uspcarAdd, Line 16
Cannot insert the value NULL into column 'device_type_code', table 'ShineUnion.dbo.ENTRANCE_LOG'; column does not allow nulls. INSERT fails.好像还是exec里面的问题
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@device_type_code".
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
@ref_id varchar(20),
@entrance_ciu char(5),
@car_type_code char(5),
@parking_type char(1),
@card_type_code int,
@access_id varchar(20),
@entrance_time datetime,
@Re nvarchar(100),
@last_update datetime,
@last_update_by nvarchar(20)
AS declare @device_type_code char(5)begin
declare @sql nvarchar(8000)
set @sql='select top 1 @device_type_code=device_type_code * from (select top ' + @card_type_code + ' * from DEVICE order by device_type_code asc)DEVICE order by device_type_code desc'
EXEC sp_executesql @sql,N'@device_type_code char(5) output',@device_type_code output
insert into ENTRANCE_LOG (ref_id,entrance_ciu,car_type_code,parking_type,device_type_code,access_id,entrance_time,Re,last_update,last_update_by) values (@ref_id,@entrance_ciu,@car_type_code,@parking_type,@device_type_code,@access_id,@entrance_time,@Re,@last_update,@last_update_by)
end--这样试一下
运行后出错如下:
Msg 245, Level 16, State 1, Procedure uspcarAdd, Line 18
Conversion failed when converting the varchar value 'select top 1 @device_type_code=device_type_code * from (select top ' to data type int.
改为:
set @sql='select top 1 @device_type_code=device_type_code ,* from (select top ' + cast(@card_type_code as varchar(10)) + ' * from DEVICE order by device_type_code asc)DEVICE order by device_type_code desc'
改为:
declare @sql nvarchar(4000)
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
请楼主再测试一下,空值错误先不用管,看看EXEC有没有问题.
create procedure uspcarAdd
@ref_id varchar(20) = '',
@entrance_ciu char(5) = '',
@car_type_code char(5) = '',
@parking_type char(1) = '',
@card_type_code int = 0,
@access_id varchar(20) = '',
@entrance_time datetime,
@Re nvarchar(100) = N'',
@last_update datetime,
@last_update_by nvarchar(20) = N''
AS declare @device_type_code char(5)begin
declare @sql nvarchar(4000)
set @sql=N'select top 1 @device_type_code=device_type_code from (select top ' +
cast(@card_type_code as nvarchar(20)) +
N' * from DEVICE order by device_type_code asc) a '
EXEC sp_executesql @sql,N'@device_type_code char(5) output',@device_type_code output
insert into ENTRANCE_LOG (ref_id,entrance_ciu,car_type_code,parking_type,device_type_code,access_id,entrance_time,Re,last_update,last_update_by) values (@ref_id,@entrance_ciu,@car_type_code,@parking_type,@device_type_code,@access_id,@entrance_time,@Re,@last_update,@last_update_by)
end
@ref_id varchar(20),
@entrance_ciu char(5),
@car_type_code char(5),
@parking_type char(1),
@card_type_code int,
@access_id varchar(20),
@entrance_time datetime,
@Re nvarchar(100),
@last_update datetime,
@last_update_by nvarchar(20)
AS declare @device_type_code char(5)begin
declare @sql nvarchar(4000)
set @sql='select top 1 @device_type_code=device_type_code * from (select top ' + cast(@card_type_code as nvarchar)+ ' * from DEVICE order by device_type_code asc)DEVICE order by device_type_code desc'
EXEC sp_executesql @sql,N'@device_type_code char(5) output',@device_type_code output
insert into ENTRANCE_LOG (ref_id,entrance_ciu,car_type_code,parking_type,device_type_code,access_id,entrance_time,Re,last_update,last_update_by) values (@ref_id,@entrance_ciu,@car_type_code,@parking_type,@device_type_code,@access_id,@entrance_time,@Re,@last_update,@last_update_by)
end
对了对了 xeqtr1982(Visual C# .NET) ( ) 信誉:100
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
多谢各位
1.'select top 1 @device_type_code=device_type_code * from ....
如果有了@device_type_code=device_type_code赋值语句,则不能再使用*来选择其它列.应为:
select top 1 @device_type_code=device_type_code from....
2.既然select top 1 @device_type_code=device_type_code from...只返回变量值,当然就不能order by device_type_code desc,因为没有device_type_code这个列.