Code in a column- Select results of code

Question

Code in a column- Select results of code

Stef Teal

SSCrazy

Points: 2382
More actions
January 26, 2007 at 4:07 pm

#118639

If I have the following 2 fields in a table, is there a way to select the Id and the RESULT of the Select stmt in the Code column?
Id   Code
4    'SELECT SubjectDisplay FROM PM_RuleSubject WHERE RuleSubjectId = 1'
2    'SELECT SubjectDisplay FROM PM_RuleSubject WHERE RuleSubjectId = 7'
1    'SELECT SubjectDisplay FROM PM_RuleSubject WHERE RuleSubjectId = 11'
3    'SELECT ObservationTypeCode FROM PM_ObservationType WHERE ObservationTypeId = 56'

Viewing 10 posts - 1 through 9 (of 9 total)

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

you can only do this with dynamic sql. seems like a bad idea to me though.

basically you'll be executing arbitrary sql blindly. anyone with access to this table could cause a lot of damage to your system.

for tips on dynamic sql, read this article:

http://www.sommarskog.se/dynamic_sql.html

---------------------------------------
elsasoft.org

Stef Teal SSCrazy Points: 2382 More actions · Answer 2

Very good point, but the code does not actually exist in a persistent table. I construct the code statements from several fields in 2 different tables, and the code field is actually in a table variable table. Also, there is no interface/access to these tables, so I think the risk of injection is very low.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

in that case have a look at sp_executesql in the article I linked to. also it's documented in BOL.

In any case Erland's article is a must read for anyone cosidering to use dynamic sql.

---------------------------------------
elsasoft.org

Michael Lysons SSCertifiable Points: 6491 More actions · Answer 4

If you are contructing the code then you can add the ID number in yourself, e.g.

'SELECT SubjectDisplay FROM PM_RuleSubject WHERE RuleSubjectId = 1'

becomes

'SELECT 4, SubjectDisplay FROM PM_RuleSubject WHERE RuleSubjectId = 1'

Then you can use jezemine's dynamic SQL suggestions.

Stef Teal SSCrazy Points: 2382 More actions · Answer 5

I ended up not putting the code in a field, and instead went this route. Let me know if anyone wants me to post the entire code, but I think this is doing the trick in a reasonably efficient and safe manner.

WHILE @ctr <= @numRecs

BEGIN

SET @strSql = (SELECT 'SET @colHeader = (SELECT ' + ColumnName + ' FROM ' + ColumnSource + ' WHERE ' + IdField + ' = ' + CAST(ColumnID AS varchar) + ')'

FROM @tblIdFields

WHERE RecNo = @ctr)

EXEC sp_executesql @strSql, N'@colHeader varchar(50) out', @colHeader out

UPDATE @tblIdFields

SET ColumnHeader = @colHeader

WHERE RecNo = @ctr

SET @ctr = @ctr + 1

END

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 6

Since you're running SQL 2k, and if you want to rum multiple statements It might also be worth check out XP_EXECRESULTSET. Then all you need to do is a little tap dancing to REPLACE 'SELECT', with SELECT '4' AS ID, subjectDispaly FROM...

EXEC XP_EXECRESULTSET N'SELECT REPLACE(Code, ''SELECT'', ''SELECT '''''' + CAST(id AS VARCHAR) + '''''' AS ID, '') AS Code FROM YourCodeTable', N'YourDatabase'

Just be careful, like jezemine says, with dynamic SQL.

SQL guy and Houston Magician

jezemine SSCrazy Eights Points: 8059 More actions · Answer 7

I wouldn't call this safe.

anyone that can control the process that inserts data into @tblIdFields can do damage to your server.

how does data get into this table variable? if you are taking user input and inserting it into @tblIdFields, then you are in trouble.

---------------------------------------
elsasoft.org

Stef Teal SSCrazy Points: 2382 More actions · Answer 8

All of the fields used to populate table variable @tblIdFields are from static, application architecture tables. The application itself is a reporting tool, and accepts no user input (except choices thru drop-downs) and the user can't save anything. So I think it's safe... ?

jezemine SSCrazy Eights Points: 8059 More actions · Answer 9

well, ok. as you can tell I am a bit paranoid when it comes to dynamic sql.

If the user is just selecting what columns to select on to construct a dynamic search condition, you ought to give this a read as well:

http://www.sommarskog.se/dyn-search.html

---------------------------------------
elsasoft.org