command parameters with null value 1

[flopo]

Hi guys,

I am trying to pass a null to a sproc (in CR XI and CR 2008) and i cannot figure it out;

here is relevant code

--sp
ALTER PROCEDURE [main].[test_null]
@s varchar(max) = null,
@z varchar(max)
AS

SET NOCOUNT ON
set transaction isolation level read uncommitted

BEGIN
if @State is null
begin
select 'null' x;
end;
else
begin
select 'not null' x;
end;
END

--cr call
[main].[test_null]
@s = {?s},@z ={?z}

Under CR XI i have no ideea, under CR 2008 my strategy was to make the parameters optional (it cannot be done for db bound params) and then into Record Selection to make it return true

Another strategy is to fill the the parameter with "null" and say if ({?s}='null') then
//set it to null, I don't know how to do this
{?s} = ""

this must be done before the actual call goes to the database... no clue where.

I don't know if this can be done, and how... but it seams to me kinda lame not to be able to do that...

BR, Florin POP

 

[IanWaterman]

In the edit command dialog box you can create parameters and set the default there to "Null"

Ian

 

[flopo]

10x Ian ... this was so simple

 

[flopo]

Any ideea for params that are int?
Null works for Strings but not for int... it converts Null to 0 which is not null (not empty...)
Thanks
Florin POP