views:

497

answers:

5

EDIT: the function creation was missing, sorry about that

I have a T-SQL request that goes:

DECLARE @IsSomething bit
SET @IsSomething = 0
IF /some tests/ SET @IsSomething = 1
EXEC('
CREATE FUNCTION IsSomething ()
RETURNS bit
AS
BEGIN
    RETURN ' + @IsSomething + '
END')

Of course if I run it twice I get

There is already an object named 'IsSomething ' in the database.

How would I do something like this:

IF EXIST @IsSomething DESTROY @IsSomething // (Pseudo bad code)

Thanks!

A: 

Seems analogous to any other language where you try to declare the same global variable multiple times. Normally we don't write:

var a;
a = 0;
...
...
undefine a;
a = 1;
...
...
undefine a;
a = 2;

Seems to me like you just need to be aware of the structure of the code you are writing.

I wouldn't consider a declared variable to be a "database object", BTW. But your question makes more sense if for some reason you do.

le dorfier
It's a script that I used to update a database, and there might be pre-existing stuff there, so I need to drop them. It's fairly common to do this with tables, so why not with this? I'm not sure about the "database object" language, sorry if it was misleading
marcgg
A "define" statement has a specific limited scope. A defined variable shouldn't outlive the execution of a single script, nor interact with other scripts unless called as subscripts.
le dorfier
The thing is that it's adding something in the database, so it's supposed to stay there. I don't want to loose it.
marcgg
Ok I get it thanks to Ben M. Sorry my question was confusing :\ It's updated now with the real problem.
marcgg
The problem is that you IsSomething (the function) and @IsSomething (the variable) are two distinct things with no relationship to each other.
le dorfier
+4  A: 
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[IsSomething]') AND OBJECTPROPERTY(id, N'IsFunction') = 1) 

DROP function IsSomething
GO
Chris McCall
the drop part is not working. If my variable is declared like this: DECLARE @IsSomething bit, how should I drop it?
marcgg
edited to reflect reality.Note that I didn't use the IF OBJECT_ID('YourObject', 'ObjectsType') IS NOT NULL style because it's too sloppy and would drop other object types you may not have intended.
Chris McCall
How's that, Chris? (In my answer, for example.)
Ben M
+1 to Ben, what of these two answers is the cleanest?
marcgg
btw both of Chris' and Ben's anwsers worked.
marcgg
from Robert Paulson's answer I'd say that Chris is right about his answer being the best one, but I'm not sure.
marcgg
Either one works--I'm not sure what Chris means about other object types being dropped, since you can't DROP FUNCTION a table, for example.
Ben M
In the effort to construct a reply, I've discovered I have no idea what the Hell I meant by that.
Chris McCall
Yeah, that looks fine, I think we're all slugging it out for that last 1% of correctedness
Chris McCall
+1  A: 

Like this

IF OBJECT_ID('YourObject', 'ObjectsType') IS NOT NULL DROP <ObjectsType> [YourObject]
RBarryYoung
Yes. Because A) "<ObjectsType>" has to be replaced with the objects type name, ie., "DROP TABLE [..]" or "DROP INDEX [..], and B) "bit" is not an Object Type, it is a domain type and you cannot drop them.
RBarryYoung
Ah, I see now, you've changed your question so that it is clear that you are trying to DROP a variable. You can't do that, you have to start another session/batch. (In scripts, GO will do this)
RBarryYoung
+2  A: 

The answer to your edited question is:

if object_id('IsSomething', 'fn') is not null drop function IsSomething
go
Ben M
+1  A: 

The template, from Visual Studio 2008 Add -> Stored Procedure Script is

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name')
    BEGIN
     DROP  Procedure  Stored_Procedure_Name
    END

GO

CREATE Procedure Stored_Procedure_Name
/*
    (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
    )

*/
AS


GO

/*
GRANT EXEC ON Stored_Procedure_Name TO PUBLIC

GO
*/

For a Procedure, Sql Server Management Studio gives the following script to drop

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

likewise for a Function it's generated script is

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_GetXyz]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_GetXyz]

I've mostly seen the latter forms (2-line versions) in most codebases I've worked on, and there's no need to declare a variable.

Robert Paulson