Using Sql2005's OUTPUT

7 Mar 2007 10:45 PM

Arthur Dent

Hello all...

Here is what i want to do (in VB code)...

<code>
Dim SQL As String = "SELECT ROWID FROM (INSERT INTO STATIC_CONTENT (NAME,
CONTENT) OUTPUT INSERTED.ROWID SELECT 'Contact Us', '') SRC"
Dim newId As Integer myDbObject.ExecuteScalar(SQL)
</code>

Assume that myDbObject has already been set up with all the necessary
connection strings and junk.
The question revolves around using ExecuteScalar to return the value from an
INSERT-OUTPUT clause.
I can 100% guarantee that this is a single-row insert everytime.
This table (STATIC_CONTENT) has a trigger on it to set the update-date every
time it's updated.

Is this possible somehow? short of writing my own overloaded ExecuteScalar
that would turn this into a whole big T-SQL BEGIN/END block with temporary
variables and all that mess?

Thanks in advance,
- Arthur Dent;

8 Mar 2007 4:23 AM

David Browne

Show quote

"Arthur Dent" <hitchhikersguideto-n***@yahoo.com> wrote in message
news:F161A104-73F3-46B1-A7AB-85824FD0E966@microsoft.com...
> Hello all...
>
> Here is what i want to do (in VB code)...
>
> <code>
> Dim SQL As String = "SELECT ROWID FROM (INSERT INTO STATIC_CONTENT (NAME,
> CONTENT) OUTPUT INSERTED.ROWID SELECT 'Contact Us', '') SRC"
> Dim newId As Integer myDbObject.ExecuteScalar(SQL)
> </code>
>
> Assume that myDbObject has already been set up with all the necessary
> connection strings and junk.
> The question revolves around using ExecuteScalar to return the value from
> an INSERT-OUTPUT clause.
> I can 100% guarantee that this is a single-row insert everytime.
> This table (STATIC_CONTENT) has a trigger on it to set the update-date
> every time it's updated.
>
> Is this possible somehow? short of writing my own overloaded ExecuteScalar
> that would turn this into a whole big T-SQL BEGIN/END block with temporary
> variables and all that mess?
>

Not quite sure what the question is, but you can't use an INSERT OUTPUT
query as a subquery. Just send

INSERT INTO STATIC_CONTENT (NAME, CONTENT)
OUTPUT INSERTED.ROWID
SELECT 'Contact Us', ''

as the query and it will return the ROWID.

David