MS SQL Server - T-SQL Question

Author

T-SQL Question

Mike Collins

2005-09-29, 1:23 pm

Is it possible to write a stored procedure like below where you will not know
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)

Alejandro Mesa

2005-09-29, 8:23 pm

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 know
> 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 Collins

2005-09-29, 8:23 pm

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:
[color=darkred]
> 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:
>

Sponsored Links