SQLTeam.com | Weblogs | Forums

Sql Query and Stored Procedure At Same Time


#1

I have a form which inserts data in to a sql db with an INSERT INTO. I would like to be able to allow a user to only input 10 records (limiting due to the inputs being 10 free advertisements). I am fairly confident that I have written a sp which will accomplish that, however, I do not know how to do the INSERT and have the sp monitor the inserts at the same time.

As an example, Bing Webmaster allows a user to insert 10 URLs per day for indexing. As each URL is submitted, there is a counter which decreases by 1 each time a URL is submitted, and at 0, no more URLs may be submitted.

Is it possible to run an INSERT and sp at the same time? If so, I would appreciate being pointed toward on-line documentation to accomplish such. Or, any guidance on how to accomplish this would be appreciated.


#2

Will your stored procedure insert just one row at a time? Then it's easy. Build a tracking table with the userid and a date column and a counter. The proc will query this table and only run the insert command if the counter is below the threshold. Then, it will update the counter, adding 1 to it


#3

The sp itself does not insert anything. The input form has an INSERT INTO which does the actual insert into the db which will be one row at a time. I was hoping to have a sp track the number of inserts/inputs per email address and stop/prevent inserts at 10. I think the following sp would track the inserts and not allow more inserts - but I'm not sure, as I don't know how to tie it to the actual db as the insert is taking place:

ALTER PROCEDURE uspFreeUserCount
@EmailAddress nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ListingsPerEmailAddress TINYINT = 2;
IF (SELECT COUNT (*) FROM Property WHERE EmailAddress = @EmailAddress) <@ListingsPerEmailAddress
INSERT INTO Property(EmailAddress) VALUES (@EmailAddress);
END

Your suggestion seems like a much better way to go about this, but I have never worked with a counter before. Could you point me toward documentation on that?

And, I would assume the use of the tracking table would be written into the INSERT INTO of the sql command?


#4

Move the insert to the proc


#5

Tremendous amount of work on the input form with code behind, sp to populate drop downs, etc. I like your tracking table idea much better. I'll do research on how to tie the counter to the insert - if you can point me to documentation on that, that would be appreciated. I really like your tracking table idea.


#6

I like the stored procedure approach and would recommend it. If you have an issue with changing the application to use a sproc, you could put the logic in an insert trigger which would rollback the insert if the limit was exceeded. Just a thought.


#7

Not much work (about 10 lines of code) to create an SP to do the INSERT and call it from the web page instead of running the INSERT directly. Also much better security control


#8

I used the trigger approached and it does just as I hoped. However, the trigger throws and exception which of course is really ugly to the user. I have searched and searched and cannot find how to display the fact that the limit has been exceeded (the exception statement) in a friendly appearance. Is it possible to not show the exception but instead show a more friendly looking "you used to many" message?


#9

You can use TRY/CATCH to catch the error and throw a message back to the application. But the application must catch that exception coming back and display it to the user.


#10

You should be able to Catch the error at the application level and post a pleasant message to the user. You'll need to do this regardless of the approach taken (Trigger vs Sproc vs etc.) This may add to the strength of the sproc approach in that an error isn't thrown but the sproc would return a Go/NoGo status which would be handled at the application level.


#11

I have searched and tried and cannot get the Try/Catch to work with the trigger. Any suggestions would be appreciated.

ALTER trigger [dbo].[tri_EmailAddress] on [dbo].[Property]
for insert as
if exists (select * from [Property] od
join (select distinct EmailAddress, Free from inserted) i
on i.EmailAddress = od.EmailAddress
group by od.EmailAddress having count (*) >2)
begin
raiserror ('Cannot have more than ten free listings.', 16, 1)
rollback tran
return
end


#12

Just catch the RAISERROR message in the application and display it to the user.