need helps - 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 2000 Forums

Transact-SQL (2000)

need helps

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-06-17 : 16:55:29

what i want to do is checking if the stored procedure has been tested in the test server before it can be run in production server.
i wrote the below code and i got an error message :
Server: Msg 111, Level 15, State 1, Line 21
'CREATE PROCEDURE' must be the first statement in a query batch.

i can not work around it unless i put the "Create stored procedure " part in a variable (@sql = 'create procedure....' which i don't want to do , is there any other way? Thanks

USE [NorthWind]
DECLARE @Instance varchar(50), @DatabaseName varchar(30), @ObjectName varchar(50)
DECLARE @spCount int,@spStatement nvarchar(4000)
declARE @SQL NVARCHAR(4000)
SET @Instance = '[' + @@SERVERNAME + ']'

SET @DatabaseName = '[NorthWind]'
SET @ObjectName = 'p_PRODUCTS_GETPRODUCTS'

SET @sql= N'SELECT @SPCOUNT = COUNT(*) FROM [testserver].' + @DATABASENAME + '.DBO.SYSOBJECTS WHERE NAME =''' + @OBJECTNAME + ''''
EXECUTE sp_executesql @sql, N'@SPCOUNT int OUTPUT', @SPCOUNT = @SPCOUNT OUTPUT

IF @SPCOUNT = 0
BEGIN
RAISERROR('Required test in test server ', 16, 1)
return
END

ELSE
BEGIN
EXECUTE('IF EXISTS (SELECT id FROM sysobjects WHERE NAME = ''' + @ObjectName + ''' AND type = ''P'') DROP PROCEDURE ' + @ObjectName)
CREATE PROCEDURE p_PRODUCTS_GETPRODUCTS
AS SELECT * FROM PRODUCTS
end

----------this below works but i don't want to put the sql into a variable :

USE [NorthWind]
DECLARE @Instance varchar(50), @DatabaseName varchar(30), @ObjectName varchar(50)
DECLARE @spCount int,@spStatement nvarchar(4000)
declARE @SQL NVARCHAR(4000)
SET @Instance = '[' + @@SERVERNAME + ']'

SET @DatabaseName = '[NorthWind]'
SET @ObjectName = 'p_PRODUCTS_GETPRODUCTS'

SET @sql= N'SELECT @SPCOUNT = COUNT(*) FROM [testserver].' + @DATABASENAME + '.DBO.SYSOBJECTS WHERE NAME =''' + @OBJECTNAME + ''''
EXECUTE sp_executesql @sql, N'@SPCOUNT int OUTPUT', @SPCOUNT = @SPCOUNT OUTPUT

IF @SPCOUNT = 0
BEGIN
RAISERROR('Required test in test server ', 16, 1)
return
END

ELSE
begin
EXECUTE('IF EXISTS (SELECT id FROM sysobjects WHERE NAME = ''' + @ObjectName + ''' AND type = ''P'') DROP PROCEDURE ' + @ObjectName)
set @sql = 'CREATE PROCEDURE p_PRODUCTS_GETPRODUCTS as select * from product'
exec @SQL
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 17:02:54

You need to put a GO statement before your CREATE PROCEDURE in order for it to be in a new batch.

Tara

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-06-17 : 17:27:19

my purpose for this queries is - i don't want it create a stored procedure in production if it hasn't been run in test invironment - if i put a Go before the create procedure - then it will create a SP in PRODUCTION whether the sp has been or hasn't been run in TEST invironment -

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 17:32:20

I'm not sure why you don't have a proper test release where you are 100% certain that things have been tested.

But the only way to CREATE PROC is to have it be the first statement in a batch. So in order to do this in a larger script is to put a GO before it.

I think you need to take a look at your policies and procedures that detail releases to production rather than getting this script to work. Just because a stored procedure has been run once on a database, that doesn't mean it functioned as desired.

Tara

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-06-17 : 18:32:02

Thanks Tara!!!

this is a template for the developer to write their own stored procedures. They work in the development server- once everything is done in Development server - i will be testing in TEST server before moving them to production - i guess - i have to find other ways to make sure everything is done in TEST before moving to production.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources