一个存储过程称的条件判断问题!CREATE PROCEDURE [dbo].[s_table_condition](
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100),
@o_flg int output
)
AS
BEGIN SET NOCOUNT ON; /*if(EXISTS @condition) 这里我就想如果传进来的@condition 这个参数为空或者没传,就执行else语句 否则执行if语句
{
begin
select @field from @table_name where @condition
end
}
else
{
begin
select @field from @table_name
end
}*/
END
GO请各位前辈指正这个存储过程应该怎么写!
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100),
@o_flg int output
)
AS
BEGIN SET NOCOUNT ON; /*if(EXISTS @condition) 这里我就想如果传进来的@condition 这个参数为空或者没传,就执行else语句 否则执行if语句
{
begin
select @field from @table_name where @condition
end
}
else
{
begin
select @field from @table_name
end
}*/
END
GO请各位前辈指正这个存储过程应该怎么写!
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100),
@o_flg int output
)
AS
BEGIN SET NOCOUNT ON; if(@condition='') --这里我就想如果传进来的@condition 这个参数为空或者没传,就执行-- --else语句 否则执行if语句
begin
select @field from @table_name where @condition
end
else
begin
select @field from @table_name
end
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100),
@o_flg int output
)
AS
BEGIN
IF @condition IS NULL
BEGIN
--为空的操作
END
ELSE
BEGIN
--不为空的操作
END
END
顺便说一下,这里可以加个默认值防止为空
exec ('select '+@field+' from '+@table_name+' where '+isnull(@condition,'1=1'))
declare @sql varchar(8000)set @sql='select '+@field+' from '+@table_name+' where '+isnull(@condition,'1=1')exec (@sql)
CREATE PROCEDURE [dbo].[s_table_condition](
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100),
@o_flg int output
)
AS
BEGIN SET NOCOUNT ON;
exec ('select '+@field+' from '+@table_name+' where '+isnull(@condition,'1=1'))
END
GO
告诉我 exec ('select '+@field+' from '+@table_name+' where '+isnull(@condition,'1=1'))
这行有语法错误
以下是我写的存储过程CREATE PROCEDURE [dbo].[s_table_condition](
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
declare @sql varchar(8000)
set @sql='select'+@field+' from'+@table_name+' where'+isnull(@condition,'1=1')
exec (@sql)
END
GO
检查语法成功!
但是我执行的时候
declare @field nvarchar(100)
declare @table_name nvarchar(100)
declare @condition nvarchar(100)
exec s_table_condition @field='expr,expr_name',@table_name='sys_price_expr',@condition=''就就报错:
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
后来我改成
set @sql='select '+@field+' from'+@table_name+' where '+isnull(@condition,'1=1')但是还是报'where' 附近有语法错误。 这个问题您在帮我看看!
应该变成select * from tableName 没有Where才对!
declare @table_name nvarchar(100)
declare @condition nvarchar(100)
exec s_table_condition @field='expr,expr_name',@table_name='sys_price_expr',@condition=null或者改一下存储过程,加个条件的判断set
@condition=case
when len(isnull(@condition,''))=0
then null
else @condition
end
set @sql= 'select '+@field+' from '+@table_name+' where '+isnull(@condition,'1=1')
CREATE PROCEDURE [dbo].[s_table_condition](
@field nvarchar(100),
@table_name nvarchar(100),
@condition nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
declare @sql varchar(8000)@condition=case
when len(isnull(@condition,''))=0
then null
else @condition
endset @sql='select '+@field+' from '+@table_name+' where '+isnull(@condition,'1=1')exec (@sql)
END
GO报错啊!
'@condition'附近有语法错误。
必须声明变量'@sql'。
必须声明变量'@sql'。