<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Dinesh Asanka
»
The OUPUT Command
26 posts, Page 3 of 3
««
«
1
2
3
The OUPUT Command
Rate Topic
Display Mode
Topic Options
Author
Message
vijay_download
vijay_download
Posted Thursday, November 01, 2007 2:00 AM
Forum 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
Anipaul
Anipaul
Posted Friday, June 06, 2008 4:33 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:01 AM
Points: 4,815,
Visits: 1,343
Nice description....... very useful one:)
Post #512781
Matt Miller (#4)
Matt Miller (#4)
Posted Friday, June 06, 2008 9:49 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002,
Visits: 13,999
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
Tom Garth
Tom Garth
Posted Friday, June 06, 2008 11:32 AM
SSC Eights!
Group: General Forum Members
Last Login: Friday, February 04, 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
FVC
FVC
Posted Friday, June 13, 2008 9:04 AM
SSC 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
harij73
harij73
Posted Thursday, August 30, 2012 2:45 AM
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 9:48 AM
Points: 4,
Visits: 65
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 »
26 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
