Stored procedure question - 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

General SQL Server Forums

New to SQL Server Programming

Stored procedure question

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-08 : 17:06:54

Hello,

I am not sure why the folowing is used? Is it used to check whether the procedure exists? And, when I use the following in the stored proc instead of the one below, I get a syntax error.

-- CREATE INITIAL OBJECT
IF OBJECT_ID('AP_Test') IS NULL
BEGIN
EXEC( "CREATE PROCEDURE AP_Test AS SELECT 'Message' = 'Created object stub for: AP_Test'" )
END
GO

IF OBJECT_ID('dbo.AP_Test') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.AP_Test
IF OBJECT_ID('dbo.AP_Test') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.AP_Test >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.AP_Test >>>'
END

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS ON
go
IF OBJECT_ID('dbo.AP_Test') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.AP_Test
IF OBJECT_ID('dbo.AP_Test') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.AP_Test >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.AP_Test >>>'
END
go
CREATE PROCEDURE AP_Test AS
DECLARE @au_id INTEGER
DECLARE @address VARCHAR(100)
SELECT @address = ''
IF @au_id IS NOT NULL
BEGIN
SELECT @address = address FROM authors WHERE au_id = @au_id
END
SELECT @address
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go

Thanks in advance!!!
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-08 : 17:24:06

Hello,

I believe since au_id is a varchar type, I cannot declare the procedure parameter name as an integer and convert the field au_id to integer? The following works fine:

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS ON
go
IF OBJECT_ID('dbo.AP_Test') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.AP_Test
IF OBJECT_ID('dbo.AP_Test') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.AP_Test >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.AP_Test >>>'
END
go
CREATE PROCEDURE AP_Test
@au_id VARCHAR(12)
,@address VARCHAR(100)
AS
SELECT @address = ''
IF @au_id IS NOT NULL
BEGIN
SELECT @address = address FROM authors WHERE au_id = @au_id
END
SELECT @address
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go

EXEC AP_Test "427-17-2319", '3410 Blonde St.'
GO

procedure output

3410 Blonde St.

Thanks in advance!!!
sqlnovice123

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-08 : 17:38:07

In your first post @au_id is a variable not a parameter and will always be null.

IF OBJECT_ID('AP_Test') IS NULL
BEGIN
EXEC( "CREATE PROCEDURE AP_Test AS SELECT 'Message' = 'Created object stub for: AP_Test'" )
END
GO
no need for the dynamic sql (and you shouldn't use double quotes as a string delimiter).

IF OBJECT_ID('AP_Test') IS NOT NULL
drop proc AP_Test
go
CREATE PROCEDURE AP_Test
AS
SELECT 'Message' = 'Created object stub for: AP_Test'
GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 01:21:34

Also instead of
IF OBJECT_ID('AP_Test') IS NOT NULL

you can use
If exists(select * from sysobjects where name='AP_test' and xtype='p')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 03:55:29

If you are going to do the "Stub" thing then you ought to use ALTER rather than DROP/CREATE.

I've often thought about using a STUB so that sysdepends can always find child-Sprocs - but if you then Drop/Create (rather than Alter) sysdepends will most likely get out of sync anyway

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-09 : 14:36:25

Problem with alter is that it doesn't update the creation date which is sometimes useful.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 14:40:51

Indeed. Toss-up then - preserve sysdepends, or have a decent crdate

We have a separate logging SProc that we include in all Stored Procedure Scripts - so we inherently have the Create Date [and history], so we can get away with using the Alter method.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 15:53:15

I cote for DROP AND CREATE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 15:54:22

I vote for that as well

I don't trust ALTER after reading Nigel's stuff...ok, so that was only for tables...still don't trust ALTER

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 18:08:57

ALTER has the benefit of NOT dropping the existing if the new replacement has syntax errors etc., and preserves the Permissions (good and bad, of course).

But I've mucked stuff up before where a "quick fix" has had a syntax error, and the DROP has killed off the existing code necessitating either a "quick re-fix"

or re-instating the original code [and hoping I have indeed found the source code for the correct version of the original code etc. etc.]

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 21:49:21

perserve the permissons...come up with something better...generate the grant before...I mean.....come on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-10 : 08:06:32

quote:
Originally posted by Kristen

But I've mucked stuff up before where a "quick fix" has had a syntax error, and the DROP has killed off the existing code

I can see it might be useful for people who make mistakes

.
or
I can see it might be useful for people who admit to making mistakes

.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 11:47:36

My recovery procedures are perfect though ...

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-10 : 11:53:42

And well tested

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 13:39:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources