Using Sql2005's OUTPUT

[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;

 

[David Browne]

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