| Author |
 Topic  |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
 Posted - 02/08/2006 : 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 - 02/08/2006 : 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
United Kingdom
12543 Posts |
Posted - 02/08/2006 : 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
India
22461 Posts |
Posted - 02/09/2006 : 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
United Kingdom
22191 Posts |
Posted - 02/09/2006 : 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
United Kingdom
12543 Posts |
Posted - 02/09/2006 : 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
United Kingdom
22191 Posts |
Posted - 02/09/2006 : 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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/09/2006 : 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 |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 02/10/2006 : 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
United Kingdom
22191 Posts |
Posted - 02/10/2006 : 11:47:36
|
My recovery procedures are perfect though ...
Kristen |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 02/10/2006 : 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
United Kingdom
22191 Posts |
Posted - 02/10/2006 : 13:39:33
|
|
|
|
| |
Topic |
|
Stored procedure question
