SQL Stored Procedure Parameters

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

General SQL Server Forums

New to SQL Server Programming

SQL Stored Procedure Parameters

Author

Topic

sirmilt
Starting Member

49 Posts

Posted - 2012-09-22 : 11:59:17

When designing a Stored Procedure, showing parameters when a field can legitimately left blank.

what I have read in tutorials lead me to believe that all fields require an entry. For example:
@FieldOne varchar(25) = a

If, for example FieldOne can legitimately be blank is this s a better practice?
@FieldOne varchar(25) = NULL

My VB program verifies that no required fields are left blank.

Milt

Milt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 12:39:17

blank is '' and not NULL
NULL represents an unknown value

so in your case I think it should be

@FieldOne varchar(25) = ''

didnt understand what you meant by last sentence though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 12:42:58

quote:
My VB program verifies that no required fields are left blank.

A required parameter is one which does not have a default value. Default value can be null, an empty string, or any other valid value. In the example below, only the first parameter is a required value.

CREATE PROCEDURE dbo.TestSproc
	@field1 VARCHAR(25),
	@field2 VARCHAR(25) = 'abc',
	@field3 VARCHAR(25) = NULL,
	@field4 VARCHAR(25) = ''
AS
SELECT @field1,@field2,@field3
GO

EXEC dbo.TestSproc 'a','b','c','d';
EXEC dbo.TestSproc 'b';
EXEC dbo.TestSproc 'c',NULL;
EXEC dbo.TestSproc 'c',NULL,'';
EXEC dbo.TestSproc 'd',NULL,'e';
EXEC dbo.TestSproc 'd',NULL,'g','h';


DROP PROCEDURE dbo.TestSproc;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources