asked on

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.

krunal_shah

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?

Pryrates

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

ASKER CERTIFIED SOLUTION

vora_bhaumik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rushShah

try using ISNULL instead of coalesce...

cadsjo

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

Johny Bravo

ASKER

It works fine.
Thanks to all experts who invest their time in this issue.

Johny Bravo

ASKER

//SiteId_ = DBNull.Value;
As SiteId_ is of type int,it is throwing error.