Executing dynamic SQL within Select stmt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Executing dynamic SQL within Select stmt

Author

Topic

AceDBA
Starting Member

13 Posts

Posted - 2004-04-06 : 13:31:04

Hi again,

Here's the issue. We have a table that stores custom fields displayed on a web page. We have a column in this table called SQLText which contains an SQL statement which should be executed to pull the default value for that field. Here's the Select statement I'm working with:
select
FieldID
, [Name]
, [Description]
, DataType
, exec(SQLText) as Value // <-- This is obviously not working

How can I most efficiently execute a dynamic SQL statement and have it returned as part of the same record set?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-06 : 13:35:50

Efficient and dynamic sql should not be combined together. We will need to see DDL for your table and DML (INSERT INTO) for sample data, and the expected result set using the sample data in order to help out.

Can your system be redesigned? What you have to work with is not going to be efficient.

Tara

AceDBA
Starting Member

13 Posts

Posted - 2004-04-06 : 15:02:05

Tara, what do you recommend. For example purposes, lets say our "Fields" table contains the following record.

FieldID = 1
[Name] = 'Model Year'
[Description] = 'Current Model Year'
Data Type = 'String'
SQLText = 'Select Year(getDate())'

Now, I want all 5 columns returned, except the 5th column should execute the SQL statement in SQLText and contain the value returned by that SQL Statement?

Does this clarify it?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-06 : 15:04:00

Can you turn that SQLText into a User Defined Function instead?

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-06 : 15:05:09

SELECT FieldID, [Name], [Description], DataType, YEAR(GETDATE())
FROM SomeTable

You should not be storing a statement inside a column. Regardless if what you want can be done, it will not be efficient.

Tara

AceDBA
Starting Member

13 Posts

Posted - 2004-04-06 : 15:11:02

Tara, I know in that specific example this could be done, but we have many values that are dependent on yet another argument and this is the way we have decided to tackle the problem. Any thoughts on how I coule execute the SQLText and return the value in the same recordset? Even if it's not the best way to go.

Thanks.

AceDBA
Starting Member

13 Posts

Posted - 2004-04-06 : 15:12:30

How about a User Defined Function which takes in the FieldID as a parameter and then executes the SQLText and returns the value?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-06 : 16:01:38

quote:
Originally posted by AceDBA

How about a User Defined Function which takes in the FieldID as a parameter and then executes the SQLText and returns the value?

Hey, now THAT is an idea!

Okay, Ace, I gotta ask, just how many different variations of SQLText do you have? If you're really talking about default values, then I'd assume you could narrow it down to a defined set and then rather than storing it in a column at all, you could make it a function that takes in a parameter and returns the answer probably without having to query your table at all. I mean, how many different default values can you have?

And for that matter, if we're talking about default values for a field, why isn't that just defined in the column definition's default setting rather than storing a SQL statement inside a table? It seems to me that you're trying to make this TOO dynamic.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-04-06 : 23:51:58

I have to agree with Tara and Mark on this one. Every time I've tried to include dynamic SQL (or other code) as data, I ended up ripping my hair out and vowing never to do it again. It took 2 or 3 tries, but finally I abandoned it for good. Trust me on this, you WILL have never-ending problems with this approach, it's not worth the hassle.

AceDBA
Starting Member

13 Posts

Posted - 2004-04-07 : 15:42:13

Mark, we will have a lot of different defaults depending on the parameters being passed in. Trust me, if we could avoid the dynamic SQL, we would. So...back to the original question ^^^

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-07 : 18:54:38

Well then probably your idea of a UDF to find and execute the statement to then use those results in another SQL statement is the way to go. But I just have to throw in my opinion, based on the very little I know about your requirements, that this is sounding more complex than might be advisable. I have seen a lot of people come through here trying to make nearly everything dynamic rather than locking down a few rules or procedures. So, best of luck to you, but you might want to take 1/2 a day and question your approach to see if it's really what you want to do.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

AceDBA
Starting Member

13 Posts

Posted - 2004-04-07 : 18:59:35

Can't use UDF either since UDFs don't allow execution of dynamic SQL. Thanks for the info. Is anyone interested in knowing how this actually ends up being implemented?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-07 : 19:34:29

Sure, give us an update when you have a solution you like.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-08 : 02:51:20

Is SQLText going to be a single value, or will it differ from row to row? If it is going to be a single value for the entire recordset, you could try computing it outside the SELECT statement into , store the result in a variable, and then add this variable to the SELECT.


declare @Sql nvarchar(1000)
declare @year int

select @Sql = 'select @year = YEAR(getdate())'

exec sp_executesql @Sql, N'@year int out', @year out

select FieldID, [Name], [Description], DataType, @year as Value FROM Chiwawa

EDIT: So now this thread is beginning to make a little more sense.

The only way I think you can acheive this row-by-row is to do exactly that, process each row separately, one at a time. You could insert the rows into a temp table with an extra column to store the results of the dynamic Sql, use a while loop to iterate through the rows, execute the stored sql and update the new column with the output value.

OS

AceDBA
Starting Member

13 Posts

Posted - 2004-04-08 : 17:01:12

owais, just read your reply and it's funny because that is how I implemented it; using temp tables and then going through it row by row :D

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-08 : 18:37:56

i agree with the anti-dynamic sql sentiments, but if that is the route to go, aren't we overcomplicating?

how about just:


exec ('select a,b,c, ' + @otherexpression + ' as d from table')

- Jeff

AceDBA
Starting Member

13 Posts

Posted - 2004-04-09 : 12:16:56

Jeff, problem is each row in the table has it's own dynamic sql; hence the need for the temp table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-09 : 13:04:43

oh ..i get it now ... ok, here's one crazy way to do it if you can store all of your default formulas in a table and give them each a name.

** WARNING : Use at your own risk !!! ***


create table TestData( FirstName varchar(30), LastName varchar(30), Office varchar(10), FormulaName varchar(10))

create table Formulas(FormulaName varchar(10), Formula varchar(200))

go

insert into Formulas
select 'LastFirst', 'LastName + '', '' + FirstName' union
select 'Today', 'convert(varchar(20), getdate())' union
select 'FirstLast','FirstName + '' '' + LastName' union
select 'Silly','reverse(LastName)'

insert into TestData
select 'Jeff','Smith','Boston','LastFirst' union
select 'Nigel','Rivett','UK','Today' union
select 'David','M','Australia','FirstLast' union
select 'Tara','Duggan','California','Silly'

go

declare @SQL1 varchar(8000);
declare @SQL2 varchar(8000); 
declare @SQL varchar(8000);

set @SQL1 = '';
set @SQL2 = 'Case FormulaName ';

select @SQL1 = @SQL1 + ', ' + Formula + ' AS ' + FormulaName,
	@SQL2 = @SQl2 + ' when ''' + FormulaName + ''' THEN ' + FormulaName
from formulas


set @SQL = 'select FirstName, LastName, Office, ' +  @SQL2 + ' END as DefaultValue FROM (select * ' + @SQL1 + ' FROM TestData) a'

exec(@SQL)

go

drop table formulas
drop table testdata

The column at the end called "DefaultValue" contains the value generated based on the formula in the "formulas" table for each row of data.

cool, huh ??

Use at your own risk !! that's a pretty ugly hack but it does work....

- Jeff

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-09 : 18:13:33

WOW! Nice work Jeff. Now THAT is thinking outside the box.

I had to parse the final @SQL to see what you were doing. It looks like this:

select 
  FirstName, 
  LastName, 
  Office, 
  Case FormulaName  
    when 'FirstLast' THEN FirstLast 
    when 'LastFirst' THEN LastFirst 
    when 'Silly' THEN Silly 
    when 'Today' THEN Today 
  END as DefaultValue 
FROM (
  select 
    * , 
    FirstName + ' ' + LastName AS FirstLast, 
    LastName + ', ' + FirstName AS LastFirst, 
    reverse(LastName) AS Silly, 
    convert(varchar(20), getdate()) AS Today 
  FROM TestData
  ) a

Pretty damn tricky. But just a warning for AceDBA... there is a limit of 8000 characters for the size of the @SQL statement Jeff builds here, so depending on really how many distinct formulae you have, you still might be stuck. But, dang, Jeff, that's some nice work.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-09 : 23:44:53

quote:

Now THAT is thinking outside the box.

Thanks, man ! It is sometimes beneficial, sometimes confusing to those around me, but it seems I rarely think INSIDE the box ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources