Eugene said:
I have created the following stored procedure whereby it will check
whether the categoryID is valid and if it is then the updation will be
performed, else a -1 will be returned. This procedure will be execute
with asp. Is this considered to be efficient?.
create procedure usp_updateCategories
@CategoryID int
@CategoryDescription varchar(30),
@ParentCategoryID int
AS SET NOCOUNT ON
if exists(SELECT top 1* FROM categories WHERE CategoryID=@CategoryID)
begin
UPDATE categories set
CategoryDescription=@CategoryDescription,ParentCategoryID=@ParentCategor
yID WHERE CategoryID=@CategoryID
Fine up to here ...
Select 0
end
else
Select -1
Although some may argue, I would use a Return parameter for this rather than
returning a bulky resultset. It makes no sense to return a cursor just to
pass a single value back to the client. To get an idea how much extra data
is sent across the wire when you do this, use the recordset's Save method
after you execute this procedure to save the recordset to an xml file. Since
the xml file is simply text, you will be able to open it in Notepad and see
all the extra stuff that had to be passed along with your single integer
value. To me, using a parameter (Return or output) makes much more sense.
When a parameter is used, the only data sent back to the client over the
network is the value ... nothing else. When the ADO engine receives the
value, it does not have to construct a recordset object and marshal the data
into the cursor. All it has to do is set the parameter object's value to the
value of the data that was returned from the database. You can't get more
efficient than that.
My criteria are:
Use resultsets (Select statements) to return multiple records - you need a
cursor in this type of situation, so you really have no alternative but to
use a select statement to return a resultset.
Use Return and Output parameters to return single values where you do not
need the functionality of a cursor. This applies to the above procedure.
Of course, using Return and output parameters (see
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/msg/2c935bd7c531d82b)
makes it a little more difficult to write the vbscript code in asp ... so I
have written a free code generator to make this task a little easier. You
can get it here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear
You will have the source code, so you can customize it if you don't like the
names I used for the variables in te generated code.
Anyways, instead of:
*******************
Select 0
end
else
Select -1
Return
********************
I would do this:
**********************
RETURN 0
end
else
RETURN -1
**********************
In your asp page, use an explicit Command object to execute the procedure so
you can retrieve the result of the Return parameter:
Dim cmd, param, catid, catdesc,parcat, retval
set and validate the catid,... variables, then open your
objConn connection, then:
Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "usp_updateCategories"
set .ActiveConnection=objConn
set param = .createparameter("@RETURN_VALUE", _
adInteger, adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@CategoryID", adInteger, _
adParamInput, 0, catid)
.parameters.append param
set param = .createparameter("@CategoryDescription", _
adVarChar, adParamInput, 30, catdesc)
.parameters.append param
set param = .createparameter("@ParentCategoryID", _
adInteger, adParamInput, 0, parcat)
.parameters.append param
.execute ,,adexecutenorecords
'read the return value here:
retval = .Parameters("@RETURN_VALUE").value
' or, slichtly more efficiently:
' retval=.Parameters(0).value
end with
HTH,
Bob Barrows
