How do I do if else statement in my store procedure?

Question

0.00/5 (No votes)

See more:

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.

Posted 2-Feb-18 3:30am

istudent

Updated 2-Feb-18 3:47am

Add a Solution

Comments

F-ES Sitecore 2-Feb-18 9:32am

You should avoid putting business logic inside stored procedures. The code that calls this procedure should decide if something needs deleting and if it does it can instigate the delete statement.

istudent 2-Feb-18 10:04am

I think about it sir and try not to do this from store procedure. It is due to implementation problem. This is something say, if there is some data for Monday Filed in (page 1) and Tuesday filed in (page2). But someone in future decided to edit Data for Monday to 0 then Tuesday Field should not be shown up in Page 2.

RedDk 2-Feb-18 14:23pm

This looks like pseudo code. Clean it up to show TSQL and we're one step further down the path to figuring out what to do next.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-02-02T03:47:00

SQL

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;