February 14, 2012 at 9:05 am
I have 2 database exmp: db1 and db2
I have a procedure named proc1 which is already present in db1
now I want to check if proc1 exists in db2 or not.
if not then create and but if exists then leave it as it is.
i am using the steps like:
IF object_id('Proc1')is null
begin
create procedure abc
------
------
end
now when i am running the above code i am getting errors.
but when i m selecting and runing only the create procedure part and skipping the "if" condition then the procedure is runing.
how can i achive my purpose.?
February 14, 2012 at 9:20 am
This is taken from BOL “The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.”
You have few ways to work around it. One way is to check if the procedure exists. If it is drop it, and then start a new batch (by using the word go in the script) and create the procedure. If you chose this way, you have to make sure to add the permissions to the script. Another way is to use dynamic SQL. Bellow is a code that shows both ways:
--First alternativ - drop the procecure
IF object_id('Proc1')is not null
drop procedure Proc1
go
create procedure Proc1
as
select getdate()
go
---Second alternativ. Use dynamic SQL
IF object_id('Proc1')is null
begin
exec ('create procedure Proc1
as
select getdate()')
end
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2012 at 9:28 am
Actually I am doing this in a script and i am doing this for 50 procedures and each procedure is more then 50 lines. So, dynamic query will not be good idea. And I dont want to drop the procedure if it is alrady there in database.
Any other option.?
February 14, 2012 at 9:34 am
Can you post the text of the error you are receiving?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 14, 2012 at 9:36 am
Phil Parkin (2/14/2012)
Can you post the text of the error you are receiving?
Ignore this - Adi has already spotted the issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 14, 2012 at 9:41 am
scottichrosaviakosmos (2/14/2012)
Actually I am doing this in a script and i am doing this for 50 procedures and each procedure is more then 50 lines. So, dynamic query will not be good idea. And I dont want to drop the procedure if it is alrady there in database.Any other option.?
Use Adi's 'second alternative' (to ensure that the proc exists) followed by an ALTER PROCEDURE (not dynamic) to fix up its definition.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 14, 2012 at 9:48 am
You need to be a bit more creative with your T-SQL batch to achieve what you want :-D:
if (OBJECT_ID('p_a') is null) SET NOEXEC OFF;
GO
if (OBJECT_ID('p_a') is not null) SET NOEXEC ON;
GO
create proc p_a
as
begin
select 'a'
end
GO
IF (OBJECT_ID('p_b') is null) SET NOEXEC OFF
GO
IF (OBJECT_ID('p_b') is not null) SET NOEXEC ON;
GO
create proc p_b
as
begin
select 'b'
end
GO
SET NOEXEC OFF;
GO
February 14, 2012 at 9:55 am
That's ugly, but also taught me something, thanks! 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 14, 2012 at 10:02 am
Phil Parkin (2/14/2012)
That's ugly, but also taught me something, thanks! 🙂
Ugly is a very subjective view.
In my opinion it's quite extravagant 🙂
It's usefull when you want to stop batch script execution on error...
February 14, 2012 at 10:23 am
Eugene Elutin – Very creative. Learned my lesson for today:-)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2012 at 11:05 am
Just been playing with this and I think that Eugene's solution may have a slight flaw. SET NOEXEC OFF needs to be executed between creates to ensure that the subsequent IF conditions are evaluated correctly in the event that SET NOEXEC ON was executed previously:
if (object_id('p1') is not null)
set noexec on
else
set noexec off
go
create procedure p1
as
select 0
go
set noexec off
go
if (object_id('p2') is not null)
set noexec on
else
set noexec off
go
create procedure p2
as
select 0
go
set noexec off
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 14, 2012 at 1:13 pm
Absolutely! It was cut-&-paste issue. Didn't put the second one after unconditional set noexec off.
if (OBJECT_ID('p_a') is null) SET NOEXEC OFF;
GO
if (OBJECT_ID('p_a') is not null) SET NOEXEC ON;
GO
create proc p_a
as
begin
select 'a'
end
GO
SET NOEXEC OFF;
GO
IF (OBJECT_ID('p_b') is null) SET NOEXEC OFF
GO
IF (OBJECT_ID('p_b') is not null) SET NOEXEC ON;
GO
create proc p_b
as
begin
select 'b'
end
GO
SET NOEXEC OFF;
GO
-- and so on...
after looking into a bit more carefully you can see that only unconditional set noexec off is required, following conditional set noexec on.
February 16, 2012 at 7:40 am
yup it worked.. tks puffy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply