stored procedure template

[iccsi]

I use SSMS template to create stored procedure for SQL Server 2000.

SSMS has following code to drop stored procedure exists,

-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'
AND SPECIFIC_NAME = N'<Procedure_Name, sysname, Procedure_Name>'
)

Enterprise Manager uses following code to drop stored procedures
exists,


IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'MyStroedProc'
AND type = 'P')
DROP PROCEDURE MyStoredProc



I checked that sysobjects is in user database and
INFORMATION_SCHEMA.ROUTINES is in master database system table.

It seems that SSMS uses SQL Server 2008 format to save stored
procedure in master database and SQL Server 2000 not.

It looks like I can not use SSMS stored procedure template to create
SQL Server 2000 stored prrocedure. SSMS template only for SQL Server
2005 and later.

Your help and information is great appreciated,

Regards,

iccsi

[Tony Toews]

Post by iccsi
SSMS has following code to drop stored procedure exists,

Once I've created the stored procedure the first time using CREATE
PROCEDURE I change the word CREATE to ALTER. While I haven't done a
lot of them that works so far.

Tony

[kodyaz]

Post by iccsi
It seems that SSMS uses SQL Server 2008 format to save stored
procedure in master database and SQL Server 2000 not.
It looks like I can not use SSMS stored procedure template to create
SQL Server 2000 stored prrocedure. SSMS template only for SQL Server
2005 and later.

Hello iccsi, you're right about the change in system views SQL2k and
later versions.
The Enterprise Manager with its later version named SSMS, uses the new
system views that are introduced with SQL2k5

Perhaps, it might be easier to create a table values function that
takes the procedure name as a parameter and returns the name of the
object.
Then if it is in SQL2k you can use sysobjects and if it's later
versions you can use the Information_schema views

So you'll have only a single point to alter according to the SQL
Server instance version.

BTW, the old method is working without a problem on SQLServer 2005 and
later versions as I remember.

Eralper
--
Microsoft Community Contributor
http://www.kodyaz.com

[Erland Sommarskog]

Post by iccsi
I checked that sysobjects is in user database and
INFORMATION_SCHEMA.ROUTINES is in master database system table.

INFORMATION_SCHEMA is in any database, although it is only listed in
master. There is some magic there. The SSMS template should work fine.

However, I recommend that you use this pattern in your SP files:

IF object_id('dbo.my_proc') IS NULL
EXEC('CREATE PROCEDURE dbo.myproc AS PRINT 12')
go
ALTER PROCEDURE dbo.myproc @par1 ...
-- Real meat follows here.

[Gene Wirchenko]

On Thu, 12 Apr 2012 23:54:10 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by iccsi
I checked that sysobjects is in user database and
INFORMATION_SCHEMA.ROUTINES is in master database system table.

INFORMATION_SCHEMA is in any database, although it is only listed in
master. There is some magic there. The SSMS template should work fine.
IF object_id('dbo.my_proc') IS NULL
EXEC('CREATE PROCEDURE dbo.myproc AS PRINT 12')
go
-- Real meat follows here.

Uh, why? What does this get you?

And if it is a good idea, wouldn't printing a stub string be even
better?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
On Thu, 12 Apr 2012 23:54:10 +0200, Erland Sommarskog

Post by Erland Sommarskog
IF object_id('dbo.my_proc') IS NULL
EXEC('CREATE PROCEDURE dbo.myproc AS PRINT 12')
go
-- Real meat follows here.

Uh, why? What does this get you?

Minimal amount of noise without having to bother about ALTER or CREATE.
The method that uses DROP will lose permissions and dependency information.

Post by Gene Wirchenko
And if it is a good idea, wouldn't printing a stub string be even
better?

Not sure I understand. "PRINT 12" is just a dummy statement. You can
use SET NOCOUNT ON, RETURN or whatever.

[Gene Wirchenko]

On Fri, 13 Apr 2012 08:48:55 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
On Thu, 12 Apr 2012 23:54:10 +0200, Erland Sommarskog

Post by Erland Sommarskog
IF object_id('dbo.my_proc') IS NULL
EXEC('CREATE PROCEDURE dbo.myproc AS PRINT 12')
go
-- Real meat follows here.

Uh, why? What does this get you?

Minimal amount of noise without having to bother about ALTER or CREATE.
The method that uses DROP will lose permissions and dependency information.

That last sentence was useful and may save my bacon some day.
Thank you.

Post by Erland Sommarskog

Post by Gene Wirchenko
And if it is a good idea, wouldn't printing a stub string be even
better?

Not sure I understand. "PRINT 12" is just a dummy statement. You can
use SET NOCOUNT ON, RETURN or whatever.

I was thinking of something that indicates that the real
procedure did not take should the stub get executed.
print "myproc() stub procedure executed"
throw someerrornumber
or similar.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
I was thinking of something that indicates that the real
procedure did not take should the stub get executed.
print "myproc() stub procedure executed"
throw someerrornumber

Requires nesting quotes. Too much hassle. :-)

[Tony Toews]

On Fri, 13 Apr 2012 21:40:03 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
I was thinking of something that indicates that the real
procedure did not take should the stub get executed.
print "myproc() stub procedure executed"
throw someerrornumber

Requires nesting quotes. Too much hassle. :-)

Spoken like an experienced and efficient person. <smile>

Tony

[Gene Wirchenko]

On Sat, 14 Apr 2012 13:30:58 -0600, Tony Toews

Post by Tony Toews
On Fri, 13 Apr 2012 21:40:03 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
I was thinking of something that indicates that the real
procedure did not take should the stub get executed.
print "myproc() stub procedure executed"
throw someerrornumber

Requires nesting quotes. Too much hassle. :-)

Spoken like an experienced and efficient person. <smile>

The other way is to work out a skeleton for the code and fill in
the blank(s). I like the idea of stub error messages.

Sincerely,

Gene Wirchenko

[Tony Toews]

On Fri, 13 Apr 2012 08:48:55 +0200, Erland Sommarskog

Post by Erland Sommarskog
The method that uses DROP will lose permissions and dependency information.

Ahh, that's good to know.

Thanks,Tony