Dropping Stored Procedures

Question

Dropping Stored Procedures

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Satya-806309 Grasshopper Points: 14 More actions · Answer 1

Actually I want to compare two databases for Ex: TestDB to productionDB.

then we have to get all sp's from testdb and i want to create alter statement for those sp's which are existing productiondb and create statement for sp's not existing in productiondb and have to run this sql script in productiondb env.

Can you please suggest query to run this job.

Thanks David

Developer

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Your wasting a lot of time to maybe get it wrong. I don't normally advocate 3rd party software, but RedGates SQL Compare will do everything you just asked for... and more.

I do have to say that promoting differences between a Dev and Prod environment is probably a bad idea... who says that all the differences are correct or ready to promote? It is "Death by SQL"... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Naples SSC-Addicted Points: 415 More actions · Answer 3

Jeff Moden (7/7/2008)
David Naples (7/7/2008)
You can't do this directly, as the CREATE PROCEDURE or ALTER PROCEDURE statement has to be the first in the batch.
Ummm... maybe not directly, but you can create dynamic SQL with "GO" and all the good stuff... and, in SQL Server 2005, you can do it without a cursor or an explicit loop of any kind... I still want to know what the alteration would be and I still think it would be a good idea of the requester started a new post.

Actually, this isn't 100% true. If you are building SELECT statements, then yes, you will get multiple result sets. However, if you are performing actions (inserts, updates, etc.) then you will receive a message stating "Incorrect syntax near 'GO'." This is on SQL 2005 SP3, but the behavior is unchanged since I first started working with it (SQL 6.5).

There is also no difference between using the EXEC function or the [sp_executesql] SP in this regard. Both produce the same results.

Cogiko ergo sum (I geek, therefore I am).

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

David Naples (7/8/2008)
Actually, this isn't 100% true. If you are building SELECT statements, then yes, you will get multiple result sets. However, if you are performing actions (inserts, updates, etc.) then you will receive a message stating "Incorrect syntax near 'GO'." This is on SQL 2005 SP3, but the behavior is unchanged since I first started working with it (SQL 6.5).
There is also no difference between using the EXEC function or the [sp_executesql] SP in this regard. Both produce the same results.

I think that the point was, you can use either EXEC(...) or sp_executsql to explicitly create, nest and implicitly terminate batchs, which allows you to satisfy all of the "First in batch" requirements and still dynamically do everything that Satya asked for, withOut using cursors.

I know because I do it all the time.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

David Naples (7/8/2008)
Jeff Moden (7/7/2008)
David Naples (7/7/2008)
You can't do this directly, as the CREATE PROCEDURE or ALTER PROCEDURE statement has to be the first in the batch.
Ummm... maybe not directly, but you can create dynamic SQL with "GO" and all the good stuff... and, in SQL Server 2005, you can do it without a cursor or an explicit loop of any kind... I still want to know what the alteration would be and I still think it would be a good idea of the requester started a new post.
Actually, this isn't 100% true. If you are building SELECT statements, then yes, you will get multiple result sets. However, if you are performing actions (inserts, updates, etc.) then you will receive a message stating "Incorrect syntax near 'GO'." This is on SQL 2005 SP3, but the behavior is unchanged since I first started working with it (SQL 6.5).
There is also no difference between using the EXEC function or the [sp_executesql] SP in this regard. Both produce the same results.

Oh shoot... you're right, David... I flat forgot about that. Thank you for the catch! 🙂

Folks, the following example shows what David is talking about and I appologize... I forgot that it only works with Selects... (the "GO", I mean...)

DECLARE @SQL VARCHAR(MAX)

--===== This works...

SET @SQL = '

SELECT * FROM Master.dbo.spt_Values

GO

SELECT * FROM Master.dbo.spt_Values

GO

'

EXEC (@SQL)

--===== This doesn't...

SET @SQL = '

CREATE TABLE #MyHead (RowNum INT IDENTITY(1,1), SomeString VARCHAR(20))

GO

INSERT INTO #MyHead

(SomeString)

SELECT ''One'' UNION ALL

SELECT ''Two'' UNION ALL

SELECT ''Three''

GO

UPDATE #MyHead

SET SomeString = ''Modified''

WHERE RowNum = 2

GO

SELECT *

FROM #MyHead

GO'

EXEC (@SQL)

First bit of code runs because it only has the SELECTs in with the "GO"s. Second bit of code fails because it has more than just SELECTS. If you remove all the "GO"s from the second bit of code, then it will work.

Like I said... sorry for error on my part. David is spot on!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

I gotta admit, I didn't realize that GO worked as anything other than a client tool batch seperator.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

David Naples SSC-Addicted Points: 415 More actions · Answer 7

rbarryyoung (7/8/2008)
I gotta admit, I didn't realize that GO worked as anything other than a client tool batch seperator.

In the case of multiple SELECT statements, the "GO" separator is simply ignored (unless, of course, you have specified something else as your batch separator, in which case the "GO" keyword simply generates a syntax error).

Ya gotta love the fact SQL has so many answers that have an "EXCEPT" clause ... 🙂

Cogiko ergo sum (I geek, therefore I am).