checking if a stored proecures parameters are null or empty

Question

checking if a stored proecures parameters are null or empty

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

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

Sergiy SSC Guru Points: 110209 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: 1004664 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 !!!**