Click here to Skip to main content
14,359,985 members
Rate this:
Please Sign up or sign in to 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
Updated 4-Oct-19 2:09am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

SQL doesn't have a "--" operator - instead "--" indicates the start of a comment.
What you need is:
SET @Finalvar = @Finalvar - 1;


[edit]
But even with that fixed, the code won;t work: you will get a conversion error on yoru CAST:
set @coun+= cast(@bck as tinyint);
Because the value in @bck is not a number:
declare @bck varchar(5)='bck'+@finalvar

[/edit]
   
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 ...
Rate this:
Please Sign up or sign in to vote.

Solution 2

--, in SQL, stands for the beginning of a comment line.
SQL does not support increment/decrement operator.
Instead of
set @finalvar--;
you have to write
set @finalvar = @finalvar - 1;
   
v2
Comments
Aarti Meswania 7-Oct-19 2:04am
   
5ed! :)
phil.o 7-Oct-19 4:23am
   
Thanks :)

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100