Null value to a Stored procedure



Hi Experts,
I have written  Stored Proc where I get the Site Id and display the records accordingly.
Now I want if 'All' is selected then all the records should be displayed.

Create PROCEDURE pro_SiteWiseResourceSkillFilter                
@domainId varchar(200),                        
@domainTechId varchar(200),                
@skillId varchar(200),  
@siteId int                        
as                                    
BEGIN                                    
                           
SET NOCOUNT ON      
 select distinct Us.WduserId,EmpName as ResourceName                
  from UserSkills Us inner join vw_getusers on Us.WduserId = vw_getUsers.WduserId                            
  inner join Domain d on d.DomainId = Us.DomainId and d.IsDeleted =0                          
  inner join Technologies Dt on Dt.TechId =Us.domainTechId and Dt.IsDeleted =0                              
  inner join Skills sk on sk.SkillId =Us.SkillId and sk.IsDeleted =0                          
  where vw_getUsers.DesignationId not in (2,25,28,38) and Us.IsActive =1  and vw_getUsers.SiteId = Coalesce(@siteId,vw_getUsers.SiteId)
  order by EmpName        
           
End            
                           
SET NOCOUNT OFF                                            
                           
END            
   
 
I am using Coalesce for this purpose.
But from my page how should I pass null value to the SP.

   public int SiteId_;

 protected void ddlSites_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlSites.SelectedIndex == 0)
            {
             
               // SiteId_ = DBNull.Value; didn't work
            }

            BindSiteWiseResourceList(string.Empty, string.Empty, string.Empty, SiteId_);
        }

How can I pass null.
LVL 8
Johny BravoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
vora_bhaumikConnect With a Mentor Commented:
Try like this,


 public int? SiteId_ =null;

 protected void ddlSites_SelectedIndexChanged(object  sender, EventArgs e)
        {
            if  (ddlSites.SelectedIndex > 0)
            {
             
              SiteId_ = ddlSites.SelectedValue;
            }

             BindSiteWiseResourceList(string.Empty,  string.Empty, string.Empty, SiteId_);
        }




0
 
krunal_shahCommented:
check this it will help,
http://forums.asp.net/p/1132283/1798468.aspx

i think this should  work,
SiteId_ = DBNull.Value;
is it giving any error?
0
 
PryratesCommented:
you should use DBNull.Value as parameter of you stored proc.
That should do.
I created a simple sample that you might give a try.
Of course you need to replace the placeholder with your correct settings
SqlConnection conn = new SqlConnection(<CONNECTIONSTRING>);
            SqlCommand sqlCom = new SqlCommand("testNull", conn);
            sqlCom.CommandType = System.Data.CommandType.StoredProcedure;
            SqlParameter param = new SqlParameter("siteid", System.Data.SqlDbType.Int);
            param.Value = DBNull.Value;
            sqlCom.Parameters.Add(param);
            conn.Open();

           SqlDataReader rdr = sqlCom.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
           if (rdr.HasRows)
           {
               while (rdr.Read())
               {
                   Console.WriteLine(rdr.GetString(0)); //assumes first element of result is a string
               }
           }
           rdr.Close();
           conn.Close();

           Console.ReadKey();


//Stored proc:

CREATE PROCEDURE testNull
@siteid int
as
BEGIN
SELECT * FROM <TABLE>
END

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rushShahCommented:
try using ISNULL instead of coalesce...
0
 
cadsjoCommented:
You can use the default value for the parameter

Create PROCEDURE pro_SiteWiseResourceSkillFilter                
@domainId varchar(200),                        
@domainTechId varchar(200),                
@skillId varchar(200),  
@siteId int = NULL

Now, just don't provide the siteId and in your sp it will be NULL.

HTH
Cadsjo
0
 
Johny BravoAuthor Commented:
It works fine.
Thanks to all experts who invest their time in this issue.
0
 
Johny BravoAuthor Commented:
//SiteId_ = DBNull.Value;
As SiteId_ is of type int,it is throwing error.
0
All Courses

From novice to tech pro — start learning today.