T-SQL Question

Is it possible to write a stored procedure like below where you will not kno
w
the table name in advance? The fields will always be the same. I've tried
this, setting quoted indentifier to off and cannot get it to compile. Thanks
.

Set @sql = "Select " + @siteID + " = site_id From " + @table
execute (@sql)

Mike,

You can use sp_executesql to accomplish this.

Example:

use northwind
go

declare @i int
declare @ts sysname
declare @tn sysname
declare @sql nvarchar(4000)

set @ts = 'dbo'
set @tn = 'order details'
set @sql = N'select top 1 @i = orderid from ' + quotename(@ts) + '.' +
quotename(@tn)
set @sql = @sql + N' group by orderid order by count(*) desc'

exec sp_executesql @sql, N'@i int output', @i output

print @i
go

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html


AMB

"Mike Collins" wrote:

> Is it possible to write a stored procedure like below where you will not k
now
> the table name in advance? The fields will always be the same. I've tried
> this, setting quoted indentifier to off and cannot get it to compile. Than
ks.
>
> Set @sql = "Select " + @siteID + " = site_id From " + @table
> execute (@sql)
>

That's great. Thanks for the example...especially for the article that looks
like it will explain everything I ever wanted to know about dynamic sql.

"Alejandro Mesa" wrote:

> Mike,
>
> You can use sp_executesql to accomplish this.
>
> Example:
>
> use northwind
> go
>
> declare @i int
> declare @ts sysname
> declare @tn sysname
> declare @sql nvarchar(4000)
>
> set @ts = 'dbo'
> set @tn = 'order details'
> set @sql = N'select top 1 @i = orderid from ' + quotename(@ts) + '.' +
> quotename(@tn)
> set @sql = @sql + N' group by orderid order by count(*) desc'
>
> exec sp_executesql @sql, N'@i int output', @i output
>
> print @i
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
>
> AMB
>
> "Mike Collins" wrote:
>