There is an SQL Query which changes constantly due to a requirement change, and every time there is a change you need to edit the SP and recompile it. The setup exists in many other locations, so you need to contact respective DB authority for changes every time. In this case, you can store that query in a Text file. Let the SP read the Text file when it required, get the query, and execute it.
Query.sql file contains the constantly changing query.
CREATE PROCEDURE dbo.CASE2
AS
BEGIN
--DECLARATION SECTION
DECLARE @SQLQuery as NVARCHAR(4000)
--OTHER SQL STATEMENTS
--Temporary table for getting the query
CREATE TABLE #tmpQuery (Query NVARCHAR(4000))
--Gets the query from .sql to Temp table
BULK INSERT #tmpQuery
FROM '\\computer1\Thomson_Financial\QUERY.sql'
--Get Query to a Variable for execution
SELECT @SQLQuery = Query FROM #TMPQUERY
--Execute the query
EXECUTE sp_executesql @SQLQUERY
--OTHER SQL STATEMENTS
END
/ 22 
