how to pass NULL as an IN parameter to SQL function

[jivesociety]

I have an SQL Server 2000 function, that has 1 out parameter and 3 in parameters. The 3rd parameter is optional though. How do you use Spring's StoredProcedure object to supply the third parameter to be null?

Any help would be appreciated.

Below is the funtion:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create function fn_Verify_Format (@instrument_id int, @fmt nvarchar(31), @fmt_id int)
returns bit
as
begin

declare @exists bit

if @fmt_id is null
begin
if exists (select 1 from dcds_format where instrument_id = @instrument_id and name = @fmt)
select @exists = 1 else select @exists = 0
end
else
begin
if exists (select 1 from dcds_format where instrument_id = @instrument_id and name = @fmt and format_id <> @fmt_id)
select @exists = 1 else select @exists = 0
end
return (@exists)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[trisberg]

Have you tried just passing in a Java 'null' - it should result in the parameter being set to NULL.

[darrinps]

That works for me:

params.put("name",null);

Have you tried just passing in a Java 'null' - it should result in the parameter being set to NULL.