using dynamic Sql with views

• nlynzaad

  SSC Enthusiast

  Points: 102

  More actions

  March 18, 2014 at 1:39 am

  #1697882

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

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  March 18, 2014 at 5:17 am

  #1697941

  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