如何将动态选择结果放入变量中 [英] How to put dynamic select result in a variable

问题描述

我有以下选择

i have the following select

SELECT OBJECTPROPERTY( OBJECT_ID(N'@TableName'), 'TableHasIdentity')

需要tablename并告诉我们一个表是否有标识

i在一个存储过程中使用这个select有一个表名作为输入

我想放select in int变量的结果

所以我的存储过程是

that needs tablename and tells us if a table has identity or not
i use this select in a stored procedure that has table name as input
and i want to put the result of select in int variable
so my stored procedure is

create procedure myprocedure 
@TableName nvarchar(255)
as
Begin
  Declare @result int 
  Exec('Set ' + @result +'=SELECT OBJECTPROPERTY( OBJECT_ID(N''' + @TableName + '''), ''' +'TableHasIdentity''' + ')''')
  print @result 
End

假设该表具有标识，因此结果必须有一个值

但我没有得到这个值

我应该如何处理这个？

suppose the table has identity so the result must have the value one
but i don't get this value
how should i handle this?

推荐答案

尝试使用 sp_executesql [ ^ ]而不是exec，因为它允许参数定义（进出）



这样的东西可能会起作用（警告 - 我无法测试这个）

Try using sp_executesql[^] instead of exec as it allows parameter definitions (both in and out)

Something like this might work (warning - I have not been able to test this)

Begin
Declare @result int
DECLARE @sSQL nvarchar(max)
DECLARE @ParmDefinition nvarchar(max)
SET sSQL = 'Set @resultOUT=SELECT OBJECTPROPERTY( OBJECT_ID(N''' + @TableName + '''), ''' +'TableHasIdentity''' + ')'''
SET @ParmDefinition = N'@resultOUT int OUTPUT';
EXEC sp_executesql @sSQL, @ParmDefinition, @resultOUT=@result OUTPUT;

需要注意的一点是输出参数是没有在SQL之外定义 - 即注意 @result 和之间的差异@resultOUT

这篇关于如何将动态选择结果放入变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！