在sql server存储过程中存在IF条件 [英] IF exists condition in sql server stored procedure

问题描述

大家好，

我正在编写一个存储过程并希望实现if条件但是在if'错误消息附近给出语法错误不正确，任何帮助都表示赞赏。

以下是我的存储过程：

  ALTER  程序 [dbo]。[USP_SearchLeadQueue] 
 
  as  
 开始 
 
 选择 wladdlead.WishListLeadID，wlcompinfo.CompanyName，
（选择  top   1  wldminfo.DMName 来自 WishListDMInfo wldminfo 其中 wldminfo.WishListLeadID = wladdlead.WishListLeadID） as  DMName，
（选择  top   1  wlcomdetail.PhoneNumber 来自 WishListCompInfoDetail wlcomdetail 其中 wlcomdetail。 CompanyInfoID = wlcompinfo.CompanyInfoID） as  PhoneNumber 
 
，如果 存在（ SELECT  DateOfLastAction 来自 WishListCallTransaction wlcall  WHERE  wlcall.WishListLeadID = wladdlead.WishListLeadID）
  SELECT   top   1  DateOfLastAction  FROM  WishListCallTransaction wlcall 
  inner   join  WishListAddLeadInfo wladdlead 
  on  wlcall.WishListLeadID = wladdlead.WishListLeadID  order   by  wlcall.DateOfLastAction  desc  
  else  选择 CreatedDate 来自 WishListAddLeadInfo wladdlead  where  wladdlead.WishListLeadID = wladdlead.WishListLeadID 
 
 
，（ select   top   1  tz.TimeZone 来自 TimeZone tz 
 内部  join  WishListCompInfoDetail wlcominfo  on  wlcominfo。< span class =code-keyword> Time  = tz.TimeZoneId） as  TimeZone 
，（（选择  top   1  LeadStatus.Status 来自 WishListCallTransaction wlcall 
  inner   join  LeadStatus  on  LeadStatus.LeadStatusId = wlcall.Status 
 其中 wlcall.WishListLeadID = wladdlead.WishListLeadID  order   by  wlcall.DateOfLastAction  desc ）） as 状态
 
，（（选择  top   1  LeadStatus.Status 来自 WishListCallTransaction wlcall 
  inner   join  LeadStatus   LeadStatus.LeadStatusId = wlcall.Status2 
 < span class =code-keyword>其中 wlcall.WishListLeadID = wladdlead.WishListLeadID  order   by  wlcall.DateOfLastAction  desc ）） as  Status2 
 
，（ select  AccountType.ShortName 来自用户用户
 内部  join  AccountType  on  AccountType.AccountTypeId = users.AccountTypeId 其中​​ wladdlead.apnameid = users.UserId） as  WhoOwnsIt 
，（选择 users.Name 来自用户其中 wladdlead.apnameid = users.UserId） as  AssociateName 
 
 来自 WishListCompInfo wlcompinfo 
  inner   join  WishListAddLeadInfo wladdlead  on  wladdlead.WishListLeadID = wlcompinfo.WishListLeadID 
  inner   join  WishListCallTransaction wlcall  on  wladdlead.WishListLeadID = wlcall.WishListLeadID 
 和 wladdlead.Active = 1 和 wlcall.Status = 2 
 
 结束 

解决方案

使用CASE WHEN Exist（...）THEN（SELECT ...）ELSE（SELECT ...）END而不是IF 。 IF不适用于您的情况。



..btw您的查询很差，请尝试让经验丰富的人帮助您。

<在if语句之前缺少blockquote>，。

Hi All,
I am writing a stored procedure and want to implement if condition but is is giving "Incorrect syntax error near 'if'" error message, any help is appreciated.
Below is my stored procedure :

ALTER procedure [dbo].[USP_SearchLeadQueue]  
  
as   
 begin  
 
 select wladdlead.WishListLeadID, wlcompinfo.CompanyName,
(select top 1 wldminfo.DMName from WishListDMInfo wldminfo where wldminfo.WishListLeadID = wladdlead.WishListLeadID) as DMName,
(select top 1 wlcomdetail.PhoneNumber from WishListCompInfoDetail wlcomdetail where wlcomdetail.CompanyInfoID = wlcompinfo.CompanyInfoID) as PhoneNumber

,If exists(SELECT DateOfLastAction from WishListCallTransaction wlcall WHERE wlcall.WishListLeadID=wladdlead.WishListLeadID)
 SELECT top 1 DateOfLastAction FROM WishListCallTransaction wlcall 
inner join WishListAddLeadInfo wladdlead
on  wlcall.WishListLeadID = wladdlead.WishListLeadID order by wlcall.DateOfLastAction desc
else select CreatedDate from WishListAddLeadInfo wladdlead where wladdlead.WishListLeadID=wladdlead.WishListLeadID 


,(select top 1 tz.TimeZone from TimeZone tz 
inner join WishListCompInfoDetail wlcominfo on wlcominfo.Time = tz.TimeZoneId) as TimeZone   
,((select top 1 LeadStatus.Status from WishListCallTransaction wlcall 
inner join LeadStatus on LeadStatus.LeadStatusId=wlcall.Status
where wlcall.WishListLeadID = wladdlead.WishListLeadID order by wlcall.DateOfLastAction desc)) as Status

,((select top 1 LeadStatus.Status from WishListCallTransaction wlcall 
inner join LeadStatus on LeadStatus.LeadStatusId=wlcall.Status2
where wlcall.WishListLeadID = wladdlead.WishListLeadID order by wlcall.DateOfLastAction desc)) as Status2

,(select AccountType.ShortName from Users users 
inner join AccountType on AccountType.AccountTypeId= users.AccountTypeId where wladdlead.apnameid=users.UserId)as WhoOwnsIt
,(select users.Name from Users users where wladdlead.apnameid=users.UserId)as AssociateName

from WishListCompInfo wlcompinfo 
inner join WishListAddLeadInfo wladdlead on wladdlead.WishListLeadID=wlcompinfo.WishListLeadID
inner join WishListCallTransaction wlcall on wladdlead.WishListLeadID = wlcall.WishListLeadID
and wladdlead.Active=1 and wlcall.Status=2

End

解决方案

Use CASE WHEN Exist(...) THEN (SELECT ...) ELSE (SELECT ...) END instead of IF. IF is not applied in your case.

..btw your query is poor, try to ask someone seasoned to help you.

"," is missing before If statement.

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