如果我的商店程序中有else语句怎么办？ [英] How do I do if else statement in my store procedure?

问题描述

这是商店程序的一部分。 
 
  - 从@MyTable中选择*; 
 
声明@seed int =（从@MyTable中选择答案，其中IsSeed = 1）; 
声明@drawDown int =（从@MyTable中选择sum（Answer），其中IsSeed = 0）; 
声明@ansToCheck nvarchar（max）=（从Answers ans中选择ans.answer，其中ans.ProjectID = @projectId和
 ans.QuestionID in（从Questions中选择ID）（ParentID = @ parentQuestionId或ID = @parentQuestionId）和Day = @providedAnsDay））; 
 
选择
 @projectId作为ProjectId，
 isnull（@ parentQuestionId，-1）作为ParentQuestionId，
 @providedQuestionId作为ProvideQuestionId，
 isnull（@seed ，0）作为种子，
 isnull（@drawDown，0）为Expended，
 isnull（@seed，0） -  isnull（@drawDown，0）为剩余，
 @ansToCheck为AnsToCheck ; 
 
 END 
 
 
如果'Remaining'在存储过程返回之前小于或等于0，我需要运行delete语句。我该怎么做 ？ 

我的尝试：



我知道如何使用if和else块但它不会让我放入select语句。



如何实现这一点。

解决方案

  SELECT  
  @ projectId   as  ProjectId，
 isnull（ @ parentQuestionId ， -  1） as  ParentQuestionId，
  @ providedQuestionId   as  ProvideQuestionId，
 isnull（ @ seed ， 0 ） as 种子，
 isnull（ @drawDown ， 0 ） as  Expended，
 isnull（ @ seed ， 0 ） -  isnull（ @ drawDown ，  0 ） as 剩余，
  @ ansToCheck   as  AnsToCheck ; 
 
 如果 isnull（ @ seed ， 0 ） -  isnull（ @ drawDown ， 0 ）< =  0  
  BEGIN  
 ... 
  END ; 

This is part of a store procedure. 

	--select * from @MyTable;

	declare @seed int = (select Answer from @MyTable where IsSeed = 1);
	declare @drawDown int = (select sum(Answer) from @MyTable where IsSeed = 0);
	declare @ansToCheck nvarchar(max) = (select ans.answer from Answers ans where ans.ProjectID = @projectId and 	
										ans.QuestionID in (select ID from Questions where (ParentID = @parentQuestionId or ID = @parentQuestionId) and Day = @providedAnsDay));

	select 
		@projectId as ProjectId, 
		isnull(@parentQuestionId, -1) as ParentQuestionId, 
		@providedQuestionId as ProvidedQuestionId,
		isnull(@seed, 0) as Seed, 
		isnull(@drawDown, 0) as Expended, 
		isnull(@seed, 0) - isnull(@drawDown, 0) as Remaining,
		@ansToCheck as AnsToCheck;

END


I need to run delete statement if 'Remaining' is less than or equals to 0 before store procedure return. How do i do it ? 

What I have tried:

I know how to use if and else block but It will not let me put in select statement.

How do I achieve this.

解决方案

SELECT
    @projectId as ProjectId, 
    isnull(@parentQuestionId, -1) as ParentQuestionId, 
    @providedQuestionId as ProvidedQuestionId,
    isnull(@seed, 0) as Seed, 
    isnull(@drawDown, 0) as Expended, 
    isnull(@seed, 0) - isnull(@drawDown, 0) as Remaining,
    @ansToCheck as AnsToCheck;

If isnull(@seed, 0) - isnull(@drawDown, 0) <= 0
BEGIN
    ...
END;

这篇关于如果我的商店程序中有else语句怎么办？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！