return a set, print it to screen, delete records. 1

[RestonDeveloper]

i am creating a winform that reads in an access database and changes the data and puts it into a mediator table, then i call i have 3 sql procedures: sp_create, sp_update, sp_delete

procedures compare mediator and main table, for records in mediator that aren't in main, i update a [actiontaken]='added' and run insert into statement on that group. if records are in both [actiontaken]='updated' and run update main statement. but deleted i just run delete statement nothing in mediator table to flag.

how do i let user know which events were deleted, for simplicity i'll just let user know how many were deleted for now.

thanks any pointers.

 

[AlexCuse]

Its' not entirely clear to me what you're doing here. Are these SQL Server stored procedures we are talking about now? (if so, you really shouldn't use sp_ as your naming convention - It causes SQL Server to first look in MASTER database for the proc, because this is the naming convention for system stored procedures link)

If so, you might try capturing @@ROWCOUNT in a variable somewhere round the delete from your table (preferably after ;-) ), and getting this value passed back to the app for notification.

Here is an example, using an output parameter to pass back the value:

[COLOR=blue]create[/color] [COLOR=blue]proc[/color] restonTest(@deleted [COLOR=blue]int[/color] [COLOR=blue]output[/color]) [COLOR=blue]as[/color]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (n [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] #t
[COLOR=blue]select[/color] 1
union all [COLOR=blue]select[/color] 2
union all [COLOR=blue]select[/color] 3
union all [COLOR=blue]select[/color] 4

[COLOR=green]--run a delete
[/color][COLOR=blue]delete[/color] [COLOR=blue]from[/color] #t [COLOR=blue]where[/color] n > 3

[COLOR=green]--capture rowcount
[/color][COLOR=blue]set[/color] @deleted = @@rowcount

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

And to call it:

[COLOR=green]---declare variable to hold output
[/color][COLOR=blue]declare[/color] @del [COLOR=blue]int[/color]

[COLOR=blue]exec[/color] restonTest @del [COLOR=blue]OUTPUT[/color]

[COLOR=blue]select[/color] @del

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[ca8msm]

Really Terrific answer Alex, well done


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[chrissie1]

I don't get it, why would you want to delete the round table?

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[RestonDeveloper]

Alex,

thanks. I suppose I could modify code above to return a string instead, and pass the data back, to alert user what rows were deleted instead. thanks this was very helpful.

you the man!

 

[AlexCuse]

What kind of notification do you want to sent back? An array of row ID's or something like that?

I thought you only wanted to notify the user of the count, but re-reading your post I see that was just the starting point. If you want to provide additional information, you probably want to return a resultset, not an output parameter. This will need to be done slightly differently, but it is still not hard.

If you show me the delete statement, I'm sure I'll be able to show you how to return the list of events affected. You just need to capture the info you need somehow before the delete.

Glad you got it (partially) working!

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!