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.
Best Answer
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 usesp_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:
Testing this out for a single call works just fine:
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.