October 7, 2011 at 4:15 am
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]
@varKeywordNVARCHAR(128) = NULL
, @varCountryNVARCHAR(128) = NULL
, @varStateNVARCHAR(128) = NULL
, @varDistINT = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @varKeywordFlagBIT
DECLARE @varStateFlagBIT
DECLARE @varCountryFlagBIT
DECLARE @varDistFlagBIT
DECLARE @varDataFlagBIT
DECLARE @Meeting_LocNVARCHAR(80)
DECLARE @varRLimitav_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_IDAS 'Club/@ID'
, District_IDAS [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
FROMmeeting_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_searchLIKE '%' + @varKeyword+ '%') OR (address_keyword_search=@varKeyword))
AND((@varStateFlag= 0)OR (address_state_provinceLIKE '%' + @varState+ '%'))
ORDER BY Club_Name
SELECT TOP (@varRLimit)* FROM #tmp_meeting_location_info
FOR XML PATH ('Club'), ROOT('Clubs')
END
GO
Thanks,
Kiran
October 7, 2011 at 4:23 am
Hello and welcome to SSC!
Well, golly gosh! It seems that not only has your DDL script become detached from your post but also your actual question is missing!!
So, to help with these problems I'm afraid I'm going to point you at a couple of articles.
Firstly, this article may be useful in helping you to post a question.
And secondly, this article[/url] explains the best way to provide us with working sample data and DDL scripts.
Between the two, it will allow the unpaid volunteers of this site to provide you with working, tested code for whatever your particular problem is.
Thanks!
October 7, 2011 at 4:24 am
Nice procedure. So, what's the question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply