USP_DATALIST_COMMENTS

Returns Comments records, based on input parameters.

Parameters

Parameter	Parameter Type	Mode	Description
@REFERENCETYPECODE	tinyint	IN	Reference Type
@REFERENCEID	uniqueidentifier	IN	Reference ID
@SEARCHKEYWORD	nvarchar(100)	IN	Search Keyword

Definition

Copy



CREATE procedure dbo.USP_DATALIST_COMMENTS
(
  @REFERENCETYPECODE TinyInt,
  @REFERENCEID uniqueidentifier,
  @SEARCHKEYWORD nvarchar(100) = null
) with execute as owner
as
  set nocount on;

  DECLARE @sqlCommand nvarchar(4000)
  DECLARE @ParmDefinition nvarchar(1000)

  SET @ParmDefinition = '@REFERENCETYPECODE TinyInt, @REFERENCEID uniqueidentifier, @SEARCHKEYWORD nvarchar(100)'

  SET @sqlCommand = 
            'select 
        COMMENT_TEXT.[ID],
        COMMENT_TEXT.[COMMENTTEXT],
        COMMENT_TEXT.[AUTHORNAME],
        COMMENT_TEXT.[AUTHORCONSTITUENTID],
        COMMENT_TEXT.[EVENTID],
        COMMENT_TEXT.[REFERENCEID],
        COMMENT_TEXT.[REFERENCETYPECODE],
        COMMENT_TEXT.[DATEADDED]
            from 
        dbo.COMMENT_TEXT (NOLOCK)
  where
  (REFERENCETYPECODE = @REFERENCETYPECODE) and
  (REFERENCEID = @REFERENCEID)'

  IF @SEARCHKEYWORD IS NOT null
  BEGIN
    SET @sqlCommand = @sqlCommand + ' AND ( COMMENTTEXT LIKE ''%' + @SEARCHKEYWORD + '%'' OR AUTHORNAME LIKE ''%' + @SEARCHKEYWORD + '%'')'
  END

  SET @sqlCommand = @sqlCommand + ' ORDER BY DATEADDED desc'

  exec sp_executesql @sqlCommand, @ParmDefinition, @REFERENCETYPECODE = @REFERENCETYPECODE, @REFERENCEID = @REFERENCEID, 
                            @SEARCHKEYWORD = @SEARCHKEYWORD