如何使用sql中的存储过程中的条件 [英] how to use if condition in store procedure in sql

问题描述

这里我的问题是，当同一个客户ID状态为打开和关闭时，我只想显示已关闭状态，所以如果我正在尝试此商店程序，但它不能正常工作...





我的代码是....

  set   ANSI_NULLS   ON  
  set   QUOTED_IDENTIFIER   ON  
  GO  
  ALTER   proc  [Abileshop]。[GrdOpenClose] 
（
  @ customerid   varchar （ 50 ），
  @ out   varchar （ 100 ）=  NULL  输出 
）
  as  
 开始 
 声明  @ s   varchar  
 选择 @ s = status 来自 newcomp1 其中 customerid = @ customerid 
  if （@ s = < span class =code-string>'  closed'）
  set  @ out = '  NO' 
  else  
 选择 id，compliantid，priorty，status， convert （ varchar ，compdate， 103 ）' > CompliantDate' 来自 newcomp1 其中 customerid = @ customerid 
  end  

解决方案

使用类似的东西。

  set   ANSI_NULLS   ON  
  set   QUOTED_IDENTIFIER   ON  
  GO  
  ALTER   proc  [Abileshop]。[GrdOpenClose] 
（
  @ customerid   varchar （ 50 ），
  @ out   varchar （ 100 ）=  NULL  输出 
）
  as  
 开始 
 声明  @ s   varchar  
 选择 @ s =（选择状态来自 newcomp1 其中 customerid = @ customerid）
  if  @ = '  closed' 
  set   @ out  '  NO' 
  else  
  select  * 来自 newcomp1 其中 customerid = @ customerid 
 结束 

- SG

< blockquote>模仿你的查询，希望这会有效。

 选择 @s = count（*）来自 newcomp1 其中 customerid = @ customerid 和 status = ' 已关闭' 
  if （@ s = 0）
  begin  
  set  @ out = '  NO' 
 结束 
 
 选择 id，compliantid，priorty，status， convert （ varchar ，compdate， 103 ）'  C. ompliantDate' 来自 newcomp1 其中 customerid = @ customerid 和 status = ' 已关闭' 

Hi,
Here i my problem is, when the same customer id having status of 'open' and 'closed' i want to show closed status only, so if i'm trying this store procedure but it not working properly...


My code is....

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [Abileshop].[GrdOpenClose]
(
@customerid varchar(50),
@out varchar(100)= NULL output
)
as
begin
declare @s varchar
select @s=status from newcomp1 where customerid=@customerid
if(@s='closed')
set @out='NO'
else 
select id,compliantid,priorty,status,convert(varchar,compdate,103)'CompliantDate' from newcomp1 where customerid=@customerid
end

解决方案

Use something like this.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [Abileshop].[GrdOpenClose]
(
@customerid varchar(50),
@out varchar(100)= NULL output
)
as
begin
declare @s varchar
select @s= (select status from newcomp1 where customerid=@customerid)
if @='closed'
set @out'NO'
else
select * from newcomp1 where customerid=@customerid
end

- SG

Modefy your query like this hope this will work.

select @s=count(*) from newcomp1 where customerid=@customerid and status='closed'
if(@s=0)
  begin
      set @out='NO'
  end

select id,compliantid,priorty,status,convert(varchar,compdate,103)'CompliantDate' from newcomp1 where customerid=@customerid and status='closed'

这篇关于如何使用sql中的存储过程中的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！