find and update

[kalyan]

I have a table variable inside a stored procedure that contains select statements as the row values... i need to find out such columns and execute those statements and update the same column with the output value...

create proc usp_procname(@val1 varchar(100))

.....

....

declare @generate table( column1 varchar(max),column2 varchar(max)... column20 varchar(max)

select * from @generate

i need to findout the column values that contain select statements execute them and update the same column

CREATE TABLE #MyTestTable
(
ID INT,
DESCRIP VARCHAR(MAX)
col3 varchar(max)
)

INSERT INTO #MyTestTable
VALUES(1, 'TRUSTEE','aaaa')

INSERT INTO #MyTestTable
VALUES(2, 'select col2 from table3 where id=1','aaaa')

INSERT INTO #MyTestTable
VALUES(3, 'select col2 from table3 where id=1','select col5 from table3 where id=1')

so if i specify as
select * from #MyTestTable

i do get as follows

1, 'TRUSTEE','aaaa'
2, 'select col2 from table3 where id=1','aaaa'
3, 'select col6 from table3 where id=1','select col5 from table3 where id=1'

now i have to find the select statements which on execution returns some values and updates the same column

so if i specify again as
select * from #MyTestTable

i should get the following output
1, 'TRUSTEE','aaaa'
2, 'fgasf','aaaa'
3, 'ghsdhs,'rt'




can anybody help me on this

earlier help is highly appreciated..

Regards

Kalyan

[Erland Sommarskog]

Post by kalyan
CREATE TABLE #MyTestTable
(
ID INT,
DESCRIP VARCHAR(MAX)
col3 varchar(max)
)
INSERT INTO #MyTestTable
VALUES(1, 'TRUSTEE','aaaa')
INSERT INTO #MyTestTable
VALUES(2, 'select col2 from table3 where id=1','aaaa')
INSERT INTO #MyTestTable VALUES(3, 'select col2 from table3 where
id=1','select col5 from table3 where id=1')

A hopeless case if I ever I saw. I hope you have influence over this
design because it needs fixing.

The worst problem is that there can be more than one value in the same
cell. A fundamental principle in relational databases is "no repeating
groups", and if you violate that rule it gets painful.

Of course, you specific problem may have tons of constraints:

1) At most two values per cell.
2) SELECT statement does never include a comma in itself.
3) The values from the table are also guaranteed to be void of
commas.
4) The SELECT statements are always syntactically correct.

But how long would it take before any of these constraints are violated?