Dynamic SQL that can be accessed from View or table-valued function

Question

Dynamic SQL that can be accessed from View or table-valued function

srivishiva

Old Hand

Points: 336
More actions
March 23, 2010 at 11:01 am

#222177

Hi,
I would like to Create a view or table-valued function that would call a stored procedure or script which contains dynamic SQL and finally returns a table
I am currently facing two problems
1.Executing stored procedure is not allowed in a view. I have written the SP with dynamic SQL
2. Dynamic SQL is not permitted in the user defined functions.
Ultimately, I would like to query something like this
select * from view1
or
select * from dbo.function1
but the script contains the dynamic SQL
Any ideas on how to accomplish this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i'd want to see the proc, i's quite possible whatever you are doing does not have to be done with dynamic sql.

it's possible to create a view that has an openquery command as it's body; that's how you can sneak a proc results into a view:

CREATE VIEW MYVIEW

AS

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srivishiva Old Hand Points: 336 More actions · Answer 2

Thanks for your reply and your solution. I don't know if it works because I have to pass a parameter to the procedure. Let me try that option.

My stored procedure is too big so I can't send it. But I am using dynamic SQL because one of the columns in the table 2 changes every time depending on the result of another query

set @sJoin = ' on Table2.Customer_' + @SourceColumn + ' = table1.LocalKey'

Thanks again

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's an example of how two seemingly different queries can be combined as a function without using dynamic sql;

the param being passed decides which results to return. this might give you an idea how to rewrite your code as a function.

CREATE function myfunction(@myval int)

returns table

as

RETURN SELECT * FROM

(

select

1 As WhichTable,

everything

from TableA

inner join AnotherTable on TableA.ID = AnotherTable.ID

WHERE 1 = @myval

UNION ALL

select

2 As WhichTable,

everything

from TableA

inner join ADifferentTable on TableA.ID = ADifferentTable.ID

WHERE 2 = @myval

) X

WHERE X.WhichTable = @myval

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

srivishiva Old Hand Points: 336 More actions · Answer 4

Thanks again Lowell for your suggestions.

I am not sure if this solution works because the variable @Sourcecolumn that I mentioned in my earlier code is the column name of the table. So, if we use the variable as you mentioned, the variable returns the value of the variable ,

so, the difference between using my code and your suggestion would be as below

My dynamic query returns the following when it is executed

on Table2.Customer_ID = table1.LocalKey

Your static query returns

on 'Customer_ID' = table1.LocalKey

So, it will be like the customer_id will be the value of the variable but actually it should be the name of the column

May be we can think of joining the system table

Please let me know if it is not clear