CLR vs Dynamic SQL?

Question

CLR vs Dynamic SQL?

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
December 4, 2008 at 5:21 am

#128988

Hi all,
I've run into a problem with some code that I am trying to write 🙁
Basically I wanted to creat a function that would return a result set as a table(no problem here).
However the code in the function might change when it's run . Meaning that I wanted to use Dynamic sql. This I now know is not possible in SQL 🙁
There are two solutions that I could think of.
1- Make the function simple return the dynamic string and then in my Procedure execute the dynamic SQL into a temp/table variable and then join onto this table,
Instead of my Tabular function like I first wanted.
2 - Use a CLR to return the dataset based on the dynamic SQL so that the function can call the CLR as a dataset and then join onto the function.
I'm not 100% sure which approach is better or if there are any other ways of getting around this.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 15 posts - 1 through 15 (of 16 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Can you post more about what you are trying to accomplish rather than how you are trying to do it? When you find yourself trying to run dynamic SQL in a T-SQL function, you are most likely into a design problem rather than an implementation problem. You are probably just mis-using T-SQL functions.

From your description in which you build a string and execute it - returning the results into a temp table, you probably can accomplish what you are trying to do using a stored procedure (or two) rather than functions.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Sorry about that:

Ok so this is what is used to create the dynamic query or should I say what is returned here is what I need to use to get my data.

SELECT @sql = 'SELECT DISTINCT [' + [FieldName] + ']

FROM [' + [TableName] + ']' +

' WHERE [' + [FieldName] + '] IS NOT NULL' +

' ORDER BY 1'

FROM DataStore

WHERE FieldId = @FieldId

@FieldId is to get a single row from a table which contains the table I need to select from the fieldName I need returned (this will be joined onto other tables later on).

As you can see the reason it's done dynamicly is cause I don't know the table name and or the field name 🙁

The design is floored yes and I can't change it 🙁

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

What are you planning on using the output of this for?

It's a rather ugly design regardless, but it will make a big difference in what you can do if you intend you use the output in the select list of a query or if you are returning it to an application. You still need to post more information to get help with this.

vaseem-773819 Ten Centuries Points: 1015 More actions · Answer 4

Hi

if your intention is to only return table by dynamic sql which later on you will join with other tables then why you are not writing stored procedure instead of using dynamic sql in table valued function. since all the process you can write in sp also.

can u explain your complete requirement.

Thanks

Vaseem

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Ok let me see if I can supply for info here.

The design of the system is not mine and I can't change it! So with regards to design good or bad we have to accept what we have 🙁 (bad I know)

basically our website is driving by SQL server, each field on the screen is populated using a field from a table which is all stored in a table e.g

[WebfieldName],[SQLFieldName],[SQLTableName]

Now each use can only see set data per field.

For example I may only be allowed to see all client names starting with "A" and someone else may only be allowed to see clients starting with "B".

As result when I populate a web page, that lists clients, I need to be able to restrict the list data shown for the various fields on the page based on the user.

Because peoples have restrictions on different fields that means my main query needs to restrict different fields on different tables depending on who they are.

Hence the need to first get a subset of data in this case only clients name starting with "A" and then join that dataset onto whatever else is shown on the screen.

Does this make more sense?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

The main reason I'm asking this questions, is because yes this can be done all in sql with pretty good performace.

It just seems weird to solve this problem with a CLR here is why I think this...

SQL is a database engine great for getting data.

Why would you ask SQL to ask another language to get the data and pass it back to SQL to use?

just seems weird?!?!

What do people think?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

david.carnley SSC Enthusiast Points: 159 More actions · Answer 7

I don't see where your "per user" criteria are coming from... how do you know that user1 sees clients starting with A and user2 those with B, etc...

Also do different users, in the same web fields, see data from different table/field?

I think you should be able to generate SQL statements based on query results but you haven't provided enough detail for me to help you write those queries...

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

Sorry there is user table that basically has the user Id with the field id and what they can and can't see, and yes the query will use different tables and different fields depending on there restrictions.

I know this can be done that's no the problem, the problem is that because I don't know the tables when the proc executes for that user I need to build the sql each time the query runs, but I can't do that in a function , so someone in my team recommends a CLR, I'm jsut wonder if I should stick to procs rather than use a CLR?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

david.carnley SSC Enthusiast Points: 159 More actions · Answer 9

don't use a function? use a stored rpoc instead, tehn you can use dynamic sql execution? is that possible in your app?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 10

yeah I mean I could have a proc that runs the dynamic sql and the dataset returned could be inserted into a temp table and the the main proc just needs to join to the temp table.

That would be my choice of solution. I'm just wondering if this would be better than a CLR or not?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

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

Create a temporary stored procedure, instead. You know... one that begins with a "#" sign. 😉

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 12

mmm ok Jeff you confused me.

Mainly cause I didn't know you could do that to start with. Secondly I'm not sure how that will help?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 13

Jeff typed that very sarcastically - although I am sure he presses the keys differently (and probably hunches a bit) when he is joking, it sometimes does not show up well in the post.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 14

thanks for that 🙂

I thought that might be the case, but thought I would check just in case Jeff had some magic in his pocket he he he

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life