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.
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.@Faxi 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= 6how 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) endThanks & RegardsSweet_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) |
|
|
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 |
|
|
|
|
|
|
|