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.

• Nic: Why not count(*) instead of count(value)?
• Bruder Lustig: @Nic Tried already. Throws no error but results in zero.
• anon: The dynamic SQL in this case is actually pretty straightforward. I don't think I'd describe it as "ugly dynamic SQL magic".
• clifton_h: Umm. Sp_executesql is not magic, it is the preferred method for most choices and guards perfectly against sql injection.
• Bruder Lustig: Why the downvotes?

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'.

• SQLDevDBA: SELECT COUNT(Value) INTO @ counter is more of an ORACLE syntax (assigning value to a variable/parameter) and will fail anyway. SELECT @ counter = COUNT(Value) would be the correct syntax for T-SQL. SELECT/INTO would select values into a new table.

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;