Want more features on Pastebin? Sign Up, it's FREE!

cfstoredproc

By: rhpt on Jan 24th, 2013  |  syntax: T-SQL  |  size: 6.57 KB  |  views: 35  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. ALTER Proc [rankingResults]
  2. @CityZipID int,
  3. @Sic int,
  4. @lastRank int = 0,
  5. @Distance int = null,
  6. @ReviewCount int = null,
  7. @HasUrl bit = null,
  8. @PhotoCount int = null,
  9. @IsFeatured bit = null,
  10. @AttrCodes varchar(max) = null,
  11. @SicBudgetIDs varchar(max) = null
  12.  
  13. AS
  14.  
  15. BEGIN
  16.  
  17. DECLARE @SQL nvarchar(max),
  18.         @CtSQL nvarchar(max),
  19.         @AttrCte varchar(2000),
  20.         @MEBCte varchar(2000),
  21.         @becnt int
  22.  
  23. IF @AttrCodes IS NULL
  24.         SET @AttrCte = ''
  25. ELSE
  26.         SET @AttrCte =
  27.                 'WITH AttrCte (be_No, sic, ATTR_CODE) AS
  28.                 (
  29.                         SELECT Be_No, sic, ATTR_CODE
  30.                         FROM map_vend2indusattr a (NOLOCK) INNER JOIN def_filter f (NOLOCK) ON a.filterId = f.filterId
  31.                         WHERE ATTR_CODE IN (SELECT * FROM dbo.Split(@AttrCodes,' + ''',''' + '))
  32.                 )'
  33.  
  34. IF @SicBudgetIDs IS NULL
  35.         SET @MEBCte = ''
  36. ELSE
  37.         BEGIN
  38.                 IF @AttrCte <> ''
  39.                         SET @MEBCte = ', '
  40.                 ELSE
  41.                         SET @MEBCte = 'WITH '
  42.                        
  43.                 SET @MEBCte = @MEBCte + 'MEBCte (sic, Be_No, sic_budget_id) AS
  44.                         (
  45.                                 SELECT dsb.sic, meb.Be_No, meb.sic_budget_id
  46.                                 FROM map_excluded_budgets2be meb (NOLOCK) INNER JOIN dbo.def_sic_budgets dsb (NOLOCK) ON meb.sic_budget_id = dsb.sic_budget_id
  47.                                 WHERE meb.sic_budget_id IN (SELECT * FROM dbo.Split(@SicBudgetIDs,' + ''',''' + '))
  48.                         ) '
  49.         END
  50.  
  51. SET @SQL = @AttrCte + @MEBCte
  52.  
  53. SET @SQL = @SQL + '
  54.         SELECT DISTINCT TOP 30 b.be_no, b.parent_no, b.be_name, b.zip_code as zip, r.rank, hasPhoto,
  55.                 CASE WHEN v.topic_profile IS NULL OR Len(LTrim(RTrim(v.topic_profile))) = 0 THEN Left(b.profile_info,120) ELSE Left(v.topic_profile,120) END AS profile,
  56.                 b.PHONE_AREA_CODE, b.Address1, b.Address2, b.PHONE_NO, b.CITY, b.STATE_CODE, r.isFeatured, b.latitude, b.longitude, r.pp_bid,
  57.                 CASE WHEN b.ctc IS NULL THEN 0 ELSE 1 END AS HasCtc,
  58.                 dbo.GetReviewCount(b.be_No) ReviewCount,
  59.                 dbo.GetMinBudget(b.be_no, @Sic) MinBudget
  60.         FROM def_rankings r (NOLOCK) INNER JOIN Def_Be b (NOLOCK) ON r.Be_No = b.Be_No INNER JOIN map_vend2industopic v (NOLOCK) ON r.Be_No = v.Be_No AND r.Sic = v.Sic'
  61.  
  62. IF @AttrCodes IS NOT NULL
  63.         SET @SQL = @SQL + ' LEFT JOIN AttrCte a ON r.be_no = a.be_no AND r.sic = a.sic '
  64.  
  65. IF @SicBudgetIDs IS NOT NULL
  66.         SET @SQL = @SQL + 'LEFT JOIN MebCte meb ON r.sic = meb.sic and r.Be_No = meb.Be_No '
  67.  
  68. SET @SQL = @SQL + ' WHERE r.CityZipID = @CityZipID AND r.Sic = @Sic'
  69.  
  70. IF @lastRank != 0
  71.         SET @SQL = @SQL + ' AND r.rank > @lastRank'
  72.  
  73. IF @Distance IS NOT NULL
  74.         SET @SQL = @SQL + ' AND r.Distance <= @Distance'
  75.  
  76. IF @ReviewCount IS NOT NULL
  77.         SET @SQL = @SQL + ' AND ReviewCount >= @ReviewCount'
  78.  
  79. IF @HasUrl IS NOT NULL
  80.         SET @SQL = @SQL + ' AND HasUrl = @HasUrl'
  81.  
  82. IF @PhotoCount IS NOT NULL
  83.         SET @SQL = @SQL + ' AND PhotoCount >= @PhotoCount'
  84.  
  85. IF @IsFeatured IS NOT NULL
  86.         SET @SQL = @SQL + ' AND IsFeatured = @IsFeatured'
  87.  
  88. IF @AttrCodes IS NOT NULL
  89.         SET @SQL = @SQL + ' AND a.ATTR_CODE IS NULL'
  90.  
  91. IF @SicBudgetIDs IS NOT NULL
  92.         SET @SQL = @SQL + ' AND meb.sic_budget_id IS NULL'
  93.  
  94. SET @SQL = @SQL + ' ORDER BY r.rank'
  95.  
  96. CREATE TABLE #Bes
  97. (
  98.         Be_No int not null,
  99.         ReviewCount int not null,
  100.         HasUrl bit not null,
  101.         PhotoCount int not null,
  102.         IsFeatured bit not null
  103.  
  104. );
  105.  
  106. SET @CtSQL = @AttrCte + @MEBCte
  107. SET @CtSQL = @CtSQL + '
  108.         INSERT INTO #Bes (Be_No, ReviewCount, HasUrl, PhotoCount, IsFeatured)
  109.         SELECT DISTINCT r.be_no, r.ReviewCount, r.HasUrl, r.PhotoCount, r.IsFeatured
  110.         FROM def_rankings r (NOLOCK) INNER JOIN Def_Be b (NOLOCK) ON r.Be_No = b.Be_No INNER JOIN map_vend2industopic v (NOLOCK) ON r.Be_No = v.Be_No AND r.Sic = v.Sic'
  111.  
  112. IF @AttrCodes IS NOT NULL
  113.         SET @CtSQL = @CtSQL + ' LEFT JOIN AttrCte a ON r.be_no = a.be_no AND r.sic = a.sic '
  114.  
  115. IF @SicBudgetIDs IS NOT NULL
  116.         SET @CtSQL = @CtSQL + 'LEFT JOIN MebCte meb ON r.sic = meb.sic and r.Be_No = meb.Be_No '
  117.  
  118. SET @CtSQL = @CtSQL + ' WHERE r.CityZipID = @CityZipID AND r.Sic = @Sic'
  119.  
  120. IF @lastRank != 0
  121.         SET @ctSQL = @ctSQL + ' AND r.rank > @lastRank'
  122.  
  123. IF @Distance IS NOT NULL
  124.         SET @CtSQL = @CtSQL + ' AND r.Distance <= @Distance'
  125.  
  126. IF @ReviewCount IS NOT NULL
  127.         SET @CtSQL = @CtSQL + ' AND ReviewCount >= @ReviewCount'
  128.  
  129. IF @HasUrl IS NOT NULL
  130.         SET @CtSQL = @CtSQL + ' AND HasUrl = @HasUrl'
  131.  
  132. IF @PhotoCount IS NOT NULL
  133.         SET @CtSQL = @CtSQL + ' AND PhotoCount >= @PhotoCount'
  134.  
  135. IF @IsFeatured IS NOT NULL
  136.         SET @CtSQL = @CtSQL + ' AND IsFeatured = @IsFeatured'
  137.  
  138. IF @AttrCodes IS NOT NULL
  139.         SET @CtSQL = @CtSQL + ' AND a.ATTR_CODE IS NULL'
  140.  
  141. IF @SicBudgetIDs IS NOT NULL
  142.         SET @CtSQL = @CtSQL + ' AND meb.sic_budget_id IS NULL'
  143.  
  144. EXEC sp_executesql @CtSQL,N'@CityZipID int, @Sic int, @Distance int, @ReviewCount int, @HasUrl bit, @PhotoCount int,@IsFeatured bit, @AttrCodes varchar(max), @SicBudgetIDs varchar(max), @lastRank int', @CityZipID, @Sic, @Distance, @ReviewCount, @HasUrl, @PhotoCount,@IsFeatured, @AttrCodes, @SicBudgetIDs, @lastRank
  145.  
  146. SET @becnt = @@Rowcount
  147.  
  148. CREATE TABLE #MyTotals
  149. (
  150.         TypeID char(1) not null,
  151.         ID int not null,
  152.         RowCt int not null
  153. );
  154.  
  155. WITH FCte (Be_No, attr_code) AS
  156. (
  157.         SELECT b.Be_No, attr_code
  158.         FROM #Bes b LEFT JOIN map_vend2indusattr v ON b.Be_No = v.Be_No
  159. )
  160. INSERT INTO #MyTotals(TypeID, ID, RowCt)
  161. SELECT 'F', a.attr_code,
  162.         CASE WHEN v.attr_code IS NULL THEN @becnt ELSE @becnt-count(1) END AS RowCt
  163. FROM def_indus_attr a (NOLOCK) INNER JOIN def_filter f (NOLOCK) ON a.filterid = f.filterid LEFT JOIN FCte v ON a.attr_code = v.attr_code
  164. WHERE f.sic = @Sic
  165. GROUP BY a.attr_code,v.attr_code;
  166.  
  167. WITH BCte(Be_No, sic_budget_id) AS
  168. (
  169.         SELECT b.Be_No, sic_budget_id
  170.         FROM #Bes b LEFT JOIN map_excluded_budgets2be meb ON b.be_no = meb.be_no
  171. )
  172. INSERT INTO #MyTotals(TypeID, ID, RowCt)
  173. SELECT 'B', dsb.sic_budget_id,
  174.         CASE WHEN meb.sic_budget_id IS NULL THEN @becnt ELSE @becnt-count(1) END AS RowCt
  175. FROM def_sic_budgets dsb (NOLOCK) LEFT JOIN BCte meb (NOLOCK) ON meb.sic_budget_id = dsb.sic_budget_id
  176. WHERE dsb.sic = @Sic AND dsb.DisplayOnly = 0
  177. GROUP BY dsb.sic_budget_id, meb.sic_budget_id;
  178.  
  179. INSERT INTO #MyTotals(TypeID, ID, RowCt)
  180. SELECT 'G', 1, Count(ReviewCount)
  181. FROM #Bes
  182. WHERE ReviewCount > 0;
  183.  
  184. INSERT INTO #MyTotals(TypeID, ID, RowCt)
  185. SELECT 'G', 2, Count(HasUrl)
  186. FROM #Bes
  187. WHERE HasUrl <> 0;
  188.  
  189. INSERT INTO #MyTotals(TypeID, ID, RowCt)
  190. SELECT 'G', 3, Count(PhotoCount)
  191. FROM #Bes
  192. WHERE PhotoCount > 0;
  193.  
  194. INSERT INTO #MyTotals(TypeID, ID, RowCt)
  195. SELECT 'G', 4, Count(IsFeatured)
  196. FROM #Bes
  197. WHERE IsFeatured <> 0
  198.  
  199. EXEC sp_executesql @SQL, N'@CityZipID int, @Sic int, @Distance int, @ReviewCount int, @HasUrl bit, @PhotoCount int,@IsFeatured bit, @AttrCodes varchar(max), @SicBudgetIDs varchar(max), @lastRank int', @CityZipID, @Sic, @Distance, @ReviewCount, @HasUrl, @PhotoCount,@IsFeatured, @AttrCodes, @SicBudgetIDs, @lastRank
  200.  
  201. SELECT * FROM #MyTotals
  202.  
  203. DROP TABLE #Bes
  204. DROP TABLE #MyTotals
  205.  
  206. END
clone this paste RAW Paste Data