How to drop folder within stored procedure?

Question

Hi, in training Encapsulate data transformations in a stored procedure it is said: As discussed previously, dropping an existing external table does not delete the folder containing its data files. You must explicitly delete the target folder if it exists before running the stored procedure, or an error will occur. In that case how to make this procedure idempotent? I could not find anything like "drop folder if exists" for the procedure.

Answer 1

Hi Filip Dzięcioł,

Welcome to Microsoft Q&A! You could refer Guoxiong's article and follow the steps one by one. I found another method in this forum, and you could also have a try. One note is the sp_refreshsqlmodule will fail if the change you made to the table type is a breaking change to that object, typically a procedure. Use sp_rename to rename the table type, I typically just add z to the beginning of the name. Create a new table type with the original name and any modification you need to make to the table type. Step through each dependency and run sp_refreshsqlmodule on it. Drop the renamed table type. I also had a try from my side as below: Copy

CREATE TYPE [dbo].[udttR] AS TABLE(  
[ProjectId] [bigint] NULL  
)  
  
CREATE PROCEDURE dbo.MyProcedure1  
  @tvp dbo.[udttR] READONLY  
AS  
BEGIN  
  SELECT [ProjectId] FROM @tvp;  
END  
GO  
  
CREATE PROCEDURE dbo.MyProcedure2  
AS  
BEGIN  
  DECLARE @tvp dbo.[udttR];  
END  
GO  
  
EXEC sys.sp_rename 'dbo.udttR', 'zudttR';  
  
CREATE TYPE [dbo].[udttR] AS TABLE(  
[ProjectId] [int] NULL  
)  
  
DECLARE @Name NVARCHAR(776);  
  
DECLARE REF_CURSOR CURSOR FOR  
SELECT referencing_schema_name + '.' + referencing_entity_name  
FROM sys.dm_sql_referencing_entities('dbo.udttR', 'TYPE');  
  
OPEN REF_CURSOR;  
  
FETCH NEXT FROM REF_CURSOR INTO @Name;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
    EXEC sys.sp_refreshsqlmodule @name = @Name;  
    FETCH NEXT FROM REF_CURSOR INTO @Name;  
END;  
  
CLOSE REF_CURSOR;  
DEALLOCATE REF_CURSOR;  
GO  
  
DROP TYPE dbo.zudttR;  
GO  

After of all, before it could be destructive to your database, you should test this on a development environment firstly. If the answer is helpful, please click "Accept Answer" and upvote it.