CREATE PROC [dbo].[Test] (@UserTypeID  INT,
                          @UserID      INT,
                          @CityID      INT,
                          @OperatorID  INT,
                          @ParameterID INT)
AS
  BEGIN
      DECLARE @temp TABLE (
        range  DECIMAL(18, 2),
        range2 DECIMAL(18, 2),
        image  VARCHAR(50),
        symbol VARCHAR(20))

      IF( @UserID > 0 )
        BEGIN
            --print 'hii'
            INSERT INTO @temp
                        (range,
                         range2,
                         image,
                         symbol)
            SELECT tbl_Legend_ViewNetwork_Dtls.range,
                   tbl_Legend_ViewNetwork_Dtls.range2,
                   tbl_Legend_ViewNetwork_Dtls.image,
                   tbl_Legend_ViewNetwork_Dtls.symbol
            FROM   tbl_Legend_ViewNetwork_Dtls
                   INNER JOIN tbl_Legend_ViewNetwork
                     ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id
            WHERE  tbl_Legend_ViewNetwork.parameter_id = @ParameterID
                   AND tbl_Legend_ViewNetwork.user_type_id = @UserTypeID
                   AND tbl_Legend_ViewNetwork.is_default = 1
                   AND tbl_Legend_ViewNetwork.user_id = @UserID

            UPDATE @temp
            SET    range = range2,
                   range2 = range
            WHERE  symbol = '<'
        END
      ELSE
        BEGIN
            INSERT INTO @temp
                        (range,
                         range2,
                         image,
                         symbol)
            SELECT tbl_Legend_ViewNetwork_Dtls.range,
                   tbl_Legend_ViewNetwork_Dtls.range2,
                   tbl_Legend_ViewNetwork_Dtls.image,
                   tbl_Legend_ViewNetwork_Dtls.symbol
            FROM   tbl_Legend_ViewNetwork_Dtls
                   INNER JOIN tbl_Legend_ViewNetwork
                     ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id
            WHERE  tbl_Legend_ViewNetwork.parameter_id = @ParameterID
                   AND tbl_Legend_ViewNetwork.user_type_id = @UserTypeID
                   AND tbl_Legend_ViewNetwork.is_default = 1

            UPDATE @temp
            SET    range = range2,
                   range2 = range
            WHERE  symbol = '<'
        END

      --select * from @temp
      SELECT '[' + STUFF((SELECT ',{"latitude":"' + a.lat + '","longitude":"' + a.long + '","value":"' + CONVERT(VARCHAR(20), a.value) + '","image":"' + temp.image + '"}'
                          FROM   (SELECT tbl_Survey_Details.lat,
                                         tbl_Survey_Details.long,
                                         tbl_Survey_Details.value
                                  FROM   tbl_Survey_Details
                                         INNER JOIN tbl_Survey
                                           ON tbl_Survey_Details.tbl_survey_id = tbl_Survey.id
                                         INNER JOIN tbl_Location
                                           ON tbl_Survey.tbl_location_id = tbl_Location.id
                                         INNER JOIN tbl_Area
                                           ON tbl_Location.tbl_area_id = tbl_Area.id
                                         INNER JOIN tbl_City
                                           ON tbl_Area.tbl_city_id = tbl_City.id
                                  WHERE  tbl_Survey_Details.tbl_parameter_id = @ParameterID
                                         AND tbl_Survey.tbl_mobile_operator_id = @OperatorID
                                         AND tbl_Area.tbl_city_id = @CityID) AS a
                                 INNER JOIN @temp temp
                                   ON a.value BETWEEN temp.range AND temp.range2
                          FOR XML Path ('')), 1, 1, '') + ']' AS data
  END

CREATE PROC [dbo].[Test] (@UserTypeID  INT,
                          @UserID      INT,
                          @CityID      INT,
                          @OperatorID  INT,
                          @ParameterID INT)
AS
  BEGIN
      DECLARE @temp TABLE (
        range  DECIMAL(18, 2),
        range2 DECIMAL(18, 2),
        image  VARCHAR(50),
        symbol VARCHAR(20))

      IF( @UserID > 0 )
        BEGIN
            --print 'hii'
            INSERT INTO @temp
                        (range,
                         range2,
                         image,
                         symbol)
            SELECT tbl_Legend_ViewNetwork_Dtls.range,
                   tbl_Legend_ViewNetwork_Dtls.range2,
                   tbl_Legend_ViewNetwork_Dtls.image,
                   tbl_Legend_ViewNetwork_Dtls.symbol
            FROM   tbl_Legend_ViewNetwork_Dtls
                   INNER JOIN tbl_Legend_ViewNetwork
                     ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id
            WHERE  tbl_Legend_ViewNetwork.parameter_id = @ParameterID
                   AND tbl_Legend_ViewNetwork.user_type_id = @UserTypeID
                   AND tbl_Legend_ViewNetwork.is_default = 1
                   AND tbl_Legend_ViewNetwork.user_id = @UserID

            UPDATE @temp
            SET    range = range2,
                   range2 = range
            WHERE  symbol = '<'
        END
      ELSE
        BEGIN
            INSERT INTO @temp
                        (range,
                         range2,
                         image,
                         symbol)
            SELECT tbl_Legend_ViewNetwork_Dtls.range,
                   tbl_Legend_ViewNetwork_Dtls.range2,
                   tbl_Legend_ViewNetwork_Dtls.image,
                   tbl_Legend_ViewNetwork_Dtls.symbol
            FROM   tbl_Legend_ViewNetwork_Dtls
                   INNER JOIN tbl_Legend_ViewNetwork
                     ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id
            WHERE  tbl_Legend_ViewNetwork.parameter_id = @ParameterID
                   AND tbl_Legend_ViewNetwork.user_type_id = @UserTypeID
                   AND tbl_Legend_ViewNetwork.is_default = 1

            UPDATE @temp
            SET    range = range2,
                   range2 = range
            WHERE  symbol = '<'
        END

   SELECT   a.lat, a.long,a.value, temp.image
                          FROM   (SELECT tbl_Survey_Details.lat,
                                         tbl_Survey_Details.long,
                                         tbl_Survey_Details.value
                                  FROM   tbl_Survey_Details
                                         INNER JOIN tbl_Survey
                                           ON tbl_Survey_Details.tbl_survey_id = tbl_Survey.id
                                         INNER JOIN tbl_Location
                                           ON tbl_Survey.tbl_location_id = tbl_Location.id
                                         INNER JOIN tbl_Area
                                           ON tbl_Location.tbl_area_id = tbl_Area.id
                                         INNER JOIN tbl_City
                                           ON tbl_Area.tbl_city_id = tbl_City.id
                                  WHERE  tbl_Survey_Details.tbl_parameter_id = @ParameterID
                                         AND tbl_Survey.tbl_mobile_operator_id = @OperatorID
                                         AND tbl_Area.tbl_city_id = @CityID) AS a
                                 INNER JOIN @temp temp
                                   ON a.value BETWEEN temp.range AND temp.range2

  END

CREATE PROC [dbo].[Test] (@UserTypeID  INT,
                      @UserID      INT,
                      @CityID      INT,
                      @OperatorID  INT,
                      @ParameterID INT)
AS BEGIN

with cte1 as (
        SELECT

            case  tbl_Legend_ViewNetwork_Dtls.symbol
            when '<' then tbl_Legend_ViewNetwork_Dtls.range2
            else tbl_Legend_ViewNetwork_Dtls.range
        end
        as range,

        case  tbl_Legend_ViewNetwork_Dtls.symbol
            when '<' then tbl_Legend_ViewNetwork_Dtls.range
            else tbl_Legend_ViewNetwork_Dtls.range2
        end
        as range2,
       tbl_Legend_ViewNetwork_Dtls.image
        FROM   tbl_Legend_ViewNetwork_Dtls
               INNER JOIN tbl_Legend_ViewNetwork
                 ON tbl_Legend_ViewNetwork_Dtls.tbl_legend_view_network_id = tbl_Legend_ViewNetwork.id
        WHERE  tbl_Legend_ViewNetwork.parameter_id = @ParameterID
               AND tbl_Legend_ViewNetwork.user_type_id = @UserTypeID
               AND tbl_Legend_ViewNetwork.is_default = 1
               AND (tbl_Legend_ViewNetwork.user_id = @UserID OR @UserID is NULL)


),

cte2 as (

    SELECT
        tbl_Survey_Details.lat,
                    tbl_Survey_Details.long,
                    tbl_Survey_Details.value

    FROM    tbl_City

    INNER JOIN tbl_Area
        ON tbl_Area_tbl_city_id = tbl_City.id AND tbl_city_id = @CityID

    INNER JOIN tbl_Location
        ON tbl_Location.tbl_area_id = tbl_Area.id

    INNER JOIN tbl_Survey
        ON tbl_Survey.tbl_location_id = tbl_Location.id

    INNER JOIN tbl_Survey_Details
        ON tbl_Survey_Details.tbl_survey_id = tbl_Survey.id

    WHERE   tbl_Survey_Details.tbl_parameter_id = @ParameterID
                    AND
        tbl_Survey.tbl_mobile_operator_id = @OperatorID
)


SELECT  a.lat, a.long,a.value, cte1.image
            FROM cte2

            INNER JOIN cte1
                   ON cte2.value BETWEEN cte1.range AND cte1.range2
OPTION (RECOMPILE)
END