我正在嘗試撰寫一個存盤程序，通過提供Year、ColumnName和Dimension.
該Year被列是表中的“年”的前兩個字符傳遞varchar“17abcde”：等。
這是我的代碼：
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
當我執行存盤程序時，它嘗試從單詞“year”而不是從 year 列的值中獲取前兩個字符。
uj5u.com熱心網友回復：
如果您將字串變數傳遞給子字串，則它將獲取變數中 Value 的子字串并回傳它。因此，如果您真正想要的是在變數中傳遞列名并獲取該列中值的子字串，那么我建議您最好嘗試使用查詢字串。
就像這樣
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
uj5u.com熱心網友回復：
不，你不能那樣做。SQL 通常必須知道您正在使用哪些列進行優化。一種方法是在 where 子句中包含每個列名：
DELETE TOP (@Dimension)
FROM [dbo].[TestDelete]
WHERE (@ColumnName = 'DateReceived' and SUBSTRING(DateReceived, 1, 2) = @Year)
OR (@ColumnName = 'DateSent' and SUBSTRING(DateSent, 1, 2) = @Year)
另一種方法是使用 sp_executesql 并將洗掉創建為字串：
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;
不過，我認為這兩者都不是一個好主意。為什么需要傳遞列名？
uj5u.com熱心網友回復：
你應該使用sp_executesql. 如果您需要已洗掉行的計數，請從動態腳本中回傳它。
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/350607.html
標籤：sql sql-server 查询语句