CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Jesun Bicar
Jesun Bicar

Reputation: 103

Creating stored procedure error

Can anybody tell me whats wrong with this stored procedure? I'm just new and trying to create a new stored procedure, I've declare @ctable. But it cant be found. Please help me

CREATE PROCEDURE [dbo].[sptally]
     @cfield varchar(80),
     @ctable varchar(30)
AS
BEGIN
SET NOCOUNT ON;
    IF OBJECT_ID('dbo.tally') IS NOT NULL   DROP TABLE dbo.tally

    DECLARE @Counter INT = (Select MAX(CAST(@cfield as bigint)) from @ctable)

    SET ROWCOUNT @Counter

    SELECT IDENTITY(INT, 1, 1) ID
    INTO dbo.tally
    FROM master.sys.all_columns c
    CROSS JOIN master.sys.all_columns c1

    SET ROWCOUNT 0

    -- ADD (unique) clustered index
    CREATE UNIQUE CLUSTERED INDEX PKC_Tally ON dbo.tally (ID)
END

Upvotes: 1

Views: 133

Answers (2)

jdl
jdl

Reputation: 1104

CREATE PROCEDURE [dbo].[sptally]
     @cfield varchar(80),
     @ctable varchar(30)
AS
BEGIN
SET NOCOUNT ON
  IF OBJECT_ID('dbo.tally') IS NOT NULL   DROP TABLE dbo.tally

  DECLARE @Counter INT

  DECLARE @SQL nvarchar(max) = N'SELECT @Counter = MAX(CAST(' + @cfield + ' as bigint)) from ' + @ctable

  EXEC sp_executesql
      @SQL
    , N'@Counter int OUTPUT'
    , @Counter = @Counter OUTPUT

  SET ROWCOUNT @Counter

  SELECT IDENTITY(INT, 1, 1) ID
  INTO dbo.tally
  FROM master.sys.all_columns c
  CROSS JOIN master.sys.all_columns c1

  SET ROWCOUNT 0

  -- ADD (unique) clustered index
  CREATE UNIQUE CLUSTERED INDEX PKC_Tally ON dbo.tally (ID)
END

Upvotes: 1

Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

table name in variable will not work like this.. you can use it using EXEC .

Try this:

DECLARE @Counter INT;

declare @strSql nvarchar(2000)
set @strSql ='Select MAX(CAST('+ @cfield +' as bigint)) from' + @ctable

exec sp_executesql   @strSql,N'@Counter int output', @Counter output
SELECT @Counter

http://technet.microsoft.com/en-us/library/ms188332.aspx

Upvotes: 2

Related Questions