How to run script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to run script

Author

Topic

malikjaved
Starting Member

21 Posts

Posted - 2008-02-04 : 10:43:00

HI Guys.

Some how i create a varible into which i am abling to fetch a complete script of creating procedure or procedures . now i m hving problem how to run this script for varible, what i am doing is

Exec @SQL

this @sql has complete script to that create procedure.
i am receiving this error msg
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 24
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near 'GO'.

here is the value inside the @SQL

IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tempdb]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[tempdb]
GO

CREATE PROCEDURE [dbo].[ModifiedProcedure]

--@specific_name nvarchar(max),
@DateFrom datetime,

@DateTo datetime,

@SQL varchar(max) OUTPUT

AS

--Declare variables
DECLARE @DropSQL nvarchar(max),

@CreateSQL nvarchar(max)

SET @SQL = ''

--Get a list of procedures to drop and create

SELECT

'IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tempdb]'') AND type in (N''P'', N''PC''))

DROP PROCEDURE [dbo].[tempdb]' AS [DropProcedure],

cast(routine_definition as nvarchar(max)) as [CreateProcedure]

INTO #ReplaceProcedures

FROM

INFORMATION_SCHEMA.ROUTINES

WHERE

routine_type = 'PROCEDURE'

AND LAST_ALTERED >= @DateFrom

AND LAST_ALTERED <= @DateTo

--Create the Script

DECLARE ProcedureScripingCursor CURSOR FOR

SELECT DropProcedure, CreateProcedure

FROM #ReplaceProcedures

OPEN ProcedureScripingCursor

FETCH NEXT FROM ProcedureScripingCursor

INTO @DropSQL, @CreateSQL

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = @SQL + @DropSQL

SET @SQL = @SQL + CHAR(10) + 'GO' + CHAR(10)

SET @SQL = @SQL + @CreateSQL

SET @SQL = @SQL + CHAR(10) + 'GO' + CHAR(10)

FETCH NEXT FROM ProcedureScripingCursor

INTO @DropSQL, @CreateSQL

END

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 11:11:30

EXEC (@SQL)

E 12°55'05.25"
N 56°04'39.16"

malikjaved
Starting Member

21 Posts

Posted - 2008-02-05 : 04:39:40

Thanks for reply, but it's still not working,

acutally the problem is that, you have complete script(drop/creating procedure) into a varible...

i am hving problem..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:23:33

What does print @SQL result?

Madhivanan

Failing to plan is Planning to fail

malikjaved
Starting Member

21 Posts

Posted - 2008-02-05 : 09:48:38

@sql is varible and it's just got the complete script of creating procedure,
you can see the value in the first post...top of the page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources