Can I pass a column name to a substring function using a variable?-CodePudding

I'm trying to write a stored procedure to delete rows from a table by providing Year, ColumnName and a Dimension.

The Year is passed by the first two characters of the column 'year' of the table that is a varchar like: "17abcde".

This is my code:

ALTER PROCEDURE SP_SvuotaTabella
    @Year INT,
    @ColumnName VARCHAR(32),
    @Dimension INT
AS
    BEGIN TRY
    BEGIN TRAN EmptyTable

        WHILE @Dimension <> 0
        BEGIN
            DELETE TOP (@Dimension) 
            FROM [dbo].[TestDelete]
            WHERE SUBSTRING(@ColumnName, 1, 2) = @Year

            SET @Dimension = @@rowcount 
        END

        COMMIT TRAN EmptyTable
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION SvuotaTabella;

        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_STATE() AS ErrorState,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
END CATCH

When I exec the stored procedure, it tries to get the first two characters from the word "year" and not from the values of the year column.

CodePudding user response：

if you pass a string variable to the substring, then it'll take the substring of the Value within the variable and return it. So if what you actually want is to pass the column name in the variable and take the substring of the value in that column, then I suggest you should better try with a Query String.

something just like this

DECLARE @Qry VARCHAR(MAX)

WHILE @Dimension <> 0
BEGIN
    
    SELECT @Qry = 'DELETE TOP (' CAST(@Dimension AS VARCHAR(20)) ') 
    FROM [dbo].[TestDelete]
    WHERE SUBSTRING(' @ColumnName ', 1, 2) = ' @Year

    EXEC(@Qry)  

    SET @Dimension = @@rowcount 
END

CodePudding user response：

No, you cannot do it that way. SQL normally has to know what columns you are working with for optimization. One way to do it would be to Have each column name in the where clause:

DELETE TOP (@Dimension) 
FROM  [dbo].[TestDelete]
WHERE (@ColumnName = 'DateReceived' and SUBSTRING(DateReceived, 1, 2) = @Year)
   OR (@ColumnName = 'DateSent' and SUBSTRING(DateSent, 1, 2) = @Year)

Another way would be to use sp_executesql and create the delete as a string:

declare @sql nvarchar(max) = concat('DELETE TOP(@Dimension) ',
    'FROM [dbo].[TestDelete] ',
    'WHERE SUBSTRING(', @ColumnName, ', 1, 2) = @Year');
exec sp_executesql @sql,
    N'@Dimension int', N'Year int',
    @Dimension = @Dimension, @Year = @Year;

I don't think either is a good idea though. Why do you need the column name to be passed?

CodePudding user response：

You should use sp_executesql. If you need a count of deleted rows, return it from the dynamic script.

DECLARE @SQLString NVARCHAR(max) = N'DELETE TOP (@Dimension) FROM [dbo].[TestDelete] WHERE SUBSTRING('   QUOTENAME(@ColumnName)   ', 1, 2) = @Year; SET @cnt=@@rowcount;'
DECLARE @ParmDefinition NVARCHAR(4000)= N'@Year INT, @Dimension INT, @cnt INT OUTPUT';
DECLARE @cnt int;
-- ..
exec sp_executesql @SQLString, @ParmDefinition,
    @Dimension = @Dimension, @Year = @Year, @cnt = @cnt OUTPUT;