我如何在SQL命令中使用单引号？

7 浏览
0 Comments

我如何在SQL命令中使用单引号？

这个问题已经在此处有答案：

可能是重复问题：

如何在SQL Server中转义单引号？

我有一个来自 这个链接 的脚本，它可以删除数据库中的所有内容。当我在此行上执行时，它会出错。

SET @statement = '
IF(@type = 'F') or (@type = 'C') or (@type = 'D') or (@type='F') or (@type='K')

原因是单引号。我想知道如何解决这个错误？

/*** drop (pretty much) everything before rebuilding the database ***/
DECLARE
    OBJECTS CURSOR FOR SELECT
        so.name,
        so.type,
        so.type_desc,
        p.name AS parentName
    FROM
        sys.objects AS so
    LEFT JOIN sys.objects AS p ON so.parent_object_id = p.object_id
    WHERE
        so.schema_id = 1
    ORDER BY
        CASE
    WHEN so.type = 'F' THEN
        0
    WHEN so.type = 'TR' THEN
        1
    WHEN so.type = 'U' THEN
        2
    WHEN so.type = 'F' THEN
        3
    ELSE
        4
    END OPEN OBJECTS DECLARE
        @name AS nvarchar (MAX) DECLARE
            @type AS nvarchar (2) DECLARE
                @type_desc AS nvarchar DECLARE
                    @parentName AS nvarchar (MAX) DECLARE
                        @statement AS nvarchar (MAX) FETCH NEXT
                    FROM
                        OBJECTS INTO @name,
                        @type,
                        @type_desc,
                        @parentName
                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                    SET @statement = '    IF(@type = ' F ')
BEGIN
PRINT ' DROPING FK : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' ALTER TABLE ' + @parentName + ' DROP CONSTRAINT ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' TR ')
BEGIN
PRINT ' DROPING TRIGGER : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP TRIGGER ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' U ')
BEGIN
PRINT ' DROPING TABLE : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP TABLE ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' FN ')
BEGIN
PRINT ' DROPING FUNCTION : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP FUNCTION ' + @name
EXECUTE(@statement)
END
ELSE
PRINT ' Didn 't drop object ' + @name + ' of type ' + @type + ' (' + @type_desc + ')'    FETCH NEXT
                    FROM
                        OBJECTS INTO @name,
                        @type,
                        @type_desc,
                        @parentName
                    END CLOSE OBJECTS DEALLOCATE OBJECTS

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments

在SQL中，单引号用于表示字符串字面量。
如果您需要明确插入单引号，则应使用双单引号('')

0
0 Comments

如果你想在预处理语句中使用单引号，需要用另一个单引号进行转义，例如，

SET @statement = 'world''s view';
SET @statement2 = 'world''s view';

从你上面的例子图案中可得到。

SET @statement = '
                IF(@type = ''F'') or (@type = ''C'')  or
                  (@type = ''D'') or (@type=''F'') or
                  (@type=''K'')'
-- the strings are all red.

0