Dynamic Query in SQL Server Function

Question

Dynamic Query in SQL Server Function

jgaull-522023

SSC Enthusiast

Points: 110
More actions
October 18, 2007 at 10:24 am

#70784

Hi,
I have several tables from a vendor in my database that are not named intuitively and their column names are not intuitive either. These tables also contain some columns that are codes given to us from another vendor so they need to be translated. By the way, all of the tables provided by the vendor have the same primary key which can be used to join them all.
I needed a way to query these tables and translate the vendor codes into my company's codes so I created a table to help. Among other things, the table contains the field in my company's lingo, the column and table name that the vendor uses, a code from the vendor, and the corresponding code we use. If there are 3 possible codes for a field, my table has 3 rows. For Example:
MyField VendorTable VendorField VendorCode MyCode
State A1 ST ALAB AL
State A1 ST ALAS AK
State A1 ST ARIZ AZ
Sex B5 SX MAL M
Sex B5 SX FEM F
What I'd like to do is provide a user-defined function where I can pass in "MyField" and the primary key in all of the vendor tables and the function will return the translated code. I was well on my way to resolving this but since I don't know the name of the table I need to query until run time, I needed to execute a dynamic query in the function. Apparently, SQL Server doesn't allow this.
I also tried changing the function to a stored procedure but I can't execute the stored procedure in the Select clause of a statement.
Any ideas? High level thoughts are definitely welcome as I don't need an exact answer handed to me.
Thanks!

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

You could try creating a table valued user defined function that uses dynamic sql to run a query against the table you have and then generate a select statement. The command you'd need within the UDF would be:

EXEC 'some string'

Build the string by loading parameters from your table

SELECT @MyTable = TABLE_Name

FROM LookupTable

SET @Commandstring = 'SELECT * FROM ' @MyTable

EXEC @CommandString

That'll give you something to work with. It's probably not an optimal method.

What about looking at Synonyms and using those instead?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Per the BOL:

In multistatement table-valued functions, function_body is a series of Transact-SQL statements that populate a TABLE return variable.

Also (definition of Exec - notice it doesn't mention table-valued functions)

Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.

So....no cigar on that approach.

I don't think you have anyway of returning the results of dynamic query from a function.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Partitioned view might be a possibility...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jgaull-522023 SSC Enthusiast Points: 110 More actions · Answer 4

Thanks for the ideas guys!!!!

For now, I created a stored stored procedure which declares a variable for each column I want to return and then I call another stored procedure (per column) that sets the variable equal to the correct value. I know this is inefficient but it'll work for now as I keep researching other options.

EXEC RetrieveData 'State', @primarykey-2, @State OUTPUT

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

If I understand this issue correctly, I strongly suggest you simply create views.

With a view on the table you can translate the column names AND translate the codes in one area without needing to call any procedures or other code when selecting data from this table.

Btw, you actually can "query" stored procedures if you use OPENROWSET.

Best Regards,

Chris Büttner