传入表名返回纪录数

星宇(16395371) 17:12:45
CREATE PROCEDURE spGetRowCount

@TableName varchar(128),
@RowCount bigint output
as
DECLARE   @SQL   NVARCHAR(1000)
DECLARE   @R BIGINT
SET   @SQL=  N'select @RC=count(*) from  '+@TableName
EXEC  SP_EXECUTESQL   @SQL,  N' @RC BIGINT OUTPUT',  @R OUTPUT
SET   @RowCount=  @R

go

sp_executesql     支持参数替换使 sp_executesql 比 EXECUTE 更通用

sp_executesql 或 EXECUTE 语句执行字符串时，字符串被作为其自包含批处理执行

sp_executesql 支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换，但是 EXECUTE 语句不支持

 
星宇(16395371) 17:12:52
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(@a) AND indid < 2

CREATE PROCEDURE spGetRowCount

@TableName varchar(128),
@RowCount bigint output
as
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(@TableName) AND indid < 2

go
 

Yx(44048955) 17:25:59
我给你一个笨办法
create procedure proTable4
@tablename varchar(40),
@cou int output
as
begin
 
 declare @table varchar(40)
 set @table = @tablename
 execute('select 1 from ' + @table )                 //不明白为何为select 1
        set @cou = @@rowcount                         //@rowcount返回上一语句受影响的行数
end

select 12 from people  如果people表里有N条纪录，则打印N个  12    12为常量  但常量为何可以这样 不懂

快乐：

EXEC 'SELECT Count(*) FROM '+@tablename

附：

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once. */
SET @SQLString =
     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'

SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable




declare   @SQL   nvarchar(1000)
declare   @R bigint
SET   @SQL=  N'select @R=count(*) from  '+convert(nvarchar(200),@TableName)
EXEC  SP_EXECUTESQL   @SQL,  N' @R BIGINT OUTPUT',  @R OUTPUT
SET   @CountAll=  @R

@CountAll为传出参数   @TableName为传入参数