Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SQL question; ExecuteScale

I have this compound SQL statement that I execute using ExecuteScaler.

UPDATE IHPS_case_note SET safety_plan_revision_request = '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.incremental = 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.incremental = 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?
0
HLRosenberger
Asked:
HLRosenberger
  • 2
  • 2
1 Solution
 
Eugene ZCommented:
could you please clarify your question and why do you nee to use ExecuteScaler

BTW: did you mean .Net ExecuteScaler?
0
 
Chris LuttrellSenior Database ArchitectCommented:
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;

Open in new window

0
 
HLRosenbergerAuthor Commented:
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.
0
 
Chris LuttrellSenior Database ArchitectCommented:
Did you try the OUTPUT clause yet?  Did it not work?
0
 
HLRosenbergerAuthor Commented:
Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now