CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
kiran tej
kiran tej

Reputation: 19

Use variable in dynamic SQL

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.

Upvotes: 1

Views: 259

Answers (1)

Dale K
Dale K

Reputation: 27201

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.

Upvotes: 2

Related Questions