CODEWITHSUNDEEP
sql-servert-sql
BBJones
BBJones

Reputation: 45

Returning a count of records in a view from a T-SQL function

I have a table in SQL Server which lists sections in a report. There are three fields, a SectionID (numeric autonumber primary key), SectionTitle (title for the section on the report) and SourceView (name of the view in the local db which returns the records for the report).

What I need is a view which has these three columns plus a fourth column which shows the number of records returned by the 'SourceView'. Something like:

CREATE myView AS

SELECT
  SectionID,
  SectionTitle,
  SourceView,
  myFunction(SourceView) AS RowsInView
FROM
  tblBaseData

I thought this would be simple to achieve by writing a function to take in the view name and return the number of rows. My original plan was to build some dynamic SQL in the body of the function.

CREATE FUNCTION myFunction(@ViewName VARCHAR) RETURNS INT AS

BEGIN

  DECLARE @RowCount INT
  DECLARE @SQL VARCHAR(200)
  SET @SQL = 'SET @RowCount = (SELECT COUNT(*) FROM ' + @ViewName + ')'
  EXEC sp_executesql @SQL
  RETURN ISNULL(@RowCount, 0)

END

However, you can't use EXEC within a function so you can't run dynamic SQL, you can't use EXEC to run a procedure which returns a value, and you can't query the system objects because - although this will work for tables - it won't work for views.

I have tried the following without success:

How to get the row count for views from a database?

Execute function inside view SQL server

https://msdn.microsoft.com/en-GB/library/ms188655.aspx

Create a function for counting rows from any table

I am trying to get a view working because I need to display the results on a web-page with current record counts, otherwise I would build a table and refresh the data periodically,

I assume that I'm just using the wrong approach to this problem. Can anyone point me in the right direction? Thanks.

Upvotes: 2

Views: 3201

Answers (2)

strickt01
strickt01

Reputation: 4048

This is rather clumsy but using a procedure rather than a view would get round the problems you have addressed in your question:

CREATE PROCEDURE GetViewRowCounts
AS
BEGIN
    DECLARE @sectionId int, @title varchar(50), @view varchar(50)
    DECLARE @tbl_view_counts TABLE (section_id int not null,
                                    title varchar(50) not null,
                                    view_name  varchar(50) not null,
                                    view_count int not null)
    DECLARE @RowCount INT
    DECLARE @SQL NVARCHAR(200), @ParmDefinition NVARCHAR(100)

    DECLARE c_vws CURSOR FAST_FORWARD FOR
     SELECT
    SectionID,
    SectionTitle,
    SourceView
    FROM
    tblBaseData

    OPEN c_vws 

    FETCH NEXT FROM c_vws INTO @sectionId, @title, @view

    WHILE @@FETCH_STATUS = 0
    BEGIN
         SET @ParmDefinition = N'@RowCountOUT int OUTPUT';
         SET @SQL = N'SELECT @RowCountOUT = COUNT(*) FROM ' + @view

         EXEC sp_executesql @SQL, @ParmDefinition, @RowCountOUT=@RowCount OUTPUT;

         INSERT @tbl_view_counts
         VALUES (@sectionId, @title, @view, @RowCount)

         FETCH NEXT FROM c_vws INTO @sectionId, @title, @view
    END

    CLOSE c_vws
    DEALLOCATE c_vws

    SELECT * FROM @tbl_view_counts
END

Upvotes: 1

J.M Smith
J.M Smith

Reputation: 391

This will give you the count back without having to use a function or know the name of the view:

CREATE myView AS

SELECT
  SectionID,
  SectionTitle,
  SourceView,
  COUNT(*) OVER() AS RowsInView
FROM
  tblBaseData

EDIT: Apologies, I misread your initial post. I agree that a stored procedure is probably the best way to go, but if you wanted to continue with a view approach, you could have a "lookup" view, like this:

CREATE VIEW dbo.ViewCounts
AS

SELECT  'SourceView1' AS SourceViewName, 
        COUNT(*) OVER () AS RowsInView
FROM SourceView1
UNION
SELECT  'SourceView2' AS SourceViewName, 
        COUNT(*) OVER () AS RowsInView
FROM SourceView2

-- etc...

Then join back onto it in your original view to get the count this way:

CREATE myView AS

SELECT
  t.SectionID,
  t.SectionTitle,
  t.SourceView,
  vc.RowsInView
FROM
  tblBaseData t
JOIN dbo.ViewCounts vc ON vc.SourceViewName = t.SourceView

It's a very dirty approach (and the ViewCounts view will need manually updating if you add new views), but could solve your problem if you absolutely need to persist with a view.

Upvotes: 1

Related Questions