Passing & Using variables in Stored Procs for table names

Hi all
SQL 2k, win 2kpro, vbscript asp
I'm just getting into stored procs and coming on quite good imho :)
but I'm stuck on a little problem
I want to pass a stored proc a variable eg "G001_"
create procedure dbo.myproc
@.prefix vchar (20)
as
I want to use the "prefix within the stored proc on table names I'm
wanting to grant access for a user on several tables with this prefix
eg
grant select on @.prefix+tablenameOne to UserName
grant select on @.prefix+tablenameTwo to UserName
grant select on @.prefix+tablenameThree to UserName
grant select, update on @.prefix+tablenamefour to UserName
grant select on @.prefix+tablenamefive (col1, col2) to UserName
Would look like this if fixed:
grant select on G001_tablenameOne to UserName
grant select on G001_tablenameTwo to UserName
etc.
but I cant seem to get this to work... How can I do this?
The tables will be created from a different stored proc and then I
want to grant users to be able to use them tables
thanks for any help.
Alyou will have to execute the 'GRANT' statment using EXEC.
Ex:
exec ('grant select on ' + @.prefix + ' tablenameOne to UserName')
--
-Vishal
"Harag" <harag@.softhome.net> wrote in message
news:5175kv4u71063eekbo7i46bjl03u0ok7um@.4ax.com...
> Hi all
> SQL 2k, win 2kpro, vbscript asp
> I'm just getting into stored procs and coming on quite good imho :)
> but I'm stuck on a little problem
> I want to pass a stored proc a variable eg "G001_"
>
> create procedure dbo.myproc
> @.prefix vchar (20)
> as
>
> I want to use the "prefix within the stored proc on table names I'm
> wanting to grant access for a user on several tables with this prefix
> eg
> grant select on @.prefix+tablenameOne to UserName
> grant select on @.prefix+tablenameTwo to UserName
> grant select on @.prefix+tablenameThree to UserName
> grant select, update on @.prefix+tablenamefour to UserName
> grant select on @.prefix+tablenamefive (col1, col2) to UserName
> Would look like this if fixed:
> grant select on G001_tablenameOne to UserName
> grant select on G001_tablenameTwo to UserName
> etc.
> but I cant seem to get this to work... How can I do this?
> The tables will be created from a different stored proc and then I
> want to grant users to be able to use them tables
> thanks for any help.
> Al