Null Value - SQL Server Forums

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)

Null Value

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-01-22 : 14:36:25

Hello,

I created a SQL 2005 Procedure with the following parameters:

@Id UNIQUEIDENTIFIER,
@Question NVARCHAR(MAX),
@Answer NVARCHAR(MAX),

Id is a GUID.

I am executing the procedure as follows:

EXECUTE dbo.MyProcedure '', 'Question1', 'Answer1',

I am getting an error:
Error converting data type varchar to uniqueidentifier.

My question is:
How can I pass a Null value to the Id and inside my procedure check if that value is Null?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 14:40:46

'' is NOT the same as NULL. You have provided a EMPTY string.

EXECUTE dbo.MyProcedure NULL, 'Question1', 'Answer1'

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-22 : 14:42:28

[code]
create proc dbo.MyProcedure
@Id UNIQUEIDENTIFIER = null,
@Question NVARCHAR(MAX),
@Answer NVARCHAR(MAX)
as
if @Id is null ...
...
go
EXECUTE dbo.MyProcedure null, 'Question1', 'Answer1',
[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 14:45:33

If you set NULL as default value for the parameter as MVJ describe above, you can even call the sp with

EXECUTE dbo.MyProcedure default, 'Question1', 'Answer1'

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-22 : 14:55:14

You can also do this if you supply the parameters by name:

EXECUTE dbo.MyProcedure @Question = 'Question1', @Answer = 'Answer1'

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources