CREATE PROCEDURE Select_IndividualSaleHouse_search
@user_ID int,
@Area_ID int
AS
declare @sql varchar(1000)
set @sql='select * from IndividualSaleHouse where user_ID='+@user_IDif (@Area_ID<>0)
begin
set @sql = @sql + ' and Area_ID=@Area_ID'
end exec (@sql )
GO
@user_ID int,
@Area_ID int
AS
declare @sql varchar(1000)
set @sql='select * from IndividualSaleHouse where user_ID='+@user_IDif (@Area_ID<>0)
begin
set @sql = @sql + ' and Area_ID=@Area_ID'
end exec (@sql )
GO
@user_ID int,
@Area_ID int
AS
declare @sql varchar(1000)
set @sql='select * from IndividualSaleHouse where user_ID=@user_ID 'if (@Area_ID<>0)
begin
set @sql = @sql + ' and Area_ID=@Area_ID'
end exec sp_executesql @sql,N'@user_ID int,@Area_ID int',@user_ID ,@Area_ID
GO
@user_ID int,
@Area_ID int
AS
declare @sql varchar(1000)
set @sql='select * from IndividualSaleHouse where user_ID='+cast(@user_ID as varchar(50))if (@Area_ID<>0)
begin
set @sql = @sql + ' and Area_ID='+cast(@Area_ID as varchar(50))
end exec (@sql)
GO
将 varchar 值 'select * from IndividualSaleHouse where user_ID=' 转换为数据类型为 int 的列时发生语法错误。
还是提出错误,错误提示和我开始的时候一样。To: ghostzxp(幽灵) ( )
错误提示是:过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。
execute sp_executesql
N'select * from IndividualSaleHouse where user_ID = @user_ID and Area_ID=@Area_ID ',
N'@user_ID int,@Area_ID int',
@user_ID = 1,@Area_ID=1
create table IndividualSaleHouse ([user_ID] int, Area_ID int,T varchar(20))
insert into IndividualSaleHouse select 1,1,'a'
union all select 2,2,'b'
union all select 3,3,'c' --存储过程Create PROCEDURE Select_IndividualSaleHouse_search
@user_ID int,
@Area_ID int
AS
declare @sql Nvarchar(1000)
set @sql='select * from IndividualSaleHouse where user_ID = @user_ID 'if (@Area_ID<>0)
begin
set @sql = @sql + ' and Area_ID=@Area_ID '
end execute sp_executesql
@sql,
N'@user_ID int,@Area_ID int',
@user_ID ,@Area_IDGO--执行
Select_IndividualSaleHouse_search 1,1
--结果
user_ID Area_ID T
----------- ----------- --------------------
1 1 a(所影响的行数为 1 行)--删除环境
drop table IndividualSaleHouse
drop proc Select_IndividualSaleHouse_search
错误提示是:过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。