Solved

SQL question; ExecuteScale

Posted on 2013-01-04
5
254 Views
Last Modified: 2013-01-10
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
Comment
Question by:HLRosenberger
  • 2
  • 2
5 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38746086
could you please clarify your question and why do you nee to use ExecuteScaler

BTW: did you mean .Net ExecuteScaler?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38746223
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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 38750838
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
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38751036
Did you try the OUTPUT clause yet?  Did it not work?
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 38763935
Thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now