خودکارسازی متغیرهای SQL: تولید خودکار نام و نوع ستون برای جدول در SQL Server

ایجاد متغیرهای SQL برای هر ستون یک جدول می‌تواند کاری زمان‌بر و تکراری باشد، به خصوص زمانی که با جداول بزرگ یا نیاز به اعلان و مقداردهی متغیرها به صورت پویا سر و کار داریم. در این مقاله، روش‌هایی برای خودکارسازی این فرآیند با استفاده از SQL پویا و نمای `INFORMATION_SCHEMA.COLUMNS` ارائه می‌شود تا زمان شما صرفه‌جویی شده و خطاهای احتمالی کاهش یابد.

فرض کنید جدولی با نام `tbl_MyTable` داریم که شامل ستون‌های `Col1`, `Col2`, `Col3` و غیره است. نیاز داریم تا برای هر ستون یک متغیر SQL با نامی مشابه و نوع داده‌ای مطابق با ستون مربوطه ایجاد کنیم و سپس به آن متغیر مقادیر ستون را اختصاص دهیم.

یک راه حل پایه و دستی برای اعلان و مقداردهی یک متغیر به این صورت است:

DECLARE @col1 VARCHAR(50);
SELECT @col1 = 'Col1';

این روش برای تعداد کمی ستون قابل قبول است، اما زمانی که تعداد ستون‌ها زیاد می‌شود، غیر عملی خواهد بود.

برای خودکارسازی اعلان متغیرها، می‌توانیم از نمای `INFORMATION_SCHEMA.COLUMNS` استفاده کنیم که اطلاعات مربوط به ستون‌های تمام جداول پایگاه داده را در اختیار ما قرار می‌دهد. با ترکیب این اطلاعات و SQL پویا، می‌توانیم دستورات `DECLARE` را به صورت خودکار تولید کنیم.

در ابتدا، باید نام جدول مورد نظر را در یک متغیر ذخیره کنیم و سپس با استفاده از `INFORMATION_SCHEMA.COLUMNS` نام و نوع داده هر ستون را استخراج کنیم. سپس این اطلاعات را برای ساخت دستور `DECLARE` به کار می‌بریم.

DECLARE @TableName VARCHAR(255) = 'tbl_MyTable';
DECLARE @Tsql NVARCHAR(MAX) = '';

SELECT @Tsql = @Tsql + '
DECLARE @' + c.COLUMN_NAME + ' ' + c.DATA_TYPE + 
    CASE
        WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') THEN '(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))) + ')'
        WHEN c.DATA_TYPE IN ('decimal', 'numeric') THEN '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + ')'
        WHEN c.DATA_TYPE IN ('varbinary', 'binary') THEN '(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))) + ')'
        ELSE ''
    END + ';'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
ORDER BY c.ORDINAL_POSITION;

PRINT @Tsql;
-- EXEC sp_executesql @Tsql;

در قطعه کد بالا، ما یک متغیر رشته‌ای به نام `@Tsql` را ساخته‌ایم که حاوی تمامی دستورات `DECLARE` مورد نیاز است. تابع `PRINT` این دستورات را نمایش می‌دهد و برای اجرای واقعی آن‌ها، باید خط `EXEC sp_executesql @Tsql;` را از حالت کامنت خارج کنید. این اسکریپت نوع داده‌های متغیر را با طول، دقت و مقیاس مناسب بر اساس نوع ستون تنظیم می‌کند.

در مرحله بعدی، برای خودکارسازی مقداردهی متغیرها، می‌توانیم به همین ترتیب از SQL پویا استفاده کنیم. پس از اعلان متغیرها، نیاز داریم تا مقادیر ستون‌ها را از جدول به متغیرهای مربوطه اختصاص دهیم.

این اسکریپت متغیرهای SQL را اعلان می‌کند و سپس مقادیر ستون‌ها را از جدول `tbl_MyTable` به متغیرهای متناظر آن‌ها اختصاص می‌دهد. این کار تنها یک ردیف از جدول را انتخاب می‌کند، زیرا برای هر ستون، به یک مقدار نیاز داریم.

DECLARE @TableName VARCHAR(255) = 'tbl_MyTable';
DECLARE @Tsql_Declaration NVARCHAR(MAX) = '';
DECLARE @Tsql_Assignment NVARCHAR(MAX) = '';

-- ساخت دستورات اعلان متغیر
SELECT @Tsql_Declaration = @Tsql_Declaration + '
DECLARE @' + c.COLUMN_NAME + ' ' + c.DATA_TYPE + 
    CASE
        WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') THEN '(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))) + ')'
        WHEN c.DATA_TYPE IN ('decimal', 'numeric') THEN '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + ')'
        WHEN c.DATA_TYPE IN ('varbinary', 'binary') THEN '(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))) + ')'
        ELSE ''
    END + ';'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
ORDER BY c.ORDINAL_POSITION;

-- ساخت دستورات انتساب مقدار به متغیرها
SELECT @Tsql_Assignment = @Tsql_Assignment + '
SELECT @' + c.COLUMN_NAME + ' = ' + QUOTENAME(c.COLUMN_NAME) + ' FROM ' + QUOTENAME(@TableName) + ';' -- فرض بر انتخاب اولین ردیف است.
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
ORDER BY c.ORDINAL_POSITION;

-- ترکیب و نمایش (یا اجرا)
PRINT @Tsql_Declaration + CHAR(13) + CHAR(10) + @Tsql_Assignment;
-- EXEC sp_executesql (@Tsql_Declaration + @Tsql_Assignment);

در اسکریپت بالا، `QUOTENAME()` برای اطمینان از صحت نام ستون‌ها و جدول (به خصوص اگر نام‌ها دارای کاراکترهای خاص یا فاصله باشند) استفاده شده است.

با ترکیب اعلان و مقداردهی متغیرها، می‌توانیم یک اسکریپت جامع‌تر ایجاد کنیم که کل فرآیند را به صورت یکجا مدیریت کند. این روش به شما امکان می‌دهد که به سرعت متغیرهای لازم را ایجاد کرده و مقادیر اولیه آن‌ها را از ستون‌های جدول دریافت کنید.

اسکریپت نهایی، هم اعلان متغیرها و هم انتساب مقادیر به آن‌ها را در یک بلاک SQL پویا انجام می‌دهد:

DECLARE @TableName VARCHAR(255) = 'tbl_MyTable';
DECLARE @Tsql_Full NVARCHAR(MAX) = '';

-- ساخت دستورات اعلان متغیر
SELECT @Tsql_Full = @Tsql_Full + '
DECLARE @' + c.COLUMN_NAME + ' ' + c.DATA_TYPE + 
    CASE
        WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') THEN '(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))) + ')'
        WHEN c.DATA_TYPE IN ('decimal', 'numeric') THEN '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + ')'
        WHEN c.DATA_TYPE IN ('varbinary', 'binary') THEN '(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))) + ')'
        ELSE ''
    END + ';'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
ORDER BY c.ORDINAL_POSITION;

-- اضافه کردن دستورات انتساب مقدار
SELECT @Tsql_Full = @Tsql_Full + '
SELECT ' + STRING_AGG('@' + c.COLUMN_NAME + ' = ' + QUOTENAME(c.COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY c.ORDINAL_POSITION) + ' FROM ' + QUOTENAME(@TableName) + ';'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName;

PRINT @Tsql_Full;
-- EXEC sp_executesql @Tsql_Full;

در این نسخه نهایی، از تابع `STRING_AGG()` (در SQL Server 2017 و بالاتر) برای ترکیب دستورات `SELECT` به شکلی کارآمدتر استفاده شده است. این روش تمام متغیرها را در یک دستور `SELECT` مقداردهی می‌کند و کد را تمیزتر و بهینه‌تر نگه می‌دارد. اگر از نسخه‌های قدیمی‌تر SQL Server استفاده می‌کنید، باید از روش‌های `FOR XML PATH` یا مشابه آن برای تجمیع رشته‌ها استفاده کنید.

با استفاده از این تکنیک‌ها، می‌توانید فرآیند کار با متغیرهای SQL را برای ستون‌های جدول به صورت قابل توجهی سرعت بخشیده و بهره‌وری خود را افزایش دهید.