Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

The OUPUT Command Expand / Collapse
Author
Message
Posted Thursday, November 1, 2007 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2007 2:26 AM
Points: 2, Visits: 3
Hi,

I m NOT using storedprocedure. I m using the ADO command object which will stored the compiled command text. I m passing only parameter value in UPDATE command text. And it doesn't return value or parameter.

How can i do this.

Pls see my command syntex below.


Set rsPending = New ADODB.Command
Set rsPending.ActiveConnection = cnnDefault
rsPending.CommandType = adCmdText

rsPending.CommandText = "UPDATE vTableServerAllocationView _
& " SET EG0_User='varUserid' OUTPUT INSERTED.Sr_No,INSERTED.Doc_No WHERE Sr_No =?"

rsPending.Parameters.Append rsPending.CreateParameter("SN", adDouble, adParamInput)

rsPending.Prepared = True
Post #417414
Posted Friday, June 6, 2008 4:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,406, Visits: 1,400
Nice description....... very useful one:)


Post #512781
Posted Friday, June 6, 2008 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:59 PM
Points: 7,105, Visits: 15,445
Vijay - I think that if you were to try to execute that as a recordset - you would get data back, just as if you had a "straight" SELECT statement in there.

Hard to tell what's going on now, though - the code you posted don't actually execute the ADO command. In other words - in "traditional" ADO - you'd instantiate a recordset object and execute the command into it, thus giving you access to the rows being OUTPUT.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #513033
Posted Friday, June 6, 2008 11:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
vijay_download (11/1/2007)
Hi,

I m NOT using storedprocedure. I m using the ADO command object which will stored the compiled command text. I m passing only parameter value in UPDATE command text. And it doesn't return value or parameter.

How can i do this.

Pls see my command syntex below.


Set rsPending = New ADODB.Command
Set rsPending.ActiveConnection = cnnDefault
rsPending.CommandType = adCmdText

rsPending.CommandText = "UPDATE vTableServerAllocationView _
& " SET EG0_User='varUserid' OUTPUT INSERTED.Sr_No,INSERTED.Doc_No WHERE Sr_No =?"

rsPending.Parameters.Append rsPending.CreateParameter("SN", adDouble, adParamInput)

rsPending.Prepared = True


Vijay,

It doen't appear that you are writing in .NET. Too bad. If I'm wrong then...

I'm sure I'll get a lot of flack for suggesting this, but it works, and under the proper circumstances, there is nothing at all wrong with it. Forget the parameter. If your code returns a record in your query window, then retrieve that record with a DataReader. Just replace the code after setting the CommandText property, with the following.

Dim dr As SqlDataReader
dr = rsPending.ExecuteReader
If dr.HasRows = True Then
dr.Read()
'Collect the field data from dr('FieldName' [or ordinal]).ToString for each column.
End If

By the way... GOOD article. GOOD discussion.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #513105
Posted Friday, June 13, 2008 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 1:29 AM
Points: 46, Visits: 66
also be aware that using the output command to log/move the deleted data in a log or archive table, that this might affect performance (depending on how much records you process).
Instead of 2 times the related index scans when performing first an insert into the log or archive table and afterwards the delete, the output clause has only 1 time the index scan, but performs a table spool that can cause even more IO.



Post #516807
Posted Thursday, August 30, 2012 2:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 4, Visits: 99
This is a very simple article which lists the functionality of the output command. However, it would be an excellent article if the following were also included:

a) what happens to the output values given by the output command and where it would be stored
b) limitations
c) performance gain when comparing to triggers

Otherwise a very valuable article, for the beginners to read with.

Thanks
Post #1352056
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse