User Tips: Using Return Values from a SQL Server S
作者：袖梨
2022-06-30
When I started developing web pages that 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 started using the command object. I eventually realized how useful return values from stored procedures could be, since I could use them to return a value based on a potential error condition that I check for in the stored procedure.
Recently, I was developing an 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 after 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 that submits to Page2.asp which attempts to insert the user-entered information into the database. If the product already exists, go back to Page1.asp, displaying a message and populating the fields with what the user entered. While this is a possible approach, 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 procedure. (I like pop-ups because by their nature, they draw 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.
Here is an example stored procedure that returns an error result if something goes awry:
Create Procedure [Proc_InsertProduct]
(
@productname varchar(50) = null,
@price money = null
}
AS
if exists(select productname from tblProducts where productname = @productname)
Recently, I was developing an 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 after 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 that submits to Page2.asp which attempts to insert the user-entered information into the database. If the product already exists, go back to Page1.asp, displaying a message and populating the fields with what the user entered. While this is a possible approach, 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 procedure. (I like pop-ups because by their nature, they draw 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.
Here is an example stored procedure that returns an error result if something goes awry:
Create Procedure [Proc_InsertProduct]
(
@productname varchar(50) = null,
@price money = null
}
AS
if exists(select productname from tblProducts where productname = @productname)
