Returning passed parameter as column in result set

Question

I have the following stored procedure code working and want to add the passed parameter @tabname as a column in the result set.

CREATE PROCEDURE CountStar 
    @Tabname char(10)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL varchar(250)

    SELECT @SQL = 'SELECT ETL_LAST_UPD_DTTM, COUNT(*) FROM ls.' + QuoteName(@Tabname) +
    'GROUP BY ETL_LAST_UPD_DTTM'
    EXEC (@SQL)

    SELECT @SQL = 'SELECT ETL_LAST_UPD_DTTM, COUNT(*) FROM ci.' + QuoteName(@Tabname) +
    'GROUP BY ETL_LAST_UPD_DTTM'
    EXEC (@SQL)
    --COMMIT
END
GO

Currently this will return the last updated timestamp and the record count for the table being passed into the stored procedure for the 2 schemas identified. I want to add the @tabname to the result set as the first column followed by last updated timestamp and the record counts. This returns 2 result sets and each should look something like this for each returned result set.

Table_name      Timestamp                   rec_cnt
--------------------------------------------------
CUSTOMERS     2015-09-24 13:10:01.1770000     378

I have tried a few things but can't get the syntax correct.

Thanks for any pointers. Pat

You need a space before Group By. ' Group By...'

Anup Agrawal
– Anup Agrawal

2015-09-24 21:41:18 +00:00
Commented Sep 24, 2015 at 21:41 — Anup Agrawal
– Anup Agrawal, Commented Sep 24, 2015 at 21:41

M.Ali · Accepted Answer · 2015-09-24 21:41:34Z

3

CREATE PROCEDURE CountStar 
    @Tabname SYSNAME    --<-- use appropriate data type for sql server objects
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max);

    SELECT @SQL = N'SELECT  @Tabname AS Table_name,ETL_LAST_UPD_DTTM, COUNT(*) 
                   FROM ls.' + QuoteName(@Tabname) +
                   N' GROUP BY ETL_LAST_UPD_DTTM'
    EXEC sp_executesql @SQL
                      ,N'@Tabname SYSNAME'
                      ,@Tabname 

    SELECT @SQL = N'SELECT @Tabname AS Table_name,ETL_LAST_UPD_DTTM, COUNT(*) 
                   FROM ci.' + QuoteName(@Tabname) +
                  N' GROUP BY ETL_LAST_UPD_DTTM'
    EXEC sp_executesql @SQL
                      ,N'@Tabname SYSNAME'
                      ,@Tabname 
END
GO

answered Sep 24, 2015 at 21:41

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

apesa Over a year ago

The edited version worked.. had to declare @SQL.. Thanks much!

Collectives™ on Stack Overflow

Returning passed parameter as column in result set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related