…… where 1=1 and DepID = case when @s_Dep is not null then @s_Dep else DepID end GROUP BY ……
where 1=1
And @s_Dep is not null
and DepID=@s_Dep GROUP BY ……
表示没看懂..... where 后面的if 是干什么用的? 如果where后面多个条件, 应该使用 and 连接,并且条件返回的必须是bool值.
我写这个语句的意思是说当@s_Dep不为NULL时,再加上 "and DepID=@s_Dep"这句 就类似于 if( @s_Dep is not null ) { and DepID=@s_Dep }
能不能写成这样的? if( @s_Dep is not null ) { and DepID=@s_Dep }
sql = sql + " where 1=1"; if (s_Dep != "") { sql = sql + " and DepID=@s_Dep"; }大概就是这个意思,请问转成存储过程怎么写?
1楼撸大师的应该可以。 where 1=1 and DepID = case when @s_Dep is not null And @s_Dep != '' then @s_Dep else DepID end GROUP BY ……
这么写我想到了,我的意思是说,在存储过程里任何位置插入条件语句应该怎么写?比如再加上一个 if (s_StartTime != "") { sql = sql + " and datediff(day,@s_StartTime,WarList.WarDate)>=0"; }总不至还写成 and datediff(day,case when @s_StartTime is not null And @s_StartTime != '' then @s_StartTime else WarList.WarDate end,WarList.WarDate)>=0我想写成 if( @s_StartTime is not null And @s_StartTime != '') { and datediff(day,@s_StartTime,WarList.WarDate)>=0 } 类似这样的,请问在存储过程里应该怎么写?
那好像只能: sql = sql + ' where 1=1'; if (s_Dep is not null) { sql = sql + ' and DepID='+@s_Dep; } exec (@sql) 像你这样,然后再用exec (@sql)或者sp_exectesql来执行动态语句了。
f (@s_Dep is not null And @s_Dep != '') BEGIN and DepID=@s_Dep end 我这么写的,也出错
基本语法还没弄清楚啊,你要想根据条件动态改变SQL条件语句,必须要和前面或后面的SQL语句连接起来,组合成一条完整的语句,不是随便写个and就行了,像9楼这样sql = sql + ' and DepID='+@s_Dep;
where 1=1
and DepID = case when
@s_Dep is not null
then @s_Dep
else DepID end
GROUP BY ……
And @s_Dep is not null
and DepID=@s_Dep GROUP BY ……
就类似于
if( @s_Dep is not null )
{
and DepID=@s_Dep
}
if( @s_Dep is not null )
{
and DepID=@s_Dep
}
sql = sql + " where 1=1";
if (s_Dep != "")
{
sql = sql + " and DepID=@s_Dep";
}大概就是这个意思,请问转成存储过程怎么写?
and DepID = case when
@s_Dep is not null
And @s_Dep != ''
then @s_Dep
else DepID end
GROUP BY ……
这么写我想到了,我的意思是说,在存储过程里任何位置插入条件语句应该怎么写?比如再加上一个
if (s_StartTime != "")
{
sql = sql + " and datediff(day,@s_StartTime,WarList.WarDate)>=0";
}总不至还写成
and datediff(day,case when @s_StartTime is not null And @s_StartTime != '' then @s_StartTime else WarList.WarDate end,WarList.WarDate)>=0我想写成
if( @s_StartTime is not null And @s_StartTime != '')
{
and datediff(day,@s_StartTime,WarList.WarDate)>=0
}
类似这样的,请问在存储过程里应该怎么写?
sql = sql + ' where 1=1';
if (s_Dep is not null)
{
sql = sql + ' and DepID='+@s_Dep;
}
exec (@sql)
像你这样,然后再用exec (@sql)或者sp_exectesql来执行动态语句了。
BEGIN
and DepID=@s_Dep
end
我这么写的,也出错