I am facing error near END of while loop

Question

1.00/5 (1 vote)

See more:

use [demo]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION dbo.isdetain(@cursem tinyint,@bck1 tinyint,@bck2 tinyint,@bck3 tinyint,@bck4 tinyint,@bck5 tinyint,@bck6 tinyint,@bck7 tinyint,@bck8 tinyint,@bck9 tinyint,@bck10 tinyint)
returns tinyint
as  
begin 
	declare @isdetain bit
	declare @finalvar tinyint=@cursem-2
	declare @coun tinyint=0
	declare @bck varchar(5)='bck'+@finalvar
	set @isdetain=null 
		while @finalvar>0
		BEGIN
			set @coun+= cast(@bck as tinyint);
			set @finalvar--;
		END

	if @coun>=4
		set @isdetain=1;
	else
		set @isdetain=0;
	
	return @coun;
	end
GO

What I have tried:

The code above i have tried but i am facing error near END of while loop

Posted 4-Oct-19 1:03am

Member 10726045

Updated 4-Oct-19 1:09am

v2

Add a Solution

2 solutions

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)

phil.o · Answer 1 · 2019-10-04T01:09:00

Solution 2

--, in SQL, stands for the beginning of a comment line.
SQL does not support increment/decrement operator.
Instead of

SQL

set @finalvar--;

you have to write

SQL

set @finalvar = @finalvar - 1;

Posted 4-Oct-19 1:09am

phil.o

Updated 4-Oct-19 1:11am

v2

Comments

Aarti Meswania 7-Oct-19 2:04am

5ed! :)

phil.o 7-Oct-19 4:23am

Thanks :)

OriginalGriff · Answer 2 · 2019-10-04T01:09:00

Solution 1

SQL doesn't have a "--" operator - instead "--" indicates the start of a comment.
What you need is:

SQL

SET @Finalvar = @Finalvar - 1;

[edit]
But even with that fixed, the code won;t work: you will get a conversion error on yoru CAST:

SQL

set @coun+= cast(@bck as tinyint);

Because the value in @bck is not a number:

SQL

declare @bck varchar(5)='bck'+@finalvar

[/edit]

Posted 4-Oct-19 1:09am

OriginalGriff

Updated 4-Oct-19 1:12am

v3

Comments

Member 10726045 4-Oct-19 7:25am

I have a variable value from bck1,...bck10. and I need to count upto the value of finalvar... like if finalvar=5 then i will take sum of only from bck1,...,bck5

OriginalGriff 4-Oct-19 7:35am

Do you want to try explaining exactly what you are trying to achieve, rather than just a "shorthand" version which makes no sense whatsoever without the context of your project we have no information on?

Member 10726045 4-Oct-19 7:45am

Problem of shorthand operator has been solved but now the problem of converting varchar to tinyint has come in bck variable

Member 10726045 4-Oct-19 7:49am

upto the final variable i need to take the summation of 'bck' variable. for example if finalvar=6 then summation would be of bck1,bck2,bck3,bck4,bck5 and bck6

OriginalGriff 4-Oct-19 8:07am

You can't do that "automatically" - you can't "get at" a variable's content by using a string containing it's name without creating a complete SQL statement and EXECuting that in an environment that also has the variables passed to it.

Forget this plan: it's not going to work without a lot of faffing about, and hard-to-maintain code.

Instead, use a simple CASE WHEN statement to "hardcode" the values:
https://www.w3schools.com/sql/sql_case.asp

SET @RESULT = CASE WHEN @finalvar = 1 THEN @bck1
                   WHEN @finalvar = 2 THEN @bck1 + @bck2
                   WHEN @finalvar = 3 THEN @bck1 + @bck2 + @bck3 
                   WHEN @finalvar = 4 THEN @bck1 + @bck2 + @bck3 + @bck4
                   ...
              END;

It'll be quicker, easier to maintain, and ... it'll work the first time you try it ...