SHARE
TWEET

Untitled

a guest Jan 21st, 2020 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [xworks-v2]
  2. GO
  3. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryCanCreate]    Script Date: 1/21/2020 11:17:38 AM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. CREATE proc [process].[ProcessTypeCategoryCanCreate]   
  9.     @CategoryLabel varchar(20)
  10. as
  11. begin
  12.    
  13.  
  14. declare @msg varchar(100) = '', @hasCategoryLabel bit
  15.  
  16.     select  @hasCategoryLabel = case when count(*) > 0 then 1 else 0 end
  17.     from    [process].[ProcessTypeCategory]
  18.     where   CategoryLabel = @CategoryLabel
  19.  
  20.     if @hasCategoryLabel = 1
  21.     begin
  22.         set @msg = 'processCategory.validation-hasCategoryLabel'   
  23.     end
  24.  
  25.     select @msg Msg
  26. end
  27. GO
  28. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryCanUpdate]    Script Date: 1/21/2020 11:17:38 AM ******/
  29. SET ANSI_NULLS ON
  30. GO
  31. SET QUOTED_IDENTIFIER ON
  32. GO
  33. CREATE proc [process].[ProcessTypeCategoryCanUpdate]
  34.     @CategoryLabel varchar(20),
  35.     @Id int
  36. as
  37. begin
  38.    
  39.  
  40. declare @msg varchar(100) = '', @hasCategoryLabel bit
  41.  
  42.     select  @hasCategoryLabel = case when count(*) > 0 then 1 else 0 end
  43.     from    [process].[ProcessTypeCategory]
  44.     where   CategoryLabel = @CategoryLabel
  45.     and Id != @Id
  46.  
  47.     if @hasCategoryLabel = 1
  48.     begin
  49.         set @msg = 'processCategory.validation-hasCategoryLabel'   
  50.     end
  51.  
  52.     select @msg Msg
  53. end
  54. GO
  55. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryCreate]    Script Date: 1/21/2020 11:17:38 AM ******/
  56. SET ANSI_NULLS ON
  57. GO
  58. SET QUOTED_IDENTIFIER ON
  59. GO
  60. CREATE procedure [process].[ProcessTypeCategoryCreate]
  61.     @ParentCategoryId int,
  62.     @CategoryLabel varchar(20),
  63.     @CategoryDescription varchar(250),
  64.     @Filename nvarchar(128)
  65. as
  66. begin
  67.     INSERT INTO [process].[ProcessTypeCategory]
  68.                ([ParentCategoryId]
  69.                ,[CategoryLabel]
  70.                ,[CategoryDescription]
  71.                ,[Filename])
  72.          VALUES
  73.                (@ParentCategoryId
  74.                ,@CategoryLabel
  75.                ,@CategoryDescription
  76.                ,@Filename)
  77.     declare @id int = (select SCOPE_IDENTITY())
  78.  
  79.     select @id Id  
  80. end
  81. GO
  82. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryDelete]    Script Date: 1/21/2020 11:17:38 AM ******/
  83. SET ANSI_NULLS ON
  84. GO
  85. SET QUOTED_IDENTIFIER ON
  86. GO
  87. Create procedure [process].[ProcessTypeCategoryDelete]
  88.     @Id int
  89. as
  90. begin
  91.     delete  [process].[ProcessTypeCategory]
  92.     where   Id = @Id   
  93. end
  94. GO
  95. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryForFE]    Script Date: 1/21/2020 11:17:38 AM ******/
  96. SET ANSI_NULLS ON
  97. GO
  98. SET QUOTED_IDENTIFIER ON
  99. GO
  100. CREATE proc [process].[ProcessTypeCategoryForFE]
  101. as
  102. begin
  103.     SELECT   [Id]
  104.             ,[ParentCategoryId]
  105.             ,[CategoryLabel]
  106.             ,[CategoryDescription]
  107.             ,[Filename]
  108.     FROM    [process].[ProcessTypeCategory]
  109. end
  110. GO
  111. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryGetAll]    Script Date: 1/21/2020 11:17:38 AM ******/
  112. SET ANSI_NULLS ON
  113. GO
  114. SET QUOTED_IDENTIFIER ON
  115. GO
  116.  
  117. CREATE procedure [process].[ProcessTypeCategoryGetAll]
  118.     @Id int,
  119.     @PageNumber int = 1,
  120.     @PageSize int = 10
  121. as
  122. begin
  123.     declare @offset int = (@PageNumber - 1) * @PageSize
  124.  
  125.     select      Id, ParentCategoryId, CategoryLabel,  CategoryDescription, Filename
  126.     from        [process].ProcessTypeCategory
  127.     where       (Id = @Id or @Id is null)
  128.     order by Id
  129.  
  130.     OFFSET @offset rows fetch next @PageSize rows ONLY
  131. end
  132. GO
  133. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryGetByParams]    Script Date: 1/21/2020 11:17:38 AM ******/
  134. SET ANSI_NULLS ON
  135. GO
  136. SET QUOTED_IDENTIFIER ON
  137. GO
  138. CREATE proc [process].[ProcessTypeCategoryGetByParams]
  139.     @Id int
  140. as
  141. begin
  142.     SELECT      [Id]
  143.                ,[ParentCategoryId]
  144.                ,[CategoryLabel]
  145.                ,[CategoryDescription]
  146.                ,[Filename]
  147.       FROM  [process].[ProcessTypeCategory]
  148.       where Id = @Id
  149. end
  150. GO
  151. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryGetNotMe]    Script Date: 1/21/2020 11:17:38 AM ******/
  152. SET ANSI_NULLS ON
  153. GO
  154. SET QUOTED_IDENTIFIER ON
  155. GO
  156. CREATE procedure [process].[ProcessTypeCategoryGetNotMe]
  157.      @Id int
  158. as
  159. begin
  160.     select      Id, ParentCategoryId, CategoryLabel, CategoryDescription, Filename
  161.     from        process.ProcessTypeCategory
  162.     where       (@Id = 0 or Id != @Id)
  163. end
  164. GO
  165. /****** Object:  StoredProcedure [process].[ProcessTypeCategorySearch]    Script Date: 1/21/2020 11:17:38 AM ******/
  166. SET ANSI_NULLS ON
  167. GO
  168. SET QUOTED_IDENTIFIER ON
  169. GO
  170. CREATE procedure [process].[ProcessTypeCategorySearch]
  171.     @PageNumber int = 1,
  172.     @PageSize int = 10,
  173.     @SearchTerm varchar(max) = null
  174. as
  175. begin
  176.     declare @offset int = (@PageNumber - 1) * @PageSize
  177.  
  178.     select      COUNT(*) OVER() TotalRows,
  179.                 @PageNumber PageNumber,
  180.                 CEILING((COUNT(*) OVER()) / CAST(@PageSize AS float)) TotalPages,  
  181.                 *
  182.     from        process.ProcessTypeCategory
  183.     where       replace(
  184.                     lower(
  185.                         isnull(CategoryLabel, '')   + ISNULL(CategoryDescription, '')      
  186.                     ),
  187.                 ' ', '')  Collate SQL_Latin1_General_CP1253_CI_AI
  188.  
  189.                 like '%' + replace(@SearchTerm,' ','') + '%'  Collate SQL_Latin1_General_CP1253_CI_AI
  190.  
  191.     order by CategoryLabel
  192.  
  193.     OFFSET @offset rows fetch next @PageSize rows ONLY
  194. end
  195. GO
  196. /****** Object:  StoredProcedure [process].[ProcessTypeCategoryUpdate]    Script Date: 1/21/2020 11:17:38 AM ******/
  197. SET ANSI_NULLS ON
  198. GO
  199. SET QUOTED_IDENTIFIER ON
  200. GO
  201. CREATE procedure [process].[ProcessTypeCategoryUpdate]
  202.     @Id int,
  203.     @ParentCategoryId int,
  204.     @CategoryLabel varchar(20),
  205.     @CategoryDescription varchar(250),
  206.     @Filename nvarchar (128)
  207. as
  208. begin
  209.     update  [process].[ProcessTypeCategory]
  210.     set     [ParentCategoryId] = @ParentCategoryId,
  211.             [CategoryLabel] = @CategoryLabel,
  212.             [CategoryDescription] = @CategoryDescription,
  213.             [Filename] = case
  214.                          when @Filename = '' then
  215.                             [Filename]
  216.                          else
  217.                             @Filename
  218.                         end
  219.     where   Id = @Id
  220.  
  221. end
  222. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top