Dynamic SQL

Question

Post reply

Dynamic SQL

joel fransson

SSC Enthusiast

Points: 109
More actions
April 4, 2007 at 1:41 am

#91090

Hello,
I have a problem when trying to write a Dynamic SQL query.
I Have a UserDefined funtion to which I want to send a variable table name. I want to use this tablename to run a query from in my method. E.G CREATE FUNCTION dbo.FlattenActors (@agentIID uniqueidentifier, @table varchar(127) ) This function should do some recursive stuff based on a start table and a ID.
But I can't seem to get it to work. I have tried to write a StoredProcedure in which I Exec the dynamic query, and execute this SP in my function, but I get all kinds of errors.
Any ideas?

Thanks
Joel Fransson

Viewing 7 posts - 1 through 6 (of 6 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

You can't use dynamic SQL in a function.

http://www.sommarskog.se/dynamic_sql.html

N 56°04'39.16"
E 12°55'05.25"

joel fransson SSC Enthusiast Points: 109 More actions · Answer 2

Thanks for the reply. Do you have any other ideas for a solution to my problem?

Could I use stored procedures instead of functions perhaps?

Can a I make a stored procedure that can return a table and be used like a UserFunction?

E.G. Select * FROM Table WHERE Table.IID IN UserFunction()

(Uhmm that might not be the correct SQL syntax but you get the idea 🙂 )

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Yes, you can make a table-valued functions and use as any other table or view.

Select t1.* FROM Table AS t1

INNER JOIN fnUserFunction(1) AS u ON u.SomeCol = t1.ThirdCol

Note that if you want to put a column value in the function parameter like this

Select t1.* FROM Table AS t1

CROSS APPLY fnUserFunction(t1.ColG) AS u ON u.SomeCol = t1.ThirdCol

you must use SQL Server 2005.

N 56°04'39.16"
E 12°55'05.25"

Will SQL 4 Food SSCrazy Points: 2253 More actions · Answer 4

Peter Larsson is (of course!) right that you can't execute dynamic SQL in a user-defined function. This is unfortunate, but you can get around it. If you need to "return" a table of results from a stored procedure, you can insert them into a real table, temp table, or a table variable fairly simply. Here's an example:

-- Simple stored procedure example

create

procedure uspTest as

begin

-- logic here...

-- SELECT statement of results to return

select [name], id from sysobjects where xtype = 'U'

end

go

-- Using a table variable defined to match order and datatype of sproc's record set

declare

@t table ([name] nvarchar(127), id int)

-- Insert with EXEC

insert

into @t ([name], id)

exec uspTest

-- Use results however you need to

select

* from @t

Hope this helps!

Carter Burleigh

But boss, why must the urgent always take precedence over the important?

schleep SSChampion Points: 12585 More actions · Answer 5

Not sure about SQL2005, but in 2000, you cannot Insert...Exec into a table variable.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 6

Joel, schleep is correct. You could, however, use a base table or a local temp table.

There is no "i" in team, but idiot has two.