CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
VafaK
VafaK

Reputation: 524

SQL Server stored procedure to empty specific tables

I have a stored procedure which is supposed to truncate specific tables based on their names.

Tables which do not have "A" as the first character in their names must be truncated.

The problem is that this code doesn't work! Please help

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[return_data]
  @DBName varchar(100)
AS

declare @i int, @tc int
set @i = 1

DECLARE @sql NVARCHAR(100)
SET @sql = 'USE ' + QUOTENAME(@DBName) + '

DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables
'
EXECUTE(@sql)

OPEN cur

declare @tbl_name nvarchar(100)
declare @first_char char(1)

FETCH NEXT FROM cur INTO @tbl_name

WHILE @@FETCH_STATUS = 0 BEGIN

    set @first_char = SUBSTRING(@tbl_name, 0, 1)

    set @sql = 'DELETE FROM ' + QUOTENAME(@tbl_name)
    if (@first_char != 'A')
    begin
      EXECUTE(@sql)
    end

    FETCH NEXT FROM cur INTO @tbl_name
END

CLOSE cur    
DEALLOCATE cur

return

Upvotes: 0

Views: 85

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

The problem is with the SUBSTRING function. The starting position is a 1-based ordinal, not 0-based. Try

SUBSTRING(@tbl_name, 1, 1)

or

LEFT(@tbl_name, 1)

Also, I suggest you make it a habit of schema-qualifying table names to avoid ambiguity.

Upvotes: 2

Related Questions