I have a personal dislike of numerous command and parameters objects in

my ASP pages. To minimize their use and still be able to use stored

procedures to return values to my ASP page, I implement returned values

as "ALIASED" columns in my sprocs.



For example, I want to UPDATE a table in a database and have the sproc

return a value which denotes success or failure of the UPDATE. Here's

how I accomplish this without using the command object and parameter

object.



First, my sproc looks like this:



CREATE PROCEDURE sp_updatetable

(@client_id INT,

@salary MONEY)

AS

DECLARE @success INT

BEGIN TRAN

BEGIN

  UPDATE table1 SET salary=@salary WHERE client_id=@client_id

  IF @@ERROR <> 0 

  BEGIN

     SELECT @success = @@ERROR

     ROLLBACK TRAN

     /* here is where I alias my "return" value */ 

     SELECT @success AS "SUCCESS"

     RETURN

  END

COMMIT TRAN

SELECT @success = 0

/* here is where I alias my "return" value */ 

SELECT @success AS "SUCCESS"

END

RETURN



In my ASP code, I would call the sproc like this:



'...create connection

'...open connection

strSQL="EXEC sp_updatetable @client_id=" & client_id & ", @salary=" &

salary

set rs = Conn.Execute(strSQL)

success = rs("success")

If success <> 0 then

   '... whatever

end if

rs.close

set rs = nothing

conn.close

set conn = nothing



Please notice that the store procedure, sp_updatetable, does not contain

an OUT parameter. You can't get a returned parameter value from a SQL70

sproc without using the command and parameter objects, but you can return

aliased columns using just a command string. 



The benefits of this method is reduced code bloat, and improved server

performance. This is because there are fewer lines of code for IIS to

parse, and fewer objects to allocate and track in memory. Had I chosen

to use the command object/parameter method, I would have had to create

a command object, assign the proper values to the various properties of

the command object and create at least three parameters objects, two for

the input parameters and one for the output parameter. Also, I would have

rewritten my sproc to expect an OUTPUT parameter. All of which I find

cumbersome and code bloating.



I'd appreciate any comments on this method.



Cheers,

Dallas