How do do an update and return a table with result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do do an update and return a table with result

Author

Topic

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2009-06-10 : 09:10:10

Hello all!

I'm trying to do something like this:

1. A user scans N number of barcodes.
2. I need to check if each barcode has already been scanned or not (0-no)
3. If the barcode has not been scanned, then I set its 'IsComplete' property to 1, otherwise it stays the same, but I need to notify the user of this.
4. The results from each scan need to go into a result set, which could then be accessed from code.

I tried to do this in a function, but I got an error saying that I couldn't use an UPDATE statement in a function.

I want to return a table so I can read from that in the code and display the result of each transaction to the user. I think it would be a lot faster than the way I do it now, which is to loop through SQLCommand objects and execute them in my business layer. Over the network, the perform is slow (about 8 seconds for 8 entered operations, even though it was worse before-1 minute!)

How to?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-10 : 09:15:04

You try to show no of updates ??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 10:00:09

UPDATE Table1
SET IsComplete = 1
WHERE IsComplete = 0
AND BarCode = @BarCode

RETURN 1 - @@ROWCOUNT

E 12°55'05.63"
N 56°04'39.26"

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2009-06-10 : 10:22:10

I need my result table to look something like this:

Message                                                                                STATUS
-------------------------------------------------------------------------              --------------
OPERATION 12345 SUCCESSFULLY ENTERED BY EMPLOYEE 'JOE JACKSON' 06/10/2009 01:23:29 PM  SUCCESS
OPERATION 12346 WAS ALREADY ENTERED BY EMPLOYEE 'TROY JONES' 06/10/2009 10:33:09 AM    ALREADY_ENTERED
EMPLOYEE WITH ID '99' DOES NOT EXIST                                                   ERROR_EMPLOYEE_DOES_NOT_EXIST
OPERATION 3777 DOES NOT EXIST                                                          ERROR_OPERATION_DOES_NOT_EXIST

So, each time I check a row, I need to send the results of that check to the results table.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2009-06-10 : 10:29:47

I can accomplish this much easier in my business layer (I'm primarily a C# programmer, and secondarily a SQL coder), but the performance is not acceptable over the network.

The error message said something about using Extended Stored Procedures, but I haven't seen those in action yet.

Can I return a table from a regular stored procedure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources