Query a view in a procedure in which the view name is a variable

Question

My problem: I have a set of views from which I want to query dynamically. I want to load the view names by SELECT * from sys.all_views into a cursor (let's call it view_cursor) and then do something like this in a procedure:

-- loop starts
FETCH NEXT FROM view_cursor INTO @view_name;
SELECT * FROM @view_name;
-- fetch next
-- loop ends

I have already tried this out with one view only which I pass as a parameter to a stored procedure:

CREATE PROCEDURE [dbo].[query_Special_View](@view_name VARCHAR(100)) AS
DECLARE viewname VARCHAR(100);
DECLARE @counter INT = 0;
BEGIN

    PRINT @view_name
    EXECUTE('SELECT COUNT(Value) INTO' + @counter + 'FROM' + @view_name + '');

    PRINT @counter;
END

Then, I wanted to execute the procedure with an existing view name:

EXEC    [dbo].[query_Special_View]
        @view_name = N'A_very_special_LOC_view' -- the view name as parameter
GO

The result is:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'Value'.

However the column Value, on which I try to invoke the COUNT() function in the procedure, does exist.

So, how can I write such dynamic queries in stored procedures? I hope I can do this without any ugly dynamic SQL magic, otherwise I will write my dynmic query logic with Java + JDBC in which I can do such things without any hassle.

Answer 1

The problem relates to your use of @counter within your dynamic SQL. It's wrong in a couple of ways: You want to assign to that variable, but in the spot you're using it, it's being translated into building the string that is the SQL statement. You should instead use sp_executesql and pass the count value back as a return parameter.

I'd also recommend that you use QUOTENAME to prevent against SQL injection in your statement. If someone creates a view with a carefully constructed name, they could make you have a very bad day.

Try using this procedure definition:

 ALTER PROCEDURE [dbo].[query_Special_View](@schema_name sysname, @view_name sysname) AS
 BEGIN
     DECLARE @params nvarchar(1000);
     DECLARE @sql nvarchar(1000);
     DECLARE @return bigint;

     SET @params =  N'@count bigint OUT';
     SET @sql = 'SELECT @count = COUNT(*)  FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@view_name) + ';';

     PRINT @sql

     EXEC sp_executesql @sql, @params, @count = @return OUT

     SELECT SchemaName     = @schema_name,
            ObjectName     = @view_name,
            ObjectRowCount = @return

 END
 GO

Testing this out for a single call works just fine:

 EXEC dbo.query_Special_View @schema_name = 'sys', @view_name = 'databases';

Going back to your original issue...

You have a series of views that you want to get the row counts for. You don't even need this as a stored procedure. You can simply put those few lines of code directly into a cursor and execute it directly.

 DECLARE @SchemaName sysname,
         @ObjectName sysname,
         @params nvarchar(1000),
         @sql nvarchar(1000),
         @return bigint;

 --Table to hold the results 
 CREATE TABLE #Results (
     SchemaName sysname,
     ObjectName sysname,
     ObjectRowCount bigint);

 --What objects do you want to get the row counts for?
 --I'm just querying sys.views, but edit this query for whatever you need
 DECLARE obj_cur CURSOR FOR
     SELECT SchemaName = schema_name(schema_id), 
            ObjectName = name
     FROM sys.views v;

 --Use that cursor to loop through all objects
 OPEN obj_cur;
 FETCH NEXT FROM obj_cur INTO @SchemaName, @ObjectName;
 WHILE @@FETCH_STATUS = 0
 BEGIN
     --Dynamic SQL to get the row count
     SET @params =  N'@count bigint OUT';
     SET @sql = 'SELECT @count = COUNT(*)  FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';';
     EXEC sp_executesql @sql, @params, @count = @return OUT
     --Put that number into a table
     INSERT INTO #Results (SchemaName, ObjectName, ObjectRowCount)
     SELECT SchemaName = @SchemaName,
            ObjectName = @ObjectName,
            ObjectRowCount   = @return
     FETCH NEXT FROM obj_cur INTO @SchemaName, @ObjectName;
 END
 CLOSE obj_cur;
 DEALLOCATE obj_cur;

 SELECT *
 FROM #Results;

 DROP TABLE #Results;

Answer 2

Always print out your dynamic SQL before executing it so you can check for syntax errors.

Should be something like:

declare @sql nvarchar(max) = concat(N'SELECT COUNT(Value) INTO ', @counter, N' FROM ',  @view_name , ';')
print @sql

EXECUTE(@sql);

Hint: a query like

SELECT COUNT(Value) INTO#tmpFROMfoo

will fail with

Msg 207, Level 16, State 1, Line 11
Invalid column name 'Value'.

Answer 3

It appears as though you are trying to get the number of rows returned from the view, and have the ability to print that value as an output. Your query looks like it's trying to write this data to a table.

Try the following (replace the default view I've used here with whatever you would need)

DECLARE @view_name NVARCHAR(100) = 'sys.databases';
DECLARE @Counter INT = 0;
DECLARE @SqlStmt NVARCHAR(2000) ,
    @ParamDef NVARCHAR(200);

IF EXISTS ( SELECT  *
            FROM    sys.all_objects
            WHERE   object_id = OBJECT_ID(@view_name) )
    BEGIN
        SELECT  @SqlStmt = 'SELECT @Counter = COUNT(*) FROM ' + @view_name
                + ';';
        SET @ParamDef = N'@Counter INT OUTPUT';
        EXEC sp_executesql @SqlStmt, @ParamDef, @Counter = @Counter OUTPUT;

        PRINT @Counter;

    END;