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
 how to handle null vaues in sp??

Author  Topic 

sweet_777
Starting Member

16 Posts

Posted - 2010-11-08 : 05:04:54
Hi all,

i have two local variables in my stored procedure.i.e 1.@Email 2.@Fax

i have written select statement :
select @a= 'select ' + @Email + ',' + @Fax + ' from tblsites where siteid= ' + CONVERT (varchar (10),@siteid)

if @email,@Fax is null in the database table means:
i am getting error like this:
select , from tblsites where siteid= 6
how to handle null values :

my sp is like this:

alter proc dummy --'DAM','Reconciliation By patient',181
(
@Module varchar(50),
@Function varchar(50),
@siteid int
)
as
begin

DECLARE @Email VARCHAR(1024)

--DECLARE @siteid int
declare @keyword varchar(1024)
declare @keyword1 varchar(1024)
--set @siteid=70
SELECT
@Email = CASE WHEN EmailDestinations is null then '' else COALESCE(@Email + ',', '') + EmailDestinations end
FROM
tblDREmailFaxDestinations
where
Module=@Module and [Function]=@Function and EmailDestinations is not null
--select @Email
DECLARE @Fax VARCHAR(1024)

SELECT
@Fax =CASE WHEN FaxDestinations is null then '' else COALESCE(@Fax + ',', '') + FaxDestinations end
FROM
tblDREmailFaxDestinations
where
Module=@Module and [Function]=@Function and FaxDestinations is not null

--SELECT EmailDestinations = @Email
--SELECT FaxDestinations = @Fax
SET @keyword = right(@Email, Len(@Email)-1)
SET @keyword1 = right(@Fax, Len(@Fax)-1)
--print @keyword

declare @a varchar(1024)
SET CONCAT_NULL_YIELDS_NULL OFF
--select @a =COALESCE(@a + '+', '') + @Email --from tblsites where siteid= ' + CONVERT (varchar (10),@siteid)
select @a= 'select ' + @Email + ',' + @Fax + ' from tblsites where siteid= ' + CONVERT (varchar (10),@siteid)
PRINT(@a)
EXEC(@a)


end

Thanks & Regards
Sweet_77

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-08 : 05:29:49
You can use isnull function.

Something like:

select @a= 'select ' + isnull(@Email + ',','') + isnull(@Fax,'') + ' from tblsites where siteid= ' + CONVERT (varchar (10),@siteid)

Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 05:40:32
Then how do your below query to be when @Email and @Fax is null.
I mean what should replace "," value in the statement?


select , from tblsites where siteid= 6



PBUH

Go to Top of Page
   

- Advertisement -