469,265 Members | 1,991 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,265 developers. It's quick & easy.

resources - stored procedure

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,ParentCat egoryID=@ParentCategor
yID WHERE CategoryID=@CategoryID
Select 0
end
else
Select -1

Return
GO
Regards

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #1
5 1533
CJM

"Eugene Anthony" <so***********@yahoo.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...
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,ParentCat egoryID=@ParentCategor
yID WHERE CategoryID=@CategoryID
Select 0
end
else
Select -1

Return
GO


Eugene,

This should really be posted in one of the SQL server groups. [Follow-ups
set to m.p.sqlserver.programming]

There is nothing particularly wrong with your implementation; AFAIK it's not
an absolute howler. You may or may not be able to improve on it, on the
other hand, SQL Server will know what you are trying to achieve and will
optimise the query accordingly when it works out the execution plan. The key
is to test this implementation against and others you can think of in Query
Analyzer - see which costs the most.

Chris
Jul 22 '05 #2
CJM wrote:

This should really be posted in one of the SQL server groups.


I disagree. it's relevant here (asp.general), because the way the procedure
is written effects the way the client (asp) will interact with the database.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #3
Eugene Anthony wrote:
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,ParentCat egoryID=@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/...935bd7c531d82b)
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/ind...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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #4
Is having different ways of executing the stored procedures from asp
considered to be acceptable in the industry or does it all have to be
the same standard.

Regards

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #5
Eugene Anthony wrote:
Is having different ways of executing the stored procedures from asp
considered to be acceptable in the industry or does it all have to be
the same standard.


There's always been different ways, some better than others. I'm not sure I
follow you.

My practice is to use what I consider to be the best way depending on the
specific situation. Others may have a different practice.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rhino | last post: by
8 posts views Thread by Thomasb | last post: by
2 posts views Thread by Dino L. | last post: by
2 posts views Thread by jed | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.