null parameter

[nat35]

Hi there,
how to pass null parameter into a sql2000 Stored Procedure
in procedure i have cond @par is not null do... it does not work but if change to @par <>''it works
my problem is i am passing parameter from dreamweaverMX i need to pass null otherway DMX does not like <>''in stored proc
thank you
nat

 

[meydz]

can you construct your SQL statement.

<code>
create procedure a
@par as numeric = null
as

if @a is not null
...
else
...

</code>

this code structure should work.

meydz

 

[SonOfEmidec1100]

By default passing a NULL to a stored proc should work with your syntax of IF @Par is NOT NULL syntax. Might be worth checking up on ANSI_DEFAULTS, ANSI_NULLS and ANSI_PADDING in BOL

 

[nat35]

I did try @par as numeric = null it did not work for me
but with the second solution where do i have look for those
I ve ticked on ANSI_NULLS_DEFAULTS in properties of db no luck
here is my code
CREATE PROCEDURE hpg_spGetSiteInstr
(
@house_type int ,
@facade int
--@date_from datetime,
--@date_to datetime
)


AS
SET ANSI_NULLS ON

IF @house_type is not null
IF @facade is not null
begin
SELECT instr.issue_no,instr.issued_date,
instr.draw_effect,instr.change_descr,instr.reason,
instr.initiated_person, instr.authorized_person,
vHT.house_type_name,vHF.house_facade_name
FROM tbl_drf_instr instr
inner join vHouseType vHT
on instr.house_type_id = vHT.house_type_id
inner join vHouseFacade vHF
on instr.facade_id = vHF.house_facade_id

WHERE instr.issue_no not like '%d'
and instr.house_type_id = @house_type
and instr.facade_id = @facade
end


ELSE
begin

SELECT instr.issue_no,instr.issued_date,
instr.draw_effect,instr.change_descr,instr.reason,
instr.initiated_person, instr.authorized_person,
vHT.house_type_name,vHF.house_facade_name
FROM tbl_drf_instr instr
inner join vHouseType vHT
on instr.house_type_id = vHT.house_type_id
inner join vHouseFacade vHF
on instr.facade_id = vHF.house_facade_id
WHERE instr.issue_no not like '%d'
and instr.house_type_id = @house_type
end
SET ANSI_NULLS OFF

GO

I have added ANSI_NULLS
when i back to analyzer and type
hpg_spGetSiteInstr '280',''
it Does not work
thank you
nat