CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ibo
Ibo

Reputation: 4329

Stored procedure not returning the result

I have defined two stored procedures that should return a set of data, the first one works perfectly, the second one runs without any error, but it does not return any result set, why?

Stored procedure #1: returns and displays recordset:

-- Counts the number of rows from any non-system Table, *SAFELY*
CREATE PROCEDURE spCountAnyTableRows(@PassedTableName as varchar(255))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ActualTableName AS NVARCHAR(255)
    DECLARE @sql AS NVARCHAR(MAX)

    SELECT @ActualTableName = QUOTENAME(TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    SELECT @sql='SELECT COUNT(*) FROM AS cnt' + @PassedTableName

    EXECUTE(@sql)

    RETURN
END

EXEC spCountAnyTableRows 'tmp.myTable'

Stored procedure #2: runs without any error, but does not return any data

DROP PROCEDURE IF EXISTS npi.spJoinOnMatnr
GO

CREATE PROCEDURE npi.spJoinOnMatnr(@PassedTableName AS NVARCHAR(255))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ActualTableName AS NVARCHAR(255)
    DECLARE @sql AS NVARCHAR(MAX)

    SELECT @ActualTableName = QUOTENAME(TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    SELECT @sql = 'SELECT A.id, A.F1, B.maktx, B.maktx_cnt
                   FROM ' + @ActualTableName + ' A
                   LEFT JOIN npi.makt B ON A.F1 = B.matnr
                   ORDER BY A.id;'

    EXECUTE(@sql)

    RETURN
END
GO

EXEC npi.spJoinOnMatnr 'tmp.myTable'

Upvotes: 0

Views: 463

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

It is a good practice to return a dynamic query:

CREATE PROCEDURE ...
  @Debug BIT  = 0
BEGIN

   IF @Debug = 1 PRINT @sql;
END

Second, I guess you are missing a schema for @ActualTableName as it is only table name.

You are passing 2 part name 'tmp.myTable':

SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName  -- table name contains only `myTable`

I propose to pass 2 parameters explicitly @schemaName and @tableName

Upvotes: 2

Related Questions