ASP - Best practice - handling null in sp

Author

Best practice - handling null in sp

2004-09-23, 3:55 pm

Sql server 7

There will be times when the user will not be required to supply a value for
the calling asp script that pass values to an sql server Sp.

How can I handle these null or empty values so I do not generate an error
for the code below....

1. Should I rewrite the sql server SP to handle the null values...

2. or should I replace the null and empty values with a dummy value.

----------------------------------------
Here is an example of my calling code:
---------------------------------------

set oConn = GetConnection()
Set oCmd = GetStoredProcedure(oConn,"sp_adduser1")
oCmd.Parameters.append oCmd.CreateParameter("u_id", adVarChar,
adParamInput,50,sID)
oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar,
adParamInput,50,sEmailName)
oCmd.Parameters.append oCmd.CreateParameter("u_NationID", adVarChar,
adParamInput,12,sNationID)

set oReturn = oCmd.CreateParameter("u_id", adInteger, adParamOutput)
oCmd.Parameters.append oReturn

oCmd.execute()

if oReturn.value=-1 then
'//Code
Else
'//Code
end if

------------------
STORED PROC
------------------

CREATE Procedure sp_adduser1
@u_name varchar(50),
@u_NationID int,
@u_id int output
As
set nocount on
if not exists(select u_id from t_user where u_name=@u_name)
begin
INSERT INTO t_user(u_name, u_NationID)
VALUES (@u_name@u_NationID)
select @u_id=@@identity
end
else
select @u_id=-1
return
GO

Scott McNair

2004-09-23, 3:55 pm

<jason@catamaranco.com> wrote in news:OxRihcYoEHA.2900
@TK2MSFTNGP12.phx.gbl:

> How can I handle these null or empty values so I do not generate an error
> for the code below....

Top of my head, the easiest way would be to use IsNull in the sproc...

INSERT INTO t_user(u_name, u_NationID)
VALUES (IsNull(@u_name,''),IsNull(@u_NationID,'
'))

Sponsored Links