×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Florida

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

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论