HLRosenberger
asked on
SQL question; ExecuteScale
I have this compound SQL statement that I execute using ExecuteScaler.
UPDATE IHPS_case_note SET safety_plan_revision_reque st = 'xxxxxxxx' FROM IHPS_case_note INNER JOIN ihps_case ON ihps_case.ID = ihps_case_note.fk_caseID INNER JOIN ihps_create_log ON ihps_create_log.ID = ihps_case.FK_createlogID WHERE DHS_evaluationID = '341287' AND ihps_create_log.incrementa l = 1;
SELECT IHPS_case_note.ID from IHPS_case_note INNER JOIN ihps_case ON ihps_case.ID = ihps_case_note.fk_caseID INNER JOIN ihps_create_log ON ihps_create_log.ID = ihps_case.FK_createlogID WHERE DHS_evaluationID = '341287' AND ihps_create_log.incrementa l = 1
This works fine, as it returns the Primary Key of the record that I just updated. Is there a better way to do this, without duplicating the whole UPDATE SQL statement?
UPDATE IHPS_case_note SET safety_plan_revision_reque
SELECT IHPS_case_note.ID from IHPS_case_note INNER JOIN ihps_case ON ihps_case.ID = ihps_case_note.fk_caseID INNER JOIN ihps_create_log ON ihps_create_log.ID = ihps_case.FK_createlogID WHERE DHS_evaluationID = '341287' AND ihps_create_log.incrementa
This works fine, as it returns the Primary Key of the record that I just updated. Is there a better way to do this, without duplicating the whole UPDATE SQL statement?
Try this, I know it works in T-SQL in a query window, I believe it will work from being called from .Net as well. The OUTPUT clause will return values from the INSERTED Psudo table just like you use in triggers.
UPDATE IHPS_case_note SET safety_plan_revision_request = 'xxxxxxxx'
OUTPUT INSERTED.ID
FROM IHPS_case_note INNER JOIN ihps_case ON ihps_case.ID = ihps_case_note.fk_caseID INNER JOIN ihps_create_log ON ihps_create_log.ID = ihps_case.FK_createlogID WHERE DHS_evaluationID = '341287' AND ihps_create_log.incremental = 1;
ASKER
I not sure that I need ExecuteScaler. What I want is the simpliest way to return back the Primary Key of the record that I updated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
BTW: did you mean .Net ExecuteScaler?