sql update from select statement

Question

Sorry for the length of this, I'm trying to give a lot of info to avoid non-relevant solutions.

My goal is to embed at least 1 UPDATE statement into a SELECT statement so that I have a chance to update some computed values at the instant before the select statement runs (think of it like a TRIGGER on SELECT). VIEW is not in immediate solution, since I'm constrained by the system I'm using (see below).

I'm customizing a 3rd party commerical ERP that is weak on features (system will remain nameless! -- upshot is you haven't heard of it, but its not home grown either). It has a canned query facility where I can use a text/GUI to build a SELECT query. Once I save a query, the users can click on the query to execute it and see the results. ERP runs atop MS SQL Server 2000; version upgrade is NOT in the cards right now. I can write whatever feature set I need outside of the ERP in whatever language I want, I have done this in the past, if the features warrant it. But my user community finds it easier when my customizations can be done in the ERP system.

The query can be arbitrarily complex, but the ERP package builds the SQL Select statement itself. Internal to the compiled ERP is something like this (this is just a guess!):

"SELECT " + fieldList + " FROM " + tableListAndJoins + " WHERE " + whereCond

The GUI builder helps novice users build the fieldList and so on but you can bypass it and write the clauses in text as long as the SQL is valid when combined as above.

I can't seem to find an incantation to run a stored procedure as a side effect of a SELECT statement, whether it's in the select clause, where clause, etc. I really don't care how I jailbreak the system -- a stable SQL injection attack would be fine, as long as it didn't mean I had to modify the security of underlying sql server itself. I've looked at UDFs, but you can't put an UPDATE statement into a scalar UDF, and it doesn't make sense to try to modify the return of a table UDF (or does it?). If you can UPDATE from within a VIEW then I would like to see an example, but I realize I can use a VIEW to compute columns and that is not the solution I am looking for. I read a suggestive statement online about being able to use some sort of XP_ to accomplish this, but as to which XP_ or how to do it, I don't know.

this question is NOT a solution, in and of itself: http://stackoverflow.com/questions/545904/updating-a-table-within-a-select-statement

Answer 1

Try using dynamic sql in the stored procedure as stated on the last reply here

Linked so that the original author can get the credits he/she deserves, and hopefully will be helpful to you.

And since you want SELECT before the UPDATE, you can modify the dynamic sql as in the link I posted, to do the SELECT first.

Answer 2

I am not sure I understand the constraints of your situation, but can you not just run two statements at once, such as:

string sql = "update MyTable set x=y;select x from MyTable;";

Answer 3

Would a stored function be an option? You can call those more seamlessly (in MySQL at least) than stored procedures -- instead of "call PROCEDURE_NAME(x)" you can just use "SELECT FUNCTION_NAME(x)".

Answer 4

My guess with the XP is that you would write your own XP to do the update and include it in the query somehow. Whether or not that would work and where in the query it should go so as to be run before SQL looks at your data is completely beyond me.

It sounds like you've tried just about everything I would try. I feel for you because it'd probably be rather easy to update the state after you ran the select.

Answer 5

I can't think of any way to combine a SELECT with an UPDATE in SQL 2000 (though in 2005 and up, the OUTPUT clause is available). However, it looks like you get three string values (fieldList, tableListAndJoins, whereCond) that get concatenated together with "SELECT", "FROM" and "WHERE", and assuming they don't do some serious SQL injenction-like code detection, you might be able to kludge together something like this:

fileList = "NULL where 1 = 0; UPDATE MyTable set MyColumn = 'Whatever' where SomeColumn = 'Criteria'; SELECT MyColumn"

tableListAndJoins = "MyTable"

whereCond = "SomeColumn = 'Criteria'"

[The semicolons are actually optional, and might not even work in SQL 2000 -- they just make it clear where one command ends and the next begins.]

The downside of this is that you'll get back two data sets. The first will be an empty one-column set (alias that NULL if you want a name to the column), and the data you want will be in the second set. Other work-arounds might be possible, depending on how these three values are used and how errors are caught. (Let that first query generate and error, and hope the update and the second query go through?)