存储过程如下
if object_id ('MoreCopr_Edit') is not null
drop proc MoreCopr_Edit
GO
create proc [dbo].[MoreCopr_Edit]
@PropertyID Nvarchar(1000) , @PropertyNo Nvarchar(100),@PropertyName Nvarchar(100),
@PropertyTypeID Int,@TakingDate DateTime,@DepartID Int,@LandOrigin Int,@PowerNature Int
as begin
begin tran--更新(开始)
exec('
update NEW_PropertyNoTable set PropertyName='+@PropertyName+',PropertyTypeID='+@PropertyTypeID+',TakingDate='+@TakingDate+',DepartID='+@DepartID+',LandOrigin='+@LandOrigin+',
PowerNature='+@PowerNature+'where '+@PropertyID )
if @@error<>0
begin
rollback tran --操作失败,则事务回滚
select 2--'修改失败!'
return
end
--更新(结束)commit tran
select 3--'修改成功!'
end
执行存储过程:DECLARE @return_value intEXEC @return_value = [dbo].[MoreCopr_Edit]
@PropertyID = N'propertyid in (6924,6923)',
@PropertyNo = N'J15-2-11',
@PropertyName = N'抽湿机22',
@PropertyTypeID = 1261,
@TakingDate = N'2008-09-02 12:12:12',
@DepartID = 24,
@LandOrigin = 0,
@PowerNature = 6SELECT 'Return Value' = @return_value
错误消息消息 102,级别 15,状态 1,第 2 行
'2' 附近有语法错误。
请各位指点迷津
if object_id ('MoreCopr_Edit') is not null
drop proc MoreCopr_Edit
GO
create proc [dbo].[MoreCopr_Edit]
@PropertyID Nvarchar(1000) , @PropertyNo Nvarchar(100),@PropertyName Nvarchar(100),
@PropertyTypeID Int,@TakingDate DateTime,@DepartID Int,@LandOrigin Int,@PowerNature Int
as begin
begin tran--更新(开始)
exec('
update NEW_PropertyNoTable set PropertyName='+@PropertyName+',PropertyTypeID='+@PropertyTypeID+',TakingDate='+@TakingDate+',DepartID='+@DepartID+',LandOrigin='+@LandOrigin+',
PowerNature='+@PowerNature+'where '+@PropertyID )
if @@error<>0
begin
rollback tran --操作失败,则事务回滚
select 2--'修改失败!'
return
end
--更新(结束)commit tran
select 3--'修改成功!'
end
执行存储过程:DECLARE @return_value intEXEC @return_value = [dbo].[MoreCopr_Edit]
@PropertyID = N'propertyid in (6924,6923)',
@PropertyNo = N'J15-2-11',
@PropertyName = N'抽湿机22',
@PropertyTypeID = 1261,
@TakingDate = N'2008-09-02 12:12:12',
@DepartID = 24,
@LandOrigin = 0,
@PowerNature = 6SELECT 'Return Value' = @return_value
错误消息消息 102,级别 15,状态 1,第 2 行
'2' 附近有语法错误。
请各位指点迷津
改为@PropertyID = N'propertyid in ('6924','6923')
SELECT 'Return Value' = @return_value
@TakingDate = N'2008-09-02 12:12:12',
这一块的“02”这里有问题我是想问,存储过程里,怎么给日期赋值
试一下这样赋值
set @TaskingDate = getdate()
drop proc MoreCopr_Edit
GO
create proc [dbo].[MoreCopr_Edit]
@PropertyID Nvarchar(1000) ,
@PropertyNo Nvarchar(100),
@PropertyName Nvarchar(100),
@PropertyTypeID Int,
@TakingDate DateTime,
@DepartID Int,
@LandOrigin Int,
@PowerNature Int
asbegin
begin tran --更新(开始)
exec('update NEW_PropertyNoTable set PropertyName='''+@PropertyName+''',
PropertyTypeID='+@PropertyTypeID+',TakingDate='''+@TakingDate+''',
DepartID='+@DepartID+',LandOrigin='+@LandOrigin+',
PowerNature='+@PowerNature+'
where '+@PropertyID )
if @@error <>0
begin
rollback tran --操作失败,则事务回滚
select 2--'修改失败!'
return
end
--更新(结束)
commit tran
select 3--'修改成功!'
end
试一下吧。顺便问一下,@PropertyID 是什么内容?
本来是PropertyID in (@PropertyID )
但是,传进去的值是字符串,然后sql语句就会变成 PropertyID in ('6924,6923')不知道怎么改,只好把@PropertyID这个变量改为nvarchar型的,直接传条件进去了
PropertyTypeID='+@PropertyTypeID+',TakingDate='''+@TakingDate+''',
DepartID='+@DepartID+',LandOrigin='+@LandOrigin+',
PowerNature='+@PowerNature+'
where '+@PropertyID )定义的字符串类型,在执行存储过程的时候变量要''
drop proc MoreCopr_Edit
GO
create proc [dbo].[MoreCopr_Edit]
@PropertyID Nvarchar(1000) ,
@PropertyNo Nvarchar(100),
@PropertyName Nvarchar(100),
@PropertyTypeID Int,
@TakingDate DateTime,
@DepartID Int,
@LandOrigin Int,
@PowerNature Int
asbegin
begin tran --更新(开始)
exec('update NEW_PropertyNoTable set PropertyName='''+@PropertyName+''',
PropertyTypeID='+@PropertyTypeID+',TakingDate='''+@TakingDate+''',
DepartID='+@DepartID+',LandOrigin='+@LandOrigin+',
PowerNature='+@PowerNature+'
where '+@PropertyID )
if @@error <>0
begin
rollback tran --操作失败,则事务回滚
select 2--'修改失败!'
return
end
--更新(结束)
commit tran
select 3--'修改成功!'
end
if object_id ('MoreCopr_Edit') is not null
drop proc MoreCopr_Edit
GO
create proc [dbo].[MoreCopr_Edit]
@PropertyID Nvarchar(1000) , @PropertyNo Nvarchar(100),@PropertyName Nvarchar(100),
@PropertyTypeID Int,@TakingDate DateTime,@DepartID Int,@LandOrigin Int,@PowerNature Int
as begin
begin tran --更新(开始)
declare @sql varchar(8000)
set @sql='update NEW_PropertyNoTable set PropertyName='''+@PropertyName+''',PropertyTypeID='+ltrim(@PropertyTypeID)
+',TakingDate='''+@TakingDate+''',DepartID='+ltrim(@DepartID)+',LandOrigin='+ltrim(@LandOrigin)+',
PowerNature='+ltrim(@PowerNature)+' where '+@PropertyID
exec(@sql)
if @@error <>0
begin
rollback tran --操作失败,则事务回滚
select 2--'修改失败!'
return
end
--更新(结束)
commit tran
select 3--'修改成功!'
end
--拼接动态SQL时注意:字符串类型要加引号,数值类型要转换为字符串类型。
@TakingDate = N'2008-09-02 12:12:12',
这一块的“02”这里有问题 我是想问,存储过程里,怎么给日期赋值
-----------------------------------------------------因为@TakingDate是 DateTime,所以赋值时应该去年前面的N试试
即:@TakingDate = '2008-09-02 12:12:12',
即:@TakingDate = Convert(DateTime,'2008-09-02 12:12:12')
create proc [dbo].[MoreCopr_Edit]
@PropertyID Nvarchar(1000) , @PropertyNo Nvarchar(100),@PropertyName Nvarchar(100),
@PropertyTypeID Int,@TakingDate DateTime,@DepartID Int,@LandOrigin Int,@PowerNature Int
as begin
begin tran --更新(开始)
declare @sql varchar(8000)
set @sql='update NEW_PropertyNoTable set PropertyName='''+@PropertyName+''',PropertyTypeID='+ltrim(@PropertyTypeID)
+',TakingDate='''+cast(@TakingDate as varchar(50)+''',DepartID='+ltrim(@DepartID)+',LandOrigin='+ltrim(@LandOrigin)+',
PowerNature='+ltrim(@PowerNature)+' where '+@PropertyID
exec(@sql)
if @@error <>0
begin
rollback tran --操作失败,则事务回滚
select 2--'修改失败!'
return
end
--更新(结束)
commit tran
select 3--'修改成功!'
end
为什么我用 ltrim(@PropertyTypeID) 这个的时候会报错的?