系统存储过程和表参数

I'm accustomed to doing this:

我习惯这样做:

use MyDb
go
create type table_list as table (name sysname)
go
create proc rebuild_indexes
    @ls table_list readonly
as
  [...]

but now I want to create the proc in master and use it in other databases:

但现在我想在master中创建proc并在其他数据库中使用它:

use master
go
create type table_list as table (name sysname)
go
create proc sp_rebuild_indexes
    @ls table_list readonly
as
  [...]

...it builds fine but I can't use it!

...它构建良好,但我不能使用它!

use MyDb
go
declare @ls table_list

yields:

Msg 2715, Level 16, State 3, Line 1 Column, parameter, or variable #1: Cannot find data type table_list. Parameter or variable '@ls' has an invalid data type.

消息2715,级别16,状态3,行1列,参数或变量#1:找不到数据类型table_list。参数或变量'@ls'的数据类型无效。

so I tried creating a synonym, which doesn't complain during creation but doesn't help:

所以我尝试创建一个同义词,它在创建过程中没有抱怨,但没有帮助:

create synonym table_list for master.dbo.table_list

thoughts anyone?

2 个解决方案

#1

Table types declared in different databases, even if they share the same name and structure, are not treated as being the same by SQL Server:

在不同的数据库中声明的表类型,即使它们共享相同的名称和结构,也不会被SQL Server视为相同:

create database DB1
go
use DB1
go
create type TT1 as table (ID int not null)
go
create procedure Echo
    @T TT1 readonly
as
    select * from @T
go
create database DB2
go
use DB2
go
create type TT1 as table (ID int not null)
go
declare @T TT1
insert into @T(ID) values (1)
exec DB1..Echo @T

Result:

(1 row(s) affected)
Msg 206, Level 16, State 2, Procedure Echo, Line 0
Operand type *: TT1 is incompatible with TT1

So far as I'm aware, there is no way to declare a variable in a database using a table type definition from another database. (e.g. anywhere where you see a user defined table type can be used, it can only be named as <table type> or <schema>.<table type>. 3 or 4 part names are not allowed)

据我所知,没有办法使用另一个数据库中的表类型定义在数据库中声明变量。 (例如,您可以使用任何可以使用用户定义的表类型的地方,它只能命名为

。或

。不允许使用3或4个部分名称)

(The above is true for 2008 and 2012; obviously, future versions may do something to address this)

(以上情况适用于2008年和2012年;显然,未来版本可能会采取措施解决此问题)

As a work-around, you can do it the "poor man's" way - have your master defined stored procedure work against a temp table, rather than a user defined table type:

作为解决方法,您可以使用“穷人”的方式 - 让您的主定义存储过程针对临时表而不是用户定义的表类型:

use Master
go
create procedure sp_Echo
as
    select DB_NAME(),* from #t
go
create database DB1
go
use DB1
go
create table #t (ID int not null)
insert into #t (ID) values (1),(2)
exec sp_Echo

Results:

------------- -----------
DB1           1
DB1           2

#2

AFAIK, user defined types can't be used outside the context of the database they were created in.

AFAIK,用户定义的类型不能在创建它们的数据库的上下文之外使用。

You may have to create the UDT in each database, but leave the sProc in master.

您可能必须在每个数据库中创建UDT,但将sProc保留在master中。

Is there a specific reason you're using the sp_ naming convention?

您是否有使用sp_命名约定的特定原因?

Peet

#1