Create proc [dbo].[Proc_GetMemberInfoBy]
(
@Member_id int,
@Member_name varchar(50)
as
declare @sql nvarchar(1024),@M_Id nvarchar(10)
set @M_Id=convert(nvarchar(10),@Member_id)
set @sql='select * from Member where 1=1'
if convert(int,@M_Id) >0
begin
set @sql=@sql+' and Member_id='+@Member_id
end
if @Member_name <>''
begin
set @sql=@sql+' and @Member_name='''+@Member_name+''''
end
go
Create proc [dbo].[Proc_GetMemberInfoBy]
@Member_id int,
@Member_name varchar(50)
as
declare @sql nvarchar(1024),@M_Id nvarchar(10)
set @M_Id=convert(nvarchar(10),@Member_id)
set @sql='select * from Member where 1=1'
if convert(int,@M_Id) >0
begin
set @sql=@sql+' and Member_id='+@Member_id
end
if @Member_name <>''
begin
set @sql=@sql+' and @Member_name='''+@Member_name+''''
end
go
Create proc [dbo].[Proc_GetMemberInfoBy]
(
这句话没有看懂
消息 245,级别 16,状态 1,过程 Proc_GetMemberInfoBy,第 12 行
在将 nvarchar 值 'select * from Member where 1=1 and Member_id=' 转换成数据类型 int 时失败。
这样就OK.
@Member_id int,
@Member_name varchar(50)
as
declare @sql nvarchar(1024),@M_Id nvarchar(10)
set @M_Id=convert(nvarchar(10),@Member_id)
set @sql='select * from Member where 1=1'
if convert(int,@M_Id) >0
begin
set @sql=@sql+' and Member_id='+convert(nvarchar(20),@Member_id)
end
if @Member_name <>''
begin
set @sql=@sql+' and @Member_name='''+@Member_name+''''
end
go
Create proc [dbo].[Proc_GetMemberInfoBy]
@Member_id int,
@Member_name varchar(50)
as
declare @sql nvarchar(1024),@M_Id nvarchar(10)
set @M_Id=convert(nvarchar(10),@Member_id)
set @sql='select * from Member where 1=1'
if convert(int,@M_Id) >0
begin
set @sql=@sql+' and Member_id='+convert(nvarchar(20),@Member_id)
end
if @Member_name <>''
begin
set @sql=@sql+' and @Member_name='''+@Member_name+''''
end
go
为什么要把这个@Member_id int,
转化成M_id
不直接用 if @Member_id >0
@Member_id int,
@Member_name varchar(50)
as
declare @sql nvarchar(1024),@M_Id nvarchar(10)
set @M_Id=convert(nvarchar(10),@Member_id)
set @sql='select * from Member where 1=1'
if convert(int,@M_Id) >0
begin
set @sql=@sql+' and Member_id='+@Member_id
end
if @Member_name <>''
begin
set @sql=@sql+' and @Member_name='''+@Member_name+''''
end
go@Member_name改成Member_name
Create proc [dbo].[Proc_GetMemberInfoBy]
(
@Member_id int,
@Member_name varchar(50)
)--要么前面那个不打,要么把后面这个打上
as
declare @sql nvarchar(1024),@M_Id nvarchar(10)
set @M_Id=convert(nvarchar(10),@Member_id)
set @sql='select * from Member where 1=1'
if convert(int,@M_Id) >0
begin
set @sql=@sql+' and Member_id='+LTRIM(@Member_id)--这个是INT型要转成字符型
end
if @Member_name <>''
begin
set @sql=@sql+' and @Member_name='''+@Member_name+''''
end
go