Build SQL Statement for execution with variables

How do I build a sql statement using variable that will be run within SP.
I have the following:
**Temp folder is designed above
declare @.CurrentDatabase nvarchar(128)
set @.CurrentDatabase = 'abc'
insert #BackupSet_Header
exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
I have written the restore sql, bur want the backup device to be dynamic
depending on variable passed to SP.
Thanks.might want to do something like this ps. I did not compile this so I hope it
compiles
create procedure my_sp @.dbname nvarchar(128) as
begin
declare @.sql nvarchar(1000)
select @.sql = 'restore headeronly from ' + @.dbname + '_backup'
insert into #BackupSet
exec master..sp_executesql @.sql
end
"Ed Gregory" <eg@.hotmail.com> wrote in message
news:O7P150o8EHA.936@.TK2MSFTNGP12.phx.gbl...
> How do I build a sql statement using variable that will be run within SP.
> I have the following:
> **Temp folder is designed above
> declare @.CurrentDatabase nvarchar(128)
> set @.CurrentDatabase = 'abc'
> insert #BackupSet_Header
> exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
> I have written the restore sql, bur want the backup device to be dynamic
> depending on variable passed to SP.
> Thanks.
>