时间戳

首页 > 解决方案 > 我在 SQL Server 中的存储过程出错

sql - 我在 SQL Server 中的存储过程出错

问题描述

我尝试编写将检查的存储过程：

  1. 如果表和列存在，那么我们写入数据
  2. 如果表不存在，那么我们创建它并写入数据
  3. 如果表存在，那么我们检查现有列并将数据写入现有列

这是代码：

CREATE PROCEDURE [dbo].[my_proc] @file_path VARCHAR(1000) = NULL
    ,@file_type VARCHAR(1000) = NULL
    ,@file_name VARCHAR(1000) = NULL
    ,@table_name VARCHAR(1000)
AS
BEGIN
    DECLARE @sql NVARCHAR(4000);

    SET @table_name = '[dbo].[' + @table_name + ']'
    SET @sql = '
    DECLARE @msg VARCHAR(max)

    --if column and table exist
    IF (
            EXISTS (
                SELECT *
                FROM sys.objects
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                ) --табличка
            AND EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                    AND (
                        NAME = '' path_file ''
                        AND NAME = '' file_type ''
                        AND NAME = '' file_name ''
                        )
                )
            ) --поля
    BEGIN
        INSERT INTO ' + @table_name + ' (
            file_path
            ,file_type
            ,file_name
            )
        VALUES (
            ''' + isnull(@file_path, '') + '''
            ,''' + isnull(@file_type, '') + '''
            ,''' + isnull(@f ile_name, '') + '''
            )
    END

    --if table not exist
    IF NOT EXISTS (
            SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
            )
    BEGIN
        CREATE TABLE ' + @table_name + ' (
            id INT NOT NULL identity(1, 1) PRIMARY KEY
            ,file_path TEXT
            ,file_name TEXT
            ,file_type TEXT
            )
    END

    --if table exist
    IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
            )
    BEGIN
        --check for columns in table
        --file_path
        IF NOT EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                    AND (
                        NAME = ''path_file''
                        OR NAME = ''file_path''
                        )
                )
        BEGIN
            PRINT (''NOT COLUMN path_file'')
        END

        --file name
        IF NOT EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', '' U '')
                    AND NAME = '' file_name ''
                )
        BEGIN
            PRINT ('' NOT COLUMN file_name '')
        END

        --file_type
        IF NOT EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                    AND (
                        NAME = ''file_type''
                        OR NAME = ''file_type''
                        )
                )
        BEGIN
            PRINT (''NOT COLUMN file_type'')
        END
        ELSE
            INSERT INTO ' + @table_name + ' (
                file_path
                ,file_name
                ,file_type
                )
            VALUES (
                ''' + isnull(@file_path, '') + '''
                ,''' + isnull(@file_name, '') + '''
                ,''' + isnull(@f ile_type, '') + '''
                )
    END';

    EXEC (@sql);
END

已经存在的表有问题，但没有我需要的列

请帮帮我

标签： sqlsql-serverstored-proceduresdynamic

解决方案

我试图运行你的脚本，唯一的错误是一个未声明的变量。

你正在用一个空格写“@file_name”。

顺便说一句，我建议您拆分代码以阅读。我尽量少弄乱。

CREATE PROCEDURE [dbo].[my_proc] 
    @file_path VARCHAR(1000) = NULL
    ,@file_type VARCHAR(1000) = NULL
    ,@file_name VARCHAR(1000) = NULL
    ,@table_name VARCHAR(1000)
AS
BEGIN
    DECLARE @sql NVARCHAR(4000);
    DECLARE @msg VARCHAR(max);

    SET @table_name = '[dbo].[' + @table_name + ']'
    SET @sql = '
    --If table not exist then we create it
    IF NOT EXISTS (
            SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N''' + @table_name + ''')
                AND type IN (N''U'')
            )
    BEGIN
        CREATE TABLE ' + @table_name + ' (
            id INT NOT NULL identity(1, 1) PRIMARY KEY
            ,file_path TEXT
            ,file_name TEXT
            ,file_type TEXT
            )
    END';

    EXEC (@sql);

    SET @sql = '
    --If table exist then we check existing columns and we write data into exists columns
    IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
            )
    BEGIN
        PRINT (''TABLE found'')

        --check for columns in table
        --file_path
        IF NOT EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', '' U '')
                    AND (
                        NAME = '' path_file ''
                        OR NAME = ''file_path''
                        )
                )
        BEGIN
            PRINT (''NOT COLUMN path_file'')
        END

        --file name
        IF NOT EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                    AND NAME = ''file_name''
                )
        BEGIN
            PRINT (''NOT COLUMN file_name'')
        END

        --file_type
        IF NOT EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                    AND (
                        NAME = ''file_type''
                        OR NAME = ''file_type''
                        )
                )
        BEGIN
            PRINT (''NOT COLUMN file_type'')
        END
        ELSE
            INSERT INTO ' + @table_name + ' (
                file_path
                ,file_name
                ,file_type
                )
            VALUES (
                ''' + isnull(@file_path, '') + '''
                ,''' + isnull(@file_name, '') + '''
                ,''' + isnull(@file_type, '') + '''
                )
    END';

    EXEC (@sql);

    SET @sql = '
    --If table and columns exists then we write data
    IF (
            EXISTS (
                SELECT *
                FROM sys.objects
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                ) --табличка
            AND EXISTS (
                SELECT *
                FROM sys.columns
                WHERE object_id = OBJECT_ID(''' + @table_name + ''', ''U'')
                    AND (
                        NAME = ''path_file''
                        AND NAME = ''file_type''
                        AND NAME = ''file_name''
                        )
                )
            ) --поля
    BEGIN
        INSERT INTO ' + @table_name + ' (
            file_path
            ,file_type
            ,file_name
            )
        VALUES (
            ''' + isnull(@file_path, '') + '''
            ,''' + isnull(@file_type, '') + '''
            ,''' + isnull(@file_name, '') + '''
            )
    END';

    EXEC (@sql);
END

推荐阅读