March 18, 2014 at 1:39 am
Sorry to bump an old thread but thought it might be useful for some people in the future.
I've had a similar problem where I needed a view that used dynamic SQL and found a workaround. Although using Dynamic SQL in a view is not possible there is a workaround using a stored procedure and a openrowset call from the view. Below a sample code snippet, its not suppose to do anything meaningful except provide an idea of how to use this workaround:
Procedure
Create sp_SomeProcedure
as
Declare @sql nvarchar(1000)
Set @sql = 'SELECT Col1, Col2, Col3 From SomeTable'
Exec sp_ExecuteSQL @sql
View
Create View SomeView
as
SELECT Col1,
Col2,
Col3
FROM OPENROWSET('SQLNCLI', 'Server=SERVERNAME;uid=USER;pwd=PASSWORD;', 'SET FMTONLY OFF;Exec SomeDB.dbo.sp_SomeProcedure With Result Sets((Col1 varchar(128), Col2 varchar(128), Col3 varchar(128)))')
March 18, 2014 at 5:17 am
TVF may be. It can be used in FROM, you know.
create function SelectEmployees (@version varchar(10))
returns @t table (
-- fields compatible with RetailXX.dbo.Employees tables
)
as
begin
if @version = 'Dev'
insert @t
select * from RetailDev.dbo.Employees
else if @version = 'QA'
insert @t
select * from RetailQA.dbo.Employees
else
insert @t
select * from Retail.dbo.Employees
return
end
Rgds
Serg
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy