使用表格的对象ID选择* [英] Select * using a table's object Id

问题描述

我有一个可以在多个表上使用的存储过程.
我想根据变量在表上执行select语句.

像这样的东西:

I have a stored procedure which can work on several tables.
I want to preform the select statement on a table depending on a variable.

Something like:

declare @myTableId int = 73102280


SELECT * FROM   (
            SELECT OBJECT_NAME(id) AS [TableName]
            FROM sysindexes I
            WHERE indid IN (1,0)
                AND OBJECTPROPERTY(id, 'IsUserTable') = 1
                AND ID = @myTableId 
                )

我想要从子查询中找到的表中选择数据.

可能吗?

我不想使用:

What I want is to select the data from the table I found in the sub-query.

Is it even possible?

I don''t want to use:

declare @myTableName nvarchar(100) = 'Users'
declare @stringToExecute nvarchar(max)

set @stringToExecute= 'select * from ' + @myTableName

推荐答案

您想要的被称为动态SQL" '.您可以使用Exec函数并像这样实现它:

What you want is known as ''dynamic SQL''. You can use Exec function and implement it like this:

declare @myTableId int = 73102280
declare @myTableName nvarchar(100)
declare @stringToExecute nvarchar(max)

SELECT * FROM   (
            SELECT OBJECT_NAME(id) AS [TableName]
            FROM sysindexes I
            WHERE indid IN (1,0)
                AND OBJECTPROPERTY(id, 'IsUserTable') = 1
                AND ID = @myTableId 
                )


set @stringToExecute= 'select * from' + @myTableName

Exec (@stringToExecute)

(如果@myTableId与sysindexes中的任何ID不匹配，则可能要添加错误处理程序)

hth，
foxyland

(you might want to add error handler if the @myTableId doesn''t match with any of the ID in sysindexes)

hth,
foxyland

其他解决方案是使名称与sp定义中的索引ID保持硬编码，以供选择，但这是最糟糕的解决方案....

问候

Other solution is to keep the names hard-coded against the index id in the sp definition for selection but it is even more worst solution....

Regards

这篇关于使用表格的对象ID选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！