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.
count(*)
instead ofcount(value)
?