USE [book]
GO
/****** Object: StoredProcedure [dbo].[p_book_list] Script Date: 06/08/2011 08:49:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p_book_list]
@name int,
@book_name nvarchar(20),
@book_id bigint,
@book_author nvarchar(20),
@book_press nvarchar(30),
@book_summary nvarchar(100),
@book_number int,
@book_price int,
@book_ispn bigint,
@book_pale nvarchar(20),
@book_address nvarchar(50),
@book_classid int,
@book_lend int
as
declare @sql nvarchar(4000);
if @name=0
begin
set @sql='update book_list set book_id='+@book_id+',book_name='+@book_name+',book_author='+@book_author+',book_summary='+@book_summary+',book_press='+@book_press+',book_number='+@book_number+',book_ispn='+@book_ispn+',book_classid='+@book_classid+',book_pale='+@book_pale+',book_price='+@book_price +' where book_id='+@name
end
else
begin
set @sql='update book_list set book_id='+@book_id+',book_name='+@book_name+',book_author='+@book_author+',book_summary='+@book_summary+',book_press='+@book_press+',book_number='+@book_number+',book_ispn='+@book_ispn+',book_classid='+@book_classid+',book_pale='+@book_pale+',book_price='+@book_price +' where book_id='+@name
end
print @sql
编译没有问题,使用的时候提示:
消息 8114,级别 16,状态 5,过程 p_book_list,第 19 行
从数据类型 varchar 转换为 bigint 时出错。
GO
/****** Object: StoredProcedure [dbo].[p_book_list] Script Date: 06/08/2011 08:49:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p_book_list]
@name int,
@book_name nvarchar(20),
@book_id bigint,
@book_author nvarchar(20),
@book_press nvarchar(30),
@book_summary nvarchar(100),
@book_number int,
@book_price int,
@book_ispn bigint,
@book_pale nvarchar(20),
@book_address nvarchar(50),
@book_classid int,
@book_lend int
as
declare @sql nvarchar(4000);
if @name=0
begin
set @sql='update book_list set book_id='+@book_id+',book_name='+@book_name+',book_author='+@book_author+',book_summary='+@book_summary+',book_press='+@book_press+',book_number='+@book_number+',book_ispn='+@book_ispn+',book_classid='+@book_classid+',book_pale='+@book_pale+',book_price='+@book_price +' where book_id='+@name
end
else
begin
set @sql='update book_list set book_id='+@book_id+',book_name='+@book_name+',book_author='+@book_author+',book_summary='+@book_summary+',book_press='+@book_press+',book_number='+@book_number+',book_ispn='+@book_ispn+',book_classid='+@book_classid+',book_pale='+@book_pale+',book_price='+@book_price +' where book_id='+@name
end
print @sql
编译没有问题,使用的时候提示:
消息 8114,级别 16,状态 5,过程 p_book_list,第 19 行
从数据类型 varchar 转换为 bigint 时出错。
-->
book_id='+cast(@book_id as varchar(50))+'其他自己类似改
book_name='+@book_name+',
--〉
book_name='''+@book_name+''',
-->
book_id='+ltrim(@book_id)+'
GO
/****** Object: StoredProcedure [dbo].[p_book_list] Script Date: 06/08/2011 08:49:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p_book_list]
@name int,
@book_name nvarchar(20),
@book_id nvarchar(20), ----这里改了
@book_author nvarchar(20),
@book_press nvarchar(30),
@book_summary nvarchar(100),
@book_number int,
@book_price int,
@book_ispn bigint,
@book_pale nvarchar(20),
@book_address nvarchar(50),
@book_classid int,
@book_lend int
as
declare @sql nvarchar(4000);
if @name=0
begin
set @sql='update book_list set book_id=convert(bigint,'+@book_id+'),book_name='+@book_name+',book_author='+@book_author+',book_summary='+@book_summary+',book_press='+@book_press+',book_number='+@book_number+',book_ispn='+@book_ispn+',book_classid='+@book_classid+',book_pale='+@book_pale+',book_price='+@book_price +' where book_id='+convert(varchar(20),@name)
end
else
begin
set @sql='update book_list set book_id=convert(bigint,'+@book_id+'),book_name='+@book_name+',book_author='+@book_author+',book_summary='+@book_summary+',book_press='+@book_press+',book_number='+@book_number+',book_ispn='+@book_ispn+',book_classid='+@book_classid+',book_pale='+@book_pale+',book_price='+@book_price +' where book_id='+convert(varchar(20),@name)
end
print @sql
像上面那样 cast 一下就可以了
book_name='+QUOTENAME(@book_name,'''')
book_id='+CONVERT(NVARCHAR(20),@book_id)