checking if a stored proecures parameters are null or empty

Question

checking if a stored proecures parameters are null or empty

ben potts

SSC-Addicted

Points: 414
More actions
April 15, 2006 at 10:56 pm

#113681

have a have a simple stored procedure i have made but i want it to be able to check if the input parameters are blank, E.g ''
and if they are to change the value to null before they are inserted into the database.
i think this is a simple if statement, but all the ways that i tried i could not get the stored procedure to compile.
any help would be appreciated,
Ben
create proc ben_sp_state1
@clientid numeric(9),
@ConsultantID numeric(9),
@ConsultancyTypeIDnumeric(9),
@ConsultancyLevel numeric(9),
@ConsultancyDatenumeric(9)
as
declare @ConsultancyBookingNumber as numeric(9)
set @ConsultancyBookingNumber =
(
select (max(ConsultancyBookingNumber)+1)
from consultancy
)
insert into consultancy
(
ConsultancyBookingNumber,
ClientID,
ConsultantID,
ConsultancyTypeID,
ConsultancyLevel,
ConsultancyDate,
ConsultancyStateID
)
values
(
@ConsultancyBookingNumber,
@clientid,
@ConsultantID,
@ConsultancyTypeID,
@ConsultancyLevel,
@ConsultancyDate,
'1'
)
go

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

I better not comment the code. Just for a purpose not to appear rude.

But you cannot supply empty string as a parameter to your SP because all of your parameters are integer. You MUST supply a number, otherwise your call for SP will fail.

You must do this check and replacement in your application code.

_____________
Code for TallyGenerator

ben potts SSC-Addicted Points: 414 More actions · Answer 2

thats for the help.

yeh im quite new to sql, and self taught at that. in what way should i alter the code to make it "nicer"? just for reference.

its a uni project so as long as it works they arent that bothered.

thanks,

ben

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Ben,

Why not make the ConsultancyBookingNumber column in the Consultancy table and IDENTITY column instead of using MAX+1? Then, you wouldn't even need this procedure as the column would be auto-numbering and auto-incrementing...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Some more suggestions:

In addition to making the ConsultancyBookingNumber an ID column, you could also specify default values (of NULL) for the various parameters so you don't have to do any checking..

eg:

create proc ben_sp_state1
@clientid numeric(9) = NULL,
@ConsultantID numeric(9)= NULL, etc...

This way if the values are not supplied, null is inserted - else the values!

You could make the default on your ConsultancyStateID colum '1' (this is a char/varchar column ?!?!?!) so you don't have to include it in your insert statement...

You have ConsultancyDate as a numeric datatype - that should be changed to datetime/smalldatetime so you can perform date calcs when/if needed!

**ASCII stupid question, get a stupid ANSI !!!**