June 11, 2018 at 9:48 am
We are running SQL Server 2012 on Windows 2008. I have read that some reasons for needing to call a stored procedure from a view is because:
--Views do not accept parameters and can not use temp tables.
--Stored procedure can use parameters and can use temp tables (i.e., convert subqueries to temp tables for better performance).
Is there a workaround for not being able to use parameters in a view?
Is there a workaround for not being able to use temp tables in a view? (I.e. Common Table Expressions)
Since stored procedures can not be called from a view, we are considering using the SELECT * FROM OPENROWSET method to allow a view to call a stored procedure. I have read some comments that states the OPENROWSET method has drawbacks or is a hackish option. I have not seen any examples or explanations of these drawbacks. Can someone share what are the drawbacks and/or why it is called a hackish option?
Thanks in advance.
June 11, 2018 at 9:54 am
HookSqlDba7 - Monday, June 11, 2018 9:48 AMWe are running SQL Server 2012 on Windows 2008. I have read that some reasons for needing to call a stored procedure from a view is because:--Views do not accept parameters and can not use temp tables.
--Stored procedure can use parameters and can use temp tables (i.e., convert subqueries to temp tables for better performance).Is there a workaround for not being able to use parameters in a view?
Is there a workaround for not being able to use temp tables in a view? (I.e. Common Table Expressions)Since stored procedures can not be called from a view, we are considering using the SELECT * FROM OPENROWSET method to allow a view to call a stored procedure. I have read some comments that states the OPENROWSET method has drawbacks or is a hackish option. I have not seen any examples or explanations of these drawbacks. Can someone share what are the drawbacks and/or why it is called a hackish option?
Thanks in advance.
Check InLine Table-Valued Functions. That might give you what you need.
June 12, 2018 at 2:57 pm
HookSqlDba7 - Monday, June 11, 2018 9:48 AMWe are running SQL Server 2012 on Windows 2008. I have read that some reasons for needing to call a stored procedure from a view is because:--Views do not accept parameters and can not use temp tables.
--Stored procedure can use parameters and can use temp tables (i.e., convert subqueries to temp tables for better performance).Is there a workaround for not being able to use parameters in a view?
Is there a workaround for not being able to use temp tables in a view? (I.e. Common Table Expressions)Since stored procedures can not be called from a view, we are considering using the SELECT * FROM OPENROWSET method to allow a view to call a stored procedure. I have read some comments that states the OPENROWSET method has drawbacks or is a hackish option. I have not seen any examples or explanations of these drawbacks. Can someone share what are the drawbacks and/or why it is called a hackish option?
Thanks in advance.
Also consider the purpose of a view. Views are not designed to be all-purpose. Their focus is rather narrow relative to the questions you are asking. Additionally, consider the ramifications of using OPENROWSET over OPENQUERY. The former requires you to specify a security context, which could provide a hacker with information they could use to do considerably more damage at lot faster than they might otherwise be able to do. Ask yourself the most important question of all, which is WHY you were looking at views to begin with. The answer to that question will be rather informative.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply