CODEWITHSUNDEEP
sql-server-2005stored-proceduresrecompile
opensas
opensas

Reputation: 63395

How to force SQL Server 2005 objects to be recompiled NOW

According to this running sp_recompile forces the object to be recompiled the next time that it is run

I need it to be recompiled the moment I run the sp-recompile command, mainly to check for syntax errors and existence of objects on which the stored procedure depends.

-- on sql 2008 there's sys.sp_refreshsqlmodule module...

Upvotes: 2

Views: 525

Answers (1)

Rob
Rob

Reputation: 45761

Probably the simplest way to do this is to re-deploy the stored procedure, which would (as far as I'm aware) remove the need to recompile the procedure.

Something along these lines:

SET @ProcedureName = 'SampleProcedure'

CREATE TABLE #ProcedureContent (Text NVARCHAR(MAX))
INSERT INTO #ProcedureContent
EXEC sp_helptext @ProcedureName

DECLARE @ProcedureText NVARCHAR(MAX)
SET @ProcedureText = ''

SELECT @ProcedureText = @ProcedureText + [Text] FROM #ProcedureContent 

EXEC ('DROP PROCEDURE ' + @ProcedureName);
EXEC (@ProcedureText)

DROP TABLE #ProcedureContent

Upvotes: 1

Related Questions