×

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

Homework? good good study, day day up!, see answer inside:

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?
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
    • 你的问题是?... ...
    • Homework? good good study, day day up!, see answer inside:
      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 信息。
        一看您就是高手,谢谢了。我现在也想到了你的解法,不过可能我没有表达清楚,我最终的目的是:目前已经有一个 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. >>>
          本文发表在 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.