×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Calgary

i wrote a sp, TableCount. hope it will help you. >>>

digitworm(digitworm)
本文发表在 rolia.net 枫下论坛-- =============================================
-- Author : DiGiTwoRM
-- Create date: Feb 8, 2007
-- Description: Get row count of a table
-- @table_name - table name as an input parameter
-- @count - count number an output parameter
-- =============================================
ALTER PROCEDURE [dbo].[TableCount]
@table_name varchar(128),
@count int out
AS
BEGIN
SET NOCOUNT Off;

DECLARE @sql nvarchar(1000)
DECLARE @parameters nvarchar(1000)

SET @table_name = 'customers'
SET @count = 0

SET @sql = 'SELECT @count = count(*) FROM dbo.' + quotename(@table_name)
SET @parameters = '@table_name varchar(128), @count int out'
EXEC sp_executesql @sql, @parameters, @table_name, @count out
END


-----------------------------------------------
I tested it with northwind database
-----------------------------------------------
use northwind
go
declare @t varchar(100)
declare @n int
set @t= 'customers'

exec tablecount @t, @n out

print @n
go

-----------------------------------------------
Result
-----------------------------------------------
91更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#3484711@0)
Last Updated: 2007-2-8
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 请教 MS SQL Server T-SQL 编程问题，想写一个小 stored procedure，命名为 sp_count，入口参数是 table name，返回该 table 的 count(*) 。它被另一个 sp 调用，想达到类似这样的效果 declare @c int exec @c = sp_count @tableName -jxwang(努力奋斗); 2007-2-7 (#3482306@0)
    • 你的问题是？... ... -bdbs(不多不少); 2007-2-7 (#3482499@0)
    • Homework? good good study, day day up!, see answer inside: -romanceviolin(Sonata); 2007-2-7 {683} (#3482787@0)
      Proc 1, sp_Counter:

      IF EXISTS (SELECT name
      FROM sysobjects
      WHERE name = N'sp_Counter'
      AND type = 'P')
      DROP PROCEDURE sp_Counter
      GO

      Create PROCEDURE sp_Counter
      @cTableName varchar(50)
      AS
      Declare @cSQL varchar(1000)
      Declare @lnCounter INT
      SELECT @cSQL = 'SELECT Count(*) FROM '
      SELECT @cSQL = @cSQL + @cTableName
      exec (@cSQL)
      GO

      Proc 2, Sp_test

      IF EXISTS (SELECT name
      FROM sysobjects
      WHERE name = N'sp_test'
      AND type = 'P')
      DROP PROCEDURE sp_test
      GO

      CREATE PROCEDURE sp_test
      @cTableName varchar(50)
      AS
      EXEC sp_Counter @cTableName
      GO
      ****************************
      Make sence?
      • 一看您就是高手，谢谢了。我现在也想到了你的解法，不过可能我没有表达清楚，我最终的目的是：目前已经有一个 temp table，存放着 db 中所有用到的 table 信息。 -jxwang(努力奋斗); 2007-2-7 {350} (#3484232@0)
        一看您就是高手，谢谢了。我现在也想到了你的解法，不过可能我没有表达清楚，我最终的目的是：目前已经有一个 temp table，存放着 db 中所有用到的 table 信息。想在一个 while loop 里面，用类似
        declare @n int
        exec @n = sp_test 'load_tbl'
        类似的语句把这个 temp table 中的 count_no 列 update 。现在的问题是 @n 返回是 0。您有什么更好的办法吗？谢谢
        • i wrote a sp, TableCount. hope it will help you. >>> -digitworm(digitworm); 2007-2-8 {1163} (#3484711@0)
          本文发表在 rolia.net 枫下论坛-- =============================================
          -- Author : DiGiTwoRM
          -- Create date: Feb 8, 2007
          -- Description: Get row count of a table
          -- @table_name - table name as an input parameter
          -- @count - count number an output parameter
          -- =============================================
          ALTER PROCEDURE [dbo].[TableCount]
          @table_name varchar(128),
          @count int out
          AS
          BEGIN
          SET NOCOUNT Off;

          DECLARE @sql nvarchar(1000)
          DECLARE @parameters nvarchar(1000)

          SET @table_name = 'customers'
          SET @count = 0

          SET @sql = 'SELECT @count = count(*) FROM dbo.' + quotename(@table_name)
          SET @parameters = '@table_name varchar(128), @count int out'
          EXEC sp_executesql @sql, @parameters, @table_name, @count out
          END


          -----------------------------------------------
          I tested it with northwind database
          -----------------------------------------------
          use northwind
          go
          declare @t varchar(100)
          declare @n int
          set @t= 'customers'

          exec tablecount @t, @n out

          print @n
          go

          -----------------------------------------------
          Result
          -----------------------------------------------
          91更多精彩文章及讨论，请光临枫下论坛 rolia.net
          • Many thanks. I don't need to verify it myself again and what I've tried is so close to your answer. I should use " @para out " but not "exec @ret = sp..." . Thanks again. -jxwang(努力奋斗); 2007-2-8 (#3486636@0)

More Topics

  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
  • 如何在 AWS EC2 实例上构建 WordPress 应用程序?
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论