Building SQL using variables

Hello all!

I am sure there is a technical name for this, but I am trying to build a sql statement using variables... where the variables would be entire clauses within the statement, not just values. This will ultimately be used in a stored procedure.

ie. Focus on the @.AndClause variable

-----------
declare @.AndClause varchar(128)
select @.AndClause = ' AND lastname like ''jharris%'''

SELECT *
FROM my_customer_table
WHERE 1=1
@.AndClause
-----------

I have seen this done before but can not find it in an of my references. Thank you for your helpdeclare @.AndClause varchar(128)
set @.AndClause = ' AND lastname like ''jharris%'''
declare @.vSQL varchar(200)
set @.vSQL = 'SELECT *
FROM my_customer_table
WHERE 1=1' + @.AndClause

exec(@.vSQL)|||I missed the plus sign... Thanks alot Jora! Do you know if there is technical name for this?|||euh ... building dynamic sql statements? Don't think there is one term for it. Also, you can use the stored procedure sp_executesql for executing dynamic queries. See Books Online for more info on the differences between the two methods.

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.
>

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.
>

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.
>