如何在存储过程中使用动态表名编写选择查询? [英] How to write Select query with dynamic table name in Stored Procedure?

问题描述

我正在编写一个存储过程.但是在这个过程中表名是动态的.我想从我的变量表中获取行号.但是我将在哪里设置 @rownumber 并返回选择查询?

I am writing a stored-procedure. But the table name is dynamic in this procedure. I want to get row number from my variable table. But where will I set @rownumber with return of select query?

Create Proc update_eMail
    (@tablename nvarchar(50),
     @columnname nvarchar(50))
AS
Begin 

    Declare @q_getrowNumber NVARCHAR(MAX)
    Declare @rownumber int
    SELECT @rownumber =  Count(ID) FROM  quotename(@tablename) // doesnt work

END

提前致谢

推荐答案

你写错了procedure for dynamic query尝试这个.它会起作用.

You write wrong Procedure for dynamic query Try This. It will Work.

ALTER PROC update_eMail(@tablename NVARCHAR(50))
AS
BEGIN 
DECLARE @RowNumber NVARCHAR(MAX)=''

  set @RowNumber='select Count(ID) FROM '+@tablename+''
  exec(@RowNumber)
END

或在您的查询中尝试以下代码

OR Try below Code in your Query

ALTER PROC update_eMail1
(
   @tablename NVARCHAR(50)
)
AS
BEGIN
     DECLARE @sql NVARCHAR(4000)= '',@rownumber INT
     SET @sql = 'SELECT @rownumber = Count(ID) FROM '+quotename(@tablename) 

   EXEC sp_executesql @sql, N'@rownumber int output',@rownumber OUTPUT

   SELECT @rownumber

END

这篇关于如何在存储过程中使用动态表名编写选择查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！