Use variable in dynamic SQL

Question

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

    -- Fetching the first 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
        PRINT 'View exists'
        -- checking if the subject is present in the view
        IF EXISTS (SELECT 1 FROM @First3Digits WHERE SubjectName = @Subj_ID)
        BEGIN
            SET @Result = 1;
        END
        ELSE
        BEGIN
            SET @Result = 0;
        END
    END
    ELSE
    BEGIN
        -- Create a view as view doesn't exist
        EXEC('create view' + @First3Digits 
            + 'as 
                    (select SubjectName from dbo.Subjects where SubjectName like '+@First3Digits+'%'+');')

        SET @Result = 0;
        PRINT 'view does not exist'
    END

    PRINT @First3Digits
GO;

In the above code I am getting issues on the line

IF EXISTS (SELECT 1 FROM @First3Digits WHERE SubjectName = @Subj_ID)

Please help me to fix this issue.

If you intend to write dynamic sql, then you need to SEE the statement you generate. Do that and you will discover one problem. — SMor
– SMor, Commented May 7, 2020 at 19:54
Since @First3Digits isn't a table variable, but might contain the name of a table, you'll need to make your problem statement dynamic SQL. Although three characters as used appears to preclude too much fun, see SQL Injection before going too far down the dynamic SQL rabbit hole. — HABO
– HABO, Commented May 7, 2020 at 20:20

Dale K · Accepted Answer · 2020-05-07 23:48:12Z

2

The line:

IF EXISTS (SELECT 1 FROM @First3Digits WHERE SubjectName = @Subj_ID)

is broken because @First3Digits is the name of a table (I assume - even though its only nvarchar(3)!) and cannot be used directly in a query like that, instead you need to use dynamic SQL, specifically sp_executesql as follows:

PRINT 'View exists'

declare @Sql nvarchar(max);

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'@Result bit out', @Result = @Result out; 

-- @Result is now set as required

Note the use of quotename to protect against SQL injection.

As an aside, on the face of it, your design looks highly in need of improvement. Creating a view per Subject (whatever that actually is) seems flawed, prone to maintenance issues and scalability issues. There is probably a better way.

edited May 7, 2020 at 23:48

answered May 7, 2020 at 20:55

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

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

2 Comments

HABO Over a year ago

It's curious that you assemble @sql with ... ' where SubjectName = ''' + @Subj_ID + ''') ... and then pass @Subj_ID as a parameter, though there is no reference to it in the dynamic SQL statement. Is that to allow its use for SQL injection?

Dale K Over a year ago

@HABO very curious :) - wasn't paying enough attention :) removed now.

Collectives™ on Stack Overflow

Use variable in dynamic SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related