Handling validation of parameters of SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Handling validation of parameters of SP

Author

Topic

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-04-21 : 09:52:09

What is the correct way to handle validation of parameters ?
Let's say I have 3 parameters that are list
CREATE PROCEDURE SP
(
@Ids varchar(512) -- '1,2'
@startDates varchar(512) -- '06-06-2009, 06-06-2009'
@endDates varchar(512) -- '10-06-2009, 15-06-2009'
)

I have to validate that each param has the same number of values.
validation errors, you use RAISEERROR???

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-21 : 12:44:50

Yeah use RAISERROR. Depending on your goal, you can use the TRY-CATCH block and do something in the CATCH(like log the error or even re-throw the error). Or you can go without a TRY-CATCH block and just let the error bubble up. Here is a sample if it helps:


DECLARE @ProcName			VARCHAR(1000)
DECLARE @Msg				VARCHAR(8000)

SET @ProcName = 'MyStoredProc'

BEGIN TRY
	IF (@StartDates IS NULL)
	BEGIN
		SET @Msg = 'Parameter "@StartDates" cannot be NULL.'
		RAISERROR(@Msg, 16, 1, 'SprocName')
	END

	-- Other stuff
END TRY
BEGIN CATCH
	-- Handle error
END CATCH

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-21 : 13:42:19

Or, perhaps, have your front end validate any parameters before they are sent to the DB.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-04-21 : 14:08:57

Cool.

But even if the front end do some validation, the SP should also do the validation no ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources