SQL Function.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Function..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-01 : 09:37:14

Hi All..

I was writing the function but get stuck, hope you guys can help me out with it.

Function Parameter : Varchar(20)
Functions Return : Varchar(50)

Now if i pass the parameter to the function like this

Input Value : Select Dbo.ReturnNewVersionCode('EC11')
Output Value: EC11.1.1

Input Value : Select Dbo.ReturnNewVersionCode('EC11.1')
Output Value: EC11.1.2

Input Value : Select Dbo.ReturnNewVersionCode('EC')
Output Value: EC.1.0

Input Value : Select Dbo.ReturnNewVersionCode('EC..1')
Output Value: EC..1.1.0

Till Now I wrote the following code hope this makes any sense..


Create Function GetEventNextVersion
(
	@pEventCode varchar(50)
)
returns Varchar(50)
Begin 
Declare @EventRev Varchar(50),@tmpVariable Varchar(50)
Declare @pVersionCode float,@tmp1 float
Set @EventRev = Reverse(@pEventCode)
Select @pVersionCode= Case When Dbo.ISEmptyVarchar(Reverse(Left(@EventRev,Charindex('.',@EventRev)))) Is Null THEN   1.0
	 When IsNumeric(Reverse(Left(@EventRev,CharIndex('.',@EventRev)-1))) = 1  Then Convert(float,Reverse(Left(@EventRev,CharIndex('.',@EventRev)-1))) + 0.1-- Checking if code is already some version then next version should be added so checking for the isnumeric if found then add 1
	 Else  1.0 End

Select  @tmpVariable = Convert(Varchar(100),@pVersionCode)
if @EventRev = 1.0
Begin 
	Set @EventRev = @pEventCode + '.' + @tmpVariable
End
Else
Begin 
	Set @EventRev = Reverse(SubString(@EventRev,CharIndex('.',@EventRev)+1,len(@EventRev)))  + '.' + @tmpVariable
End 
return @EventRev
End

This functions doesnt some how works the way i want it too..

Any help..

Complicated things can be done by simple thinking

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-01 : 09:44:18

quote:

Input Value : Select Dbo.ReturnNewVersionCode('EC11')
Output Value: EC11.1.1

Input Value : Select Dbo.ReturnNewVersionCode('EC11.1')
Output Value: EC11.1.2

Input Value : Select Dbo.ReturnNewVersionCode('EC')
Output Value: EC.1.0

Input Value : Select Dbo.ReturnNewVersionCode('EC..1')
Output Value: EC..1.1.0

these don't seem to follow any pattern... what are the rules you are trying to follow?

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-01 : 09:50:19

quote:
Originally posted by Seventhnight

[quote]
these don't seem to follow any pattern... what are the rules you are trying to follow?

oks the pattern is somthing like this ..

If the code passed is
EC11-> this is the orginal code then its new version should be EC11.1.1
and now if the code passed is EC11.1.0
Then its next version should come like this EC11.1.1

OH my mistake .. in the earlier post.. i guess by mistake i put 1.1

Complicated things can be done by simple thinking

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-01 : 10:48:56

Hi,

I don't know what ever I understood from your logic is right or not but, I tried to put some idea on screen. Please check if the function below works. Hope it works

Regards
Sachin Samuel

Create Function GetEventNextVersion_Sachin
(
@pEventCode varchar(50)
)
returns Varchar(50)
Begin
declare @strret varchar(50)
set @strret=''

if charindex('.',@pEventCode)>0
begin
set @strret=reverse(@pEventCode)
set @strret=@pEventCode+'.'+convert(varchar,substring(reverse(left(@strret,charindex('.',@strret))),2,len(@strret))+1)
--

end
else
begin
if isnumeric(right(@pEventCode,1))=1
begin
set @strret=@pEventCode+'.1.1'
end
else
begin
set @strret=@pEventCode+'.1.0'
end
end
return @strret
End

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-02 : 01:21:37

Well I tired out your query but there is some problem with it..

See when you run the Query..

Select Dbo.GetEventNextVersion_Sachin('EC')
Output comes -> EC.1.0 --> perfect..

Select Dbo.GetEventNextVersion_Sachin('EC.1.0')
Output Comes -> EC.1.0.1 --> not correct

Instead of which i want.. EC.1.1 ..Right since the next version after 1.0 would be 1.1 and then 1.2 and so on n so forth ..

I hope this makes some sense..:-)

Thanks for the help..

Complicated things can be done by simple thinking

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-02 : 04:06:08

Well finally got the what was required.. :-)

just have to change one '.' with '_' from the orginal design..

this is the following query

Create Function GetEventNextVersion
(
@pEventCode varchar(50)
)
returns Varchar(50)
Begin
Declare @fNextVersion Varchar(10) -- in this variable we will first get the current version and then we will add next version to it.
Declare @EventCode Varchar(50) -- in this we will just get the event code and then add '_' and next version to it..
Declare @VarTmp Varchar(50) --This is just the temp variable..:-)
-- Lets first get the Version Information ...
Set @VarTmp = Reverse(@pEventCode)
Select @fNextVersion = Case When IsNumeric(Left(@VarTmp,CharIndex('_',@varTmp)-1)) = 1 Then
Case When CharIndex('.',Left(@varTmp,CharIndex('_',@varTmp)-1)) = 0 Then
'1.0'
Else
Convert(Varchar(10),Convert(Numeric(10,1),Reverse(Left(@varTmp,Charindex('_',@varTmp)-1))) + 0.1)
End
Else
'1.0' End ,
-- So now we got the version number with us.. get the Event Code...
@EventCode = Case When IsNumeric(Left(@VarTmp,CharIndex('_',@varTmp)-1)) = 1 Then
Case When CharIndex('.',Left(@varTmp,CharIndex('_',@varTmp)-1)) = 0 Then
@pEventCode
Else
Reverse(SubString(@varTmp,CharIndex('_',@varTmp)+1,len(@varTmp)))
End
Else
@pEventCode
End

Return @EventCode+'_'+@fNextVersion
End

Select Dbo.GetEventNextVersion('EC_000_4.1')
Ouput EC_000_4.2

Select Dbo.GetEventNextVersion('EC_000')
Ouput EC_000_1.0

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources