CODEWITHSUNDEEP
sqlsql-servervariablesparameters
pgunston
pgunston

Reputation: 302

In SQL, how do I refer to a parameter / variable when using DENY UPDATE?

CREATE PROCEDURE cspFieldAccess
@Tbl varchar(20),
@Fld varchar(20),
@Usr varchar(35),
@Dny varchar(5),
@All varchar(5)
AS
IF @Dny = 'Y' and @All = 'Y' 
DENY UPDATE ON [@Tbl] ([@Fld]) TO [MCCOSKERS\ViewPoint Production Users] ;
ELSE IF @Dny = 'Y' and @All = 'N'
DENY UPDATE ON [@Tbl] ([@Fld]) TO [@Usr] ;
ELSE IF @Dny = 'N' and @All = 'Y' 
GRANT UPDATE ON [@Tbl] ([@Fld]) TO [MCCOSKERS\ViewPoint Production Users] ;
ELSE IF @Dny = 'N' and @All = 'N'
DENY UPDATE ON [@Tbl] ([@Fld]) TO [@Usr] ;

EXEC cspFieldAccess 'HQRV', RevEmail, 'MCCOSKERS\PGunston', Y, N

The above code produces an error message:

Cannot find the object '@Tbl', because it does not exist or you do not have permission.

Instead of the query looking for the table called '@Tbl', I want it to go for the value which is assigned to the @Tbl parameter.

How can I achieve this?

The below query

Upvotes: 0

Views: 53

Answers (2)

cameront
cameront

Reputation: 710

SQL Server is interpreted so the expression is evaluation as you have typed it. To substitute variables in you need to use dynamic SQL. Be Careful however as exec statement opens you up to sql injection.

declare @Tbl varchar(20) = 'foo'
declare @Fld varchar(20) = 'bar'
declare @login  varchar(100) = '[MCCOSKERS\ViewPoint Production Users]'
declare @sql varchar(max)
set @sql = 'DENY UPDATE ON [' + @Tbl + '] ([' + @Fld + ']) TO ' + @login  + ' ;'
exec(@sql)

You can also use the Query->SQLCMD mode and try the following which uses the SQLCMD syntax for substituting in variables if this appeals to you.

:setvar Tbl  foo
:setvar Fld  bar
DENY UPDATE ON [$(Tbl)] ([$(Fld)]) TO [MCCOSKERS\ViewPoint Production Users];

Upvotes: 0

Dave C
Dave C

Reputation: 7402

Use dynamic SQL:

        declare
        @Tbl varchar(20),
        @Fld varchar(20),
        @sql nvarchar(max)

        set @Tbl = 'table'
        set @Fld = 'field'
        set @sql='DENY UPDATE ON ['+@Tbl+'] (['+@Fld]+') TO [MCCOSKERS\ViewPoint Production Users]'
        print @sql
        --exec sp_executesql @sql

Once you run, and print, confirm it looks ok, then uncomment the EXEC and try it out.

Be careful of SQL injections, if users can modify the variables, you're at risk.

Upvotes: 1

Related Questions