动态SQL遇到的问题 [英] Issues encountered with dynamic SQL

问题描述

ALTER PROCEDURE [dbo].[Create_Subjects]
    @Subj_ID nvarchar(9)
AS
    DECLARE @First3Digits nvarchar(3);
    DECLARE @Result int;
    DECLARE @Sql nvarchar(max)

    -- Fetching the fiest 3 digits of the subject
    SET @First3Digits = SUBSTRING(@Subj_ID,1,3);

    -- Check if view is present or not
    IF EXISTS (SELECT 1 FROM sys.views WHERE Name = @First3Digits)
    BEGIN
        SET @Sql = 'select @Result = case when exists (select 1 from dbo.' + quotename(@First3Digits) + ' where SubjectName = ''' + @Subj_ID + ''') then 1 else 0 end';
        EXECUTE sp_executesql @Sql, N'@Subj_ID nvarchar(9), @Result bit out', @Subj_ID = @Subj_ID, @Result = @Result out; 
        -- checking if the subject is present in the view    
    END
    ELSE
    BEGIN
        -- Create a view as view doesn't exist
        SET @Sql = 'create view ' + @First3Digits 
                    + ' as 
    (select SubjectName from dbo.Subjects where SubjectName like '+@First3Digits+'%'+');';
        EXECUTE sp_executesql @Sql, N'@First3Digits nvarchar(3)', @First3Digits= @First3Digits;
        SET @Result = 0;
    END

    RETURN @Result
GO

这是执行存储过程的代码：

This is the code for executing the stored procedure:

EXEC [dbo].[Create_Subjects] '1234567890'

遇到错误：

Msg 156，级别15，状态1，第28行

关键字'view'附近的语法错误

Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'view'

消息102，级别15，状态1，行29

'）'附近的语法不正确

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ')'

推荐答案

SQL的问题数量。但是首先，调试它们的方法是不执行该命令而打印该SQL，然后执行其常规SQL，您就可以轻松识别出问题所在。

There are a number of issues with your SQL. But firstly the way to debug them is to print the SQL without executing it, then its normal SQL and you can easily identify what is wrong with it.

1. 组成视图的SQL周围不允许使用括号。


2. 您必须按照常规方式对字符串加引号，这意味着将动态字符串中的引号加倍。


3. 按照注释中的建议再次使用 quotename 。


4. 无需传递参数 @ First3Digits 放入 sp_executesql ，因为到那时为止，您已经使用了它的值-在创建一个视图。

1. No brackets are allowed around the SQL making up the view.
2. You have to quote your strings as per normal, which means doubling up the quotes in the dynamic string.
3. Use quotename again as suggested in the comments.
4. There is no need to pass the parameter @First3Digits into sp_executesql because by that point you've used its value - which you have to do given you are creating a view.

    set @Sql = 'create view dbo.' + quotename(@First3Digits)
        + ' as'
        + ' select SubjectName'
        + ' from dbo.Subjects'
        + ' where SubjectName like ''' + @First3Digits + ''' + ''%'';';

    -- This is how you debug dynamic SQL
    print(@Sql);

    execute sp_executesql @Sql;

注意：正如我在上一个问题中提到的那样，根据提供的信息，这似乎确实是设计不良。几乎可以肯定，有一种更好的方法可以解决更大的图像问题。正如Martin Smith所说，内联表值函数可能值得研究。

Note: As I mentioned in your previous question, with the information provided, this seems to be a really bad design. There is almost certainly a better way to solve your bigger picture problem. As commented by Martin Smith an Inline Table Valued Function might be worth investigating.

这篇关于动态SQL遇到的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！