treat content of field as part of the sql statemen

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 2005 Forums

Transact-SQL (2005)

treat content of field as part of the sql statemen

Author

Topic

john7652
Starting Member

2 Posts

Posted - 2010-08-20 : 00:20:33

Hi.
I have a table with a column that contains part of sql statement, specifically a "case when" statement.
when I use select to read the value of the field of rows in that table, I want the content of the field to be treated as part of the sql statement.
Let's say I have a table called 'table1' with one column called 'col1' and one row with the value of 'case when 1=1 then 1 else 0 end' in that row.
I wish to do something like this:
select col1 from table1

so the result will be:
1
instead of
case when 1=1 then 1 else 0 end

how do I do that? please help me nice people.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-20 : 03:34:51

Try this -


declare @sql as varchar(max)
SET @sql = ''
select @sql = @sql + 'SELECT ' + st + ' UNION ALL ' from table1
SET @sql = LEFT ( @sql, LEN(@sql) - 10 )
EXEC ( @sql )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

john7652
Starting Member

2 Posts

Posted - 2010-08-20 : 11:19:10

Thanks Vaibhav T,
On one hand it works but on the other hand I'm preventing from assigning values to variables and retrieving data at the same time, so let me put my example this way,
on table1 I also have another column, 'col2' which contains a number.
The 'case when' statement refer to this number.
So for instance, col1 might contain a value such as:
'case when col2>10 then 2 else 8 end'.

I tried to add col2 to your example. One way I tried is:

select @sql = @sql + 'SELECT  ' + col1 + ',col2 from test UNION ALL ' from test

this doesn't give error but it creates duplicates because for every row in the table the select query runs, so I get all possible combination, and of course, I need only those that match according to the table.

I also tried:

select @sql = @sql + 'SELECT  ' + col1 + ' test UNION ALL ',col2 from test

but that of course give the error of:
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Let me tell you what it's about, I have a table or a query that shows for each salesperson how much sales they had on a period of time, and for each employee, on the employees table, there's a column that the manager puts in a logic for the commissions, so for instance:

case when sales<2000 then sales*0.1 else case when sales<4000 then sales*0.2 end end

I tried to avoid giving such a specific example so it won't look complicated but I guess it's necessary.

so you see, I built a query that takes the sales and put it next to the employees's commission logic so it looks like this:

John,2000,case when sales<2000 then sales*0.1 else sales*0.2+200 end
Tom,2500,case when sales<2000 then sales*0.15-50 else sales*0.25 end

The logic get very complicated and varies from employee to employee but in any case it's a valid "case" statement that contains the word 'sales' which is a column in the query.

Also Vaibhav T, I noticed you used "union". What if my query has hundreds of records? I know that such a union statement can cause performance issues.

I don't know much about stored procedures, but maybe we can use that to solve my problem?

anyway, I am very thankful and I'm aware that my problem is kind of challenging so I really appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 12:19:43

cant understand why you store statement itself as values in table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-21 : 07:11:22

Visakh has been asked valid point.
I dont think you need to store statement.
as if you are using only values then you can do it inline.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources