Need to retrieve a data row that having minimum sum of 4 red squared values.

i Have done a SP ,but seems to be it was wrong.

select *
select *
from SMST as p
where  p.Source=@Source AND p.Destination=@Destination OR p.Destination2=@Destination AND  [Count]=(Select MIN(sourceRoadCount+middleroadcount+destinationroadcount+x1destinationroad2count as [Count]) from SMST  )

Source and Destination will be given by user on a win form.
sourceRoadCount+middleroadcount+destinationroadcount+x1destinationroad2count are the 4 field that i want to get minimum sum valu.

select *
 from SMST as p
  where  p.Source=@Source AND ( p.Destination=@Destination OR p.Destination2=@Destination) AND  (p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)>=4 from SMST
select *
 from SMST as p
  where  p.Source=@Source AND ( p.Destination=@Destination OR p.Destination2=@Destination) AND  (p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)>=4 from SMST

thanx for quick replying dear.

but on last line....

(p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)>=4 from SMST .

this is not what i wont.
what i need is ;
the minimum count of

(p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)

these fields.

so is this correct..

select *
 from SMST as p
  where  p.Source=@Source AND ( p.Destination=@Destination OR p.Destination2=@Destination) AND  [SUM[(p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)]] from SMST

No, since everything in the where clause needs to be compared to something else. What you are looking for is something like

SELECT * FROM smst as p
WHERE p.Source = @Source AND (p.Destination = @Destination OR p.Destination2 = @Destination) AND
 p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.xldestinationroad2count =
    (SELECT Min(t.sourceRoadCount+t.middleroadcount+t.destinationroadcount+t.xldestinationroad2count)
     FROM smst as t 
     WHERE t.Source = @Source AND (t.Destination=@Destination OR t.Destination2=@Destination)

You may need some GROUP BY clauses in there.

You could make it smaller by removing the inner select, specifying the fields you want (rather than *), and using GROUP BYs. Since I don't know what your table looks like, I can't really do that for you :)

thanxxx..........

OOh,.... i got a error on this code......

Msg 156, Level 15, State 1, Procedure getdata, Line 28
Incorrect syntax near the keyword 'END'.


this is te error i got.

Instead of posting a backup to a database that we know nothing about (what database server are you using?) just post the stored procedure that is causing the problem.