Using Query Analyser to create a stored procedure

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

General SQL Server Forums

New to SQL Server Programming

Using Query Analyser to create a stored procedure

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2008-06-19 : 11:01:47

Experts;

I am use the Query analyser to buidl a Master Stored Procedure that would go through the Stored Procedures in the DB to recreate them again

I am tesing this in the Query Analyser

This is the section of my code to create on of the Stored Procedures in the DataBase


select @SQL = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[IMGSAttributes'+@TableName+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
select @SQL = @SQL +  char(13) + char(10) + 'drop procedure [dbo].[IMGSAttributes'+@TableName+']'
select @SQL = @SQL + char(13) + char(10) + 'GO'+ char(13) + char(10) +'SET QUOTED_IDENTIFIER ON'+ char(13) + char(10) +'GO'+ char(13) + char(10) +'SET ANSI_NULLS ON'+ char(13) + char(10) +'GO'+ char(13) + char(10) +'CREATE PROCEDURE dbo.IMGSAttributes'+@TableName
select @SQL = @SQL + char(13) + char(10) + '@pIndex varchar(50)'+ char(13) + char(10) + 'AS'+ char(13) + char(10) +'SET CONCAT_NULL_YIELDS_NULL OFF'+ char(13) + char(10) +'Select '+@PrimaryColumName + ' as GMKey,'+''''+@TableName +''' as TABLENAME, '+@ColText
select @SQL = @SQL + ' from '+@TableName+' where ' +@PrimaryColumName +' = @pIndex'
select @SQL = @SQL +  char(13) + char(10) + 'RETURN '+  char(13) + char(10) +'GO '+  char(13) + char(10) +' SET QUOTED_IDENTIFIER OFF '+  char(13) + char(10) +'GO'+  char(13) + char(10) +'SET ANSI_NULLS ON '+  char(13) + char(10) +'GO'

exec(@SQL)

When I run this I got some errors:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'GO'.
Server: Msg 111, Level 15, State 1, Line 8
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@pIndex'.
Server: Msg 178, Level 15, State 1, Line 15
A RETURN statement with a return value cannot be used in this context.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'GO'.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-19 : 11:04:55

You wont be able to use GO , while running using Dynamic sql.

Chirag

http://www.chirikworld.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 11:06:34

Replace the Exec(@Sql) with Print(@sql) and see what string its printing out.

mmalaka
Starting Member

33 Posts

Posted - 2008-06-19 : 11:19:06

quote:
Originally posted by visakh16

Replace the Exec(@Sql) with Print(@sql) and see what string its printing out.

it is printing this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IMGSAttributesCFM_CLASS_SWIMMING_POOLS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IMGSAttributesCFM_CLASS_SWIMMING_POOLS]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.IMGSAttributesCFM_CLASS_SWIMMING_POOLS
@pIndex varchar(50)
AS
SET CONCAT_NULL_YIELDS_NULL OFF
Select PRIMARYINDEX as GMKey,'CFM_CLASS_SWIMMING_POOLS' as TABLENAME, 'Facility Name ' + cast(Facility_Name as Char) + ' ' +'Building No ' + cast(Building_Number as Char) + ' ' +'Street ' + cast(Street_Name as Char) + ' ' +'District ' + cast(Postal_District as Char) + ' ' +'Committee Area ' + cast(Committee_Area as Char) + ' ' +'Sub Class ' + cast(SubClass as Char) + ' ' +'Phone No ' + cast(Phone_No as Char) + ' ' +'URL ' + cast(Url as Char) + ' ' as Details from CFM_CLASS_SWIMMING_POOLS where PRIMARYINDEX = @pIndex
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

When I run this on a new Query it work fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources