2

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...

opensas
  • 60,462
  • 79
  • 252
  • 386

1 Answers1

1

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
Rob
  • 45,296
  • 24
  • 122
  • 150
  • @opensas, I deliberately didn't do that here so that the core of the script would be visible without extraneous "noise", but sure, go right ahead =) – Rob Apr 02 '11 at 08:40