SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored procedure question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 02/08/2006 :  17:06:54  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/08/2006 :  17:38:07  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/09/2006 :  01:21:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/09/2006 :  03:55:29  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/09/2006 :  14:36:25  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/09/2006 :  14:40:51  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/09/2006 :  15:53:15  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/09/2006 :  15:54:22  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/09/2006 :  18:08:57  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/09/2006 :  21:49:21  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/10/2006 :  08:06:32  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/10/2006 :  11:47:36  Show Profile  Reply with Quote
My recovery procedures are perfect though ...

Kristen
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/10/2006 :  11:53:42  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/10/2006 :  13:39:33  Show Profile  Reply with Quote
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000