>I would recomend not to use 'sp_' prefix for UDFs because it's used by system stored procedures.

Excellent advice Sergey ... you beat me to it by a couple of hours (you East Coasters always get a jump on us <g>).

In our application, we even avoid the use of the "usp_" prefix ... that way the users of our app can create their own Stored Procs if they want to and use that prefix.

We use the convention of "bsp_" for Basic Stored Procs (for CRUD-types of Stored Procs) and "csp_" for all additional custom stuff.

~~Bonnie





>
>

>CREATE PROCEDURE usp_DropProc
>	@SProcName sysname
>AS
>
>	DECLARE @XType char(2)
>
>	SELECT @XType = XType
>		FROM dbo.sysobjects
>		WHERE Name = @SProcName
>	
>	IF @XType IS NULL
>		RAISERROR ('Object ''%s'' does not exists', 16, 1, @SProcName)
>	ELSE IF @XType IN ('FN', 'IF', 'TF')
>		EXEC('DROP FUNCTION ' + @SProcName)
>	ELSE IF @XType = 'P'
>		EXEC('DROP PROCEDURE ' + @SProcName)
>	ELSE
>		RAISERROR ('Object ''%s'' is not Procedure or Function', 16, 1, @SProcName)
>

>>I put togethere a simpl sp_DropProc routine. I'd like your thoughts, if you please.
>>It worked fine for both of these:
>>
>>

>>EXEC sp_DropProc 'fn_EncryptDecryptString'
>>EXEC sp_DropProc 'sp_AddSCUser'
>>

>>
>>

>>

>>USE SC
>>GO
>>--DROP PROC sp_DropProc
>>
>>CREATE PROCEDURE sp_DropProc
>>	@SProcName nvarchar(100)
>>
>>AS
>>
>>  DECLARE @XType char(2)
>>  SET @XType = ''
>>
>>  SELECT @XType = XType
>>    FROM dbo.sysobjects
>>    WHERE Name = @SProcName
>>	
>>  IF @XType = 'FN'
>>    BEGIN	
>>      EXEC('DROP FUNCTION ' + @SProcName)
>>    END
>>
>>  IF @XType = 'P'
>>    BEGIN	
>>      EXEC('DROP PROCEDURE ' + @SProcName)
>>    END
>>
>>GO
>>