Technical Article

Change Stored Procedure Automaticly

,

You can use sp_heltext comment to get stored procedure code , text and content.  This script helps to change particular text of stored procedure.

DECLARE @ProcedureName AS VARCHAR(100)
DECLARE @InKeyWord AS VARCHAR(100)
DECLARE @ChangeText AS VARCHAR(100)

SET @ProcedureName = 'uspGetBillOfMaterials' ---Adventure Works---
SET @InKeyWord = ''
SET @ChangeText = ''

--We create temp table
IF OBJECT_ID('tempdb..#TempProcScript') IS NOT NULL
DROP TABLE #TempProcScript
BEGIN 
CREATE TABLE #TempProcScript
(ProcTxt VARCHAR(500))
END

---Insert ProcScript to Table
INSERT INTO #TempProcScript
EXEC  dbo.sp_HelpText @ProcedureName

--Add variable all text of Proc

DECLARE @QueryLine AS VARCHAR(1000) 
DECLARE @QueryAll AS VARCHAR(8000) =''
DECLARE ChangeProc CURSOR FOR 
SELECT ProcTxt FROM #TempProcScript
OPEN ChangeProc
FETCH NEXT FROM ChangeProc INTO @QueryLine
WHILE @@FETCH_STATUS = 0   
BEGIN 

SET @QueryAll = @QueryAll + ISNULL(@QueryLine, '')

   FETCH NEXT FROM ChangeProc INTO @QueryLine   
END   
CLOSE ChangeProc   
DEALLOCATE ChangeProc 
 
---Replace ProcScript---

SELECT @QueryAll = REPLACE(@QueryAll,@InKeyWord,@ChangeText)
---Replace CREATE statement  to   ALTER---

SELECT @QueryAll = REPLACE(@QueryAll,'CREATE','ALTER')
EXEC (@QueryAll)

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating