| 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 OBJECTIF OBJECT_ID('AP_Test') IS NULLBEGIN EXEC( "CREATE PROCEDURE AP_Test AS SELECT 'Message' = 'Created object stub for: AP_Test'" )ENDGOIF OBJECT_ID('dbo.AP_Test') IS NOT NULLBEGIN 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 >>>'ENDSET QUOTED_IDENTIFIER OFFgoSET ANSI_NULLS ONgoIF OBJECT_ID('dbo.AP_Test') IS NOT NULLBEGIN 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 >>>'ENDgoCREATE PROCEDURE AP_Test ASDECLARE @au_id INTEGERDECLARE @address VARCHAR(100)SELECT @address = '' IF @au_id IS NOT NULL BEGIN SELECT @address = address FROM authors WHERE au_id = @au_id ENDSELECT @addressSET ANSI_NULLS OFFgoSET QUOTED_IDENTIFIER OFFgoThanks 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 OFFgoSET ANSI_NULLS ONgoIF OBJECT_ID('dbo.AP_Test') IS NOT NULLBEGIN 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 >>>'ENDgoCREATE PROCEDURE AP_Test @au_id VARCHAR(12) ,@address VARCHAR(100)ASSELECT @address = '' IF @au_id IS NOT NULL BEGIN SELECT @address = address FROM authors WHERE au_id = @au_id ENDSELECT @addressSET ANSI_NULLS OFFgoSET QUOTED_IDENTIFIER OFFgoEXEC AP_Test "427-17-2319", '3410 Blonde St.'GOprocedure output3410 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 NULLBEGINEXEC( "CREATE PROCEDURE AP_Test AS SELECT 'Message' = 'Created object stub for: AP_Test'" )ENDGOno need for the dynamic sql (and you shouldn't use double quotes as a string delimiter).IF OBJECT_ID('AP_Test') IS NOT NULLdrop proc AP_TestgoCREATE 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 NULLyou can useIf exists(select * from sysobjects where name='AP_test' and xtype='p')MadhivananFailing 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 anywayKristen |
 |
|
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
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 |
|
|
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
|
|
 |
|
|
|