SQL server genrate script Include If exists option

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL server genrate script Include If exists option

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-18 : 13:17:55

I am trying to generate SQL SP scripts(SQL 2005), i have close to 250 SP's.

I do see one option Include if not exists(Under Choose scripting options.).

But i don't see "If exists option", is there a way to have Drop procedure if exists included in teh generate script.

Thank you very much for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 13:21:03

you can get same effect using if not exists option it puts code like

if not exists(...)
create proc...
go
alter proc...

which is in effect same

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-18 : 13:23:12

You can use this.

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[ProcedureName]') AND type in (N'P', N'PC'))

DROP PROCEDURE [ProcedureName]

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-18 : 13:25:26

Check the If Exists option and the Script Drop Option. Here's a sample of what it does on my dev machine:

USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[ListEmployeesByDepartment] Script Date: 02/18/2009 13:23:26 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListEmployeesByDepartment]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ListEmployeesByDepartment]
GO
/****** Object: StoredProcedure [dbo].[ListEmployeesByDepartment] Script Date: 02/18/2009 13:23:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListEmployeesByDepartment]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[ListEmployeesByDepartment]
@departmentName nvarchar(50)
as

select c.LastName,c.LastName
from Person.Contact c
inner join HumanResources.Employee e
on c.ContactID=e.ContactID
inner join HumanResources.EmployeeDepartmentHistory h
on e.EmployeeID=h.EmployeeID
inner join HumanResources.Department d
on h.DepartmentID=d.DepartmentID
where d.Name=@departmentname
and h.EndDate is null
order by 1'
END
GO

Mike
"oh, that monkey is going to pay"

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-18 : 14:08:00

Visakh,
The problem is if exists it does'nt drop.
reason is lot of these sp's changed.
I have almost 250 SP's, i can do it manually but very time consuming.

In sql server 2000, it has that option If exists drop procedure and update the server with the new SP.

quote:
Originally posted by visakh16

you can get same effect using if not exists option it puts code like

if not exists(...)
create proc...
go
alter proc...

which is in effect same

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources