Issues encountered with dynamic SQL

Question

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'

Error encountered:

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

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

Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'view'. Msg 102, Level 15, State 1, Line 29 Incorrect syntax near '%'. — kiran tej
– kiran tej, Commented May 7, 2020 at 22:37
If your view name doesn't conform to regular identifier naming rules (e.g. it starts with a number), you also must enclose the name in double quotes or square brackets. Use QUOTENAME like you did earlier in the proc: SET @Sql = 'create view ' + QUOTENAME(@First3Digits) — Dan Guzman
– Dan Guzman, Commented May 7, 2020 at 22:37
You are also missing apostrophes when you add @First3Digits in your create view. You did it correctly in the SELECT block, but not the CREATE VIEW block. — David
– David, Commented May 7, 2020 at 22:39
Rather than creating a view for each parameter look at inline table valued functions — Martin Smith
– Martin Smith, Commented May 7, 2020 at 23:43

Dale K · Accepted Answer · 2020-05-08 00:06:33Z

1

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.

No brackets are allowed around the SQL making up the view.
You have to quote your strings as per normal, which means doubling up the quotes in the dynamic string.
Use quotename again as suggested in the comments.
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;

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.

edited May 8, 2020 at 0:06

answered May 7, 2020 at 22:56

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marc Gravell Over a year ago

This is a SQL injection attack vector, though. That may be an inherent problem if making a view, but it is still crucial to be aware of.

Dale K Over a year ago

@MarcGravell how would you fix it? You can't create a view that uses a parameter... so I guess it just needs escaping somehow?

Marc Gravell Over a year ago

@DaleK I revised my words a bit; since a view is involved (reading it a second time), that is more problematic. I'd probably just avoid this scenario with a very large barge pole. Like "social distancing" but for RDBMS.

Dale K Over a year ago

Yeah, I added note to that effect on OP's first question. But didn't receive any further details to be able to suggest an alternative.

Dale K Over a year ago

Also do you recon you can fit enough SQL into a nvarchar(3) for an injection attack? :)

|

Collectives™ on Stack Overflow

Issues encountered with dynamic SQL

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related