User tips:using return Values from a SQL Server S

When I started developing Web pages this interact with a SQL Server database, I probably started like everbody Else:with Inline SQL statements. I then progressed to using the connection object to call stored procedures and eventually using the command object . I eventually realized how useful return values from stored procedures could is, since I could use them to return a value B ased on a potential error condition, I Check for in the stored procedure.
Recently, I was developing a online catalog and had a situation to deal with:
User enters data into a form
Need to validate the entries (easy enough with client-side JavaScript)
Need to insert the "data into a SQL Server database" checking to make sure various conditions don ' t exist. For example, the user could enter a product, but only if the product doesn ' t already exist in the catalog. That's not something that ' s easily accomplished with Client-side validation!
Initially I decided upon a fairly popular route:create a form in page1.asp then submits to page2.asp which attempts to in SERT the user-entered information into the database. If the product already exists, go back to Page1.asp, displaying a and populating the fields with what the user ent Ered. While this is a possible approach, the trust me when I say that it's a pain to code if you have a lot of form fields! Ideally I wanted a pop-up message that I could customize based on the condition found in the stored. (I like pop-ups because by their nature, they draw the more attention than a message displayed on a page.) Also, I wanted the user taken back to page1.asp with all of his/her entries already filled in.
This is a example stored procedure that returns a error result if something goes:
Create Procedure [Proc_insertproduct]
(
@productname varchar = NULL,
@price money = null
}
As
If exists (select ProductName from tblproducts where ProductName = @productname)