Need to Add text in SP

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  October 10, 2011 at 7:21 am

  #244976

  Hi,

  I have Implemented logic for getting corrected records in my SP, but I need some bit help from you in my below SP..

  As per my requirement my SP has returned 50 records as per "like" condition and one one record for exact match.

  But my concern is if suppose there would be more than 50 records then I have to show there "More than 50 results found, showing the first 50." in my SP.

  Only I have to show if there would be more than 50 records but records are below 50 than I don’t have to show the above mentioned text.

  So please suggest me how to Implement this thing in my SP.

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_client_ri_club_locator]') AND type in (N'P', N'PC'))

  DROP PROCEDURE [dbo].[sp_client_ri_club_locator]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE [dbo].[sp_client_ri_club_locator]

  @varKeyword NVARCHAR(128) = NULL

  , @varCountry NVARCHAR(128) = NULL

  , @varState NVARCHAR(128) = NULL

  , @varDist INT = NULL

  AS

  BEGIN

  SET NOCOUNT ON;

  DECLARE @varKeywordFlag BIT

  DECLARE @varStateFlag BIT

  DECLARE @varCountryFlag BIT

  DECLARE @varDistFlag BIT

  DECLARE @varDataFlag BIT

  DECLARE @Meeting_Loc NVARCHAR(80)

  DECLARE @varRLimit av_integer

  SELECT @varRLimit = rc4_job_schedule FROM client_ri_rc4_data_sync_log(NOLOCK) WHERE rc4_job_name = 'GeoCodeSync'

  IF (@varKeyword IS NULL OR @varKeyword = '')

  SET @varKeywordFlag = 0

  ELSE

  SET @varKeywordFlag = 1

  IF (@varState IS NULL OR @varState = '')

  SET @varStateFlag = 0

  ELSE

  SET @varStateFlag = 1

  IF (@varCountry IS NULL OR @varCountry = '')

  SET @varCountryFlag = 0

  ELSE

  SET @varCountryFlag = 1

  IF (@varDist IS NULL OR @varDist = 0)

  SET @varDistFlag = 0

  ELSE

  SET @varDistFlag = 1

  IF (@varKeywordFlag = 0 AND @varStateFlag = 0 AND @varCountryFlag = 0 AND @varDistFlag = 0)

  SET @varDataFlag = 0

  ELSE

  SET @varDataFlag = 1

  SELECT

  Club_Key

  , (club_name +', '+ ISNULL(address_state_province,address_country))AS 'Club/@Name'

  , Club_ID AS 'Club/@ID'

  , District_ID AS [Dst.]

  , CONVERT(DECIMAL(19,4),address_latitude) AS adr_latitude

  , CONVERT(DECIMAL(19,4),address_longitude) AS adr_longitude

  , ISNULL(meeting_time_format,'Not Known') AS mtg_time

  INTO #tmp_meeting_location_info

  FROM meeting_location_info(NOLOCK)

  WHERE @varDataFlag = 1

  AND ( (@varDistFlag = 0) OR (district_id = @varDist))

  AND ( (@varCountryFlag = 0) OR (address_country = @varCountry))

  AND ( (@varKeywordFlag = 0) OR (address_keyword_search LIKE '%' + @varKeyword + '%') OR (address_keyword_search=@varKeyword))

  AND ( (@varStateFlag = 0) OR (address_state_province LIKE '%' + @varState + '%'))

  ORDER BY Club_Name

  SELECT TOP (@varRLimit)* FROM #tmp_meeting_location_info

  FOR XML PATH ('Club'), ROOT('Clubs')

  END

  GO

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 10, 2011 at 7:25 am

  #1392396

  Use an output parameter. Set the value of the output parameter to the number of rows returned by the SELECT. Interpret the parameter at the client side.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden