T-SQL statement does not accept variable as role name in grant expression

Question

I want to create a stored proc in SQL Server 2008 to create database roles and grant them permissions that takes a single parameter nvarchar, which represents the name of the role to be created, but I'm not sure how to write this.

I can create a role with this

EXEC sp_addrole @RoleName

but when I try to grant permissions for a role with this

Grant select on dbo.someTable to  @RoleName

he will not accept @RoleName, how can I do this?

thank

Answer 1

Use dynamic SQL to generate the sql statement as text, which can then be run using EXEC

declare @sql nvarchar(max)
set @sql = 'Grant select on dbo.someTable to ' + @RoleName  -- protect if required
EXEC (sql)