以表名作为参数的存储过程 [英] Stored procedure with table name as parameter

问题描述

我有一个存储过程，我想将该表的行数分配给一个变量，然后再使用该变量.

I have a stored procedure, and I would like to assign the number of rows of that table to a variable and later use that variable.

我调用的程序如下:

EXEC TEST.dbo.myProc nameOfTable

程序是这样的:

CREATE PROCEDURE myProc @table_name varchar(1024) AS
BEGIN
    DECLARE  @Nval INT
    /*  SOME INSTRUCTIONS */

    SELECT   @Nval  = COUNT(*) FROM @table_name 
END 

执行时出现错误:

消息 156，级别 15，状态 1，过程 nLQ，第 57 行
关键字FROM"附近的语法不正确.

Msg 156, Level 15, State 1, Procedure nLQ, Line 57
Incorrect syntax near the keyword 'FROM'.

我将如何分配变量 @Nval?

推荐答案

试试这个

ALTER PROCEDURE [dbo].[sp_tablenametest]
@table_name varchar(50),
@PMId int,
@ValueEq int

AS
BEGIN
SET NOCOUNT ON;

DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @table_name + 
' WHERE Column1 = ''' + @PMId +  '''' +
' AND Column2= ''' + @ValueEq + ''''

EXEC sp_executesql @cmd 
END

这篇关于以表名作为参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！