How to reuse a SQL statement

I needed to create a stored procedure that would call a somewhat complex SELECT statement using T-SQL full text search (CONTAINS/CONTAINSTABLE). The result set would be filtered differently depending on parameter values passed into the stored procedure.  I didn't want to copy and paste the full SELECT statement into multiple IF/THEN/ELSE blocks.

I considered two options:

1. Create a shared stored procedure to return a "base" recordset, and then call that stored procedure from other stored procedures that would further filter the "base" recordset
2. Generate dynamic SQL within an IF/THEN/ELSE structure and then execute the dynamic SQL at the end.

I found this link which provided great information on how to pass a result set from one stored procedure to another:
http://www.sommarskog.se/share_data.html

I decided against Option #1 because the best option involves the creation of a temporary table.  I was worried about performance and also maintaining the declaration of the temporary table in the calling stored procedures in the event of a design change to the source table.

This link provided enough information for me to generate and execute dynamic SQL:
http://stackoverflow.com/questions/7992837/sub-query-in-dynamic-query-sql-server-2008

Here is the final design of my stored procedure:

   1: USE [MyDatabase]

   2: GO

   3: /****** Object:  StoredProcedure [dbo].[SearchMessages]    Script Date: 12/05/2013 14:37:41 ******/

   4: SET ANSI_NULLS ON

   5: GO

   6: SET QUOTED_IDENTIFIER ON

   7: GO

   8:  

   9: CREATE PROCEDURE [dbo].[SearchMessages]

  10:     @limit INT = -1

  11:     ,@and_search_terms NVARCHAR(4000) = ''

  12:     ,@or_search_terms NVARCHAR(4000) = ''

  13:     ,@xml_include_message_types XML = ''

  14:     ,@xml_exclude_message_types XML = ''

  15: AS

  16: BEGIN

  17:     SET NOCOUNT ON;

  18:     

  19:     DECLARE @idoc int;

  20:     DECLARE @select_msg_type_prefix NVARCHAR(40) = 'SELECT A.* FROM ( ';

  21:     DECLARE @select_msg_type_suffix NVARCHAR(4) = ') A ';

  22:     DECLARE @select_msg_type_where NVARCHAR(150);

  23:     DECLARE @select_msg_type_orderby NVARCHAR(100) = ' ORDER BY A.select_rank, A.rank DESC, A.title';

  24:     DECLARE @select_final NVARCHAR(MAX);

  25:  

  26:     DECLARE @select_rnk_msg NVARCHAR(1000) =

  27:         '

  28:         SELECT RNK_MSG.* 

  29:         FROM (

  30:             SELECT m.*, 1 as select_rank, km.rank

  31:             FROM dbo.MY_TABLE m

  32:             INNER JOIN

  33:                 CONTAINSTABLE(dbo.MY_TABLE, (msg_text, location, title), @and_search_terms) as km

  34:                 ON m.msg_id = km.[KEY]

  35:             UNION

  36:             SELECT m.*, 2 as select_rank, km.rank

  37:             FROM dbo.MY_TABLE m

  38:             INNER JOIN

  39:                 CONTAINSTABLE(dbo.MY_TABLE, (msg_text, location, title), @or_search_terms) as km

  40:                 ON m.msg_id = km.[KEY]

  41:             WHERE m.msg_id NOT IN (SELECT msg_id FROM dbo.MY_TABLE WHERE CONTAINS((msg_text, location, title), @and_search_terms))

  42:         ) RNK_MSG

  43:         WHERE RNK_MSG.status != ''N'' AND RNK_MSG.delete_ind != 1

  44:         '

  45:         ;

  46:         

  47:     IF @limit >= 0

  48:     BEGIN

  49:         set @select_msg_type_prefix = 'SELECT TOP (@limit) A.* FROM ( ';

  50:     END

  51:         

  52:     IF @xml_include_message_types IS NOT NULL AND @xml_include_message_types.exist('/MessageTypes') = 1

  53:     BEGIN

  54:         EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_include_message_types

  55:  

  56:         SET @select_msg_type_where =

  57:         'WHERE A.message_type_id IN (SELECT * FROM OPENXML (@idoc, ''/MessageTypes/Type'', 1) WITH (ID char(1)))';

  58:     END

  59:     ELSE IF @xml_exclude_message_types IS NOT NULL AND @xml_exclude_message_types.exist('/MessageTypes') = 1

  60:     BEGIN

  61:         EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_exclude_message_types

  62:  

  63:         SET @select_msg_type_where =

  64:         'WHERE A.message_type_id NOT IN (SELECT * FROM OPENXML (@idoc, ''/MessageTypes/Type'', 1) WITH (ID char(1)))';

  65:     END

  66:     ELSE

  67:     BEGIN

  68:         SET @select_msg_type_where = '';

  69:     END

  70:  

  71:     SET @select_final = @select_msg_type_prefix + @select_rnk_msg + @select_msg_type_suffix + @select_msg_type_where + @select_msg_type_orderby;

  72:     EXEC sp_executesql @select_final

  73:         ,N'@idoc INT,@limit INT,@and_search_terms NVARCHAR(4000),@or_search_terms NVARCHAR(4000)'

  74:         ,@idoc=@idoc,@limit=@limit,@and_search_terms=@and_search_terms,@or_search_terms=@or_search_terms;

  75:  

  76: END