stored procedure giving wrong output

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

General SQL Server Forums

New to SQL Server Programming

stored procedure giving wrong output

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-12-05 : 13:15:50

Hi All,

Iam having clientlist table which contains clients username and the clientversions details.

Here is the list of Clisntversions of my table:

2.2.0.12
2.2.0.13
2.2.0.16
2.2.0.18
2.3.0.1
2.3.0.2

From the list we can see the maximum version is 2.3.0.2, but when i ran my SP iam getting the maximum client version is 2.2.0.18. please find the below query and help me on the same :

DECLARE @Temp as table(ver VARCHAR(20),Major INT,Minor INT,Build INT, Revision INT)

INSERT INTO @Temp
SELECT DISTINCT (ClientVersion),NULL,NULL,NULL,NULL FROM ClientList WHERE LoginStatus<>0

DECLARE @RecCount INT,@MaxMajor INT,@MxCount INT,@MaxMinor INT,@MaxBuild INT,@MaxRevision INT

UPDATE
@Temp
SET Major=[dbo].[udf_Version] (Ver,1)

UPDATE
@Temp
SET Minor=[dbo].[udf_Version] (Ver,2)

UPDATE
@Temp
SET Build=[dbo].[udf_Version] (Ver,3)

UPDATE
@Temp
SET Revision=[dbo].[udf_Version] (Ver,4)

SELECT @RecCount=COUNT(*) FROM @Temp
SELECT @MaxMajor=MAX(Major) FROM @Temp

DECLARE @FinalVal INT
DECLARE @Ver TABLE(VAL INT)
DECLARE @Ver1 TABLE(VAL VARCHAR(100))

INSERT INTO @Ver SELECT DISTINCT MAJOR FROM @Temp
IF (SELECT COUNT(*) FROM @Ver)=1
BEGIN
DELETE FROM @Ver
SELECT @MaxMinor=MAX(Minor) FROM @Temp
INSERT INTO @Ver SELECT DISTINCT Minor FROM @Temp
IF ((SELECT COUNT(*) FROM @Ver)=1) OR ((SELECT COUNT(*) FROM @Temp WHERE Minor=@MaxMinor)>1)
BEGIN
DELETE FROM @Ver
SELECT @MaxBuild =MAX(Build) FROM @Temp
INSERT INTO @Ver SELECT DISTINCT Build FROM @Temp
IF ((SELECT COUNT(*) FROM @Ver)=1) OR ((SELECT COUNT(*) FROM @Temp WHERE Build=@MaxBuild)>1)
BEGIN
DELETE FROM @Ver
INSERT INTO @Ver SELECT DISTINCT Revision FROM @Temp
SELECT @MaxRevision =MAX(Revision) FROM @Temp
INSERT INTO @Ver1 SELECT ver from @Temp WHERE Revision =@MaxRevision

END
ELSE
BEGIN
INSERT INTO @Ver1 SELECT ver from @Temp WHERE Build =@MaxBuild
END
END
ELSE
BEGIN
INSERT INTO @Ver1 SELECT ver from @Temp WHERE Minor =@MaxMinor
END
END
ELSE
BEGIN
INSERT INTO @Ver1 SELECT ver from @Temp WHERE Major=@MaxMajor
END

SELECT TOP 1 @Version= VAL FROM @Ver1
END

Thanks,
Ram

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 13:18:29

didnt you get solution here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168735

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-12-05 : 13:36:22

Hi Visakh,

I got the solution from you but i also wanted to know whats worng in my stored procedure as well..

Thanks,
Ram

rams

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-05 : 14:07:37

Have you tried to follow the logic you implemented?

It's getting down to the Revision section (based on the data in the original post) so the MAX revision is 18. It's doing what you asked it to so. Now, if you want to know how to get the MAX, you might want to do a search on that as this has been solved several times on these forums alone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources