Stored Procedure With Bit parameter [Resolved]

Try this ?

where u.deleted=0 and 
 
  (u.username = @username or @username is null) and 
 
  (u.profileid = @profileid or @profileid is null) and 
 
  (u.status = @status or @status is null) 
 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Here you are not checking for NULL value in the variable value

if @username = ''
set @username = null
else
set @username = '%' + @username + '%'

if @EmpName = ''
set @EmpName = null
else
set @EmpName = '%' + @EmpName + '%'

if @profileid = '00000000-0000-0000-0000-000000000000'
set @profileid = null

if @status= ''
set @status= null

select u.username , e.empname, p.profile, u.status
from tbluser u
left join tblemp e on u.empid = e.empid
left join tblprofile p on u.profileid = p.profileid
where u.deleted=0 and
(u.username like @username or @username  is null) and
(e.empname like @EmpName or @EmpName  is null) and
(u.profileid = @profileid or @profileid  is null) and
(u.status = @status or @status is null)
END
--execute GetData null,null,null,null

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Refer this http://sqlfiddle.com/#!3/1583c/10

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

do not write

u.status = @status

and @status is null in where clause
As you are passing @status to Null

That's why you are not getting any rows.

Because Null means no data or absence of data.
Eqal(=) sign is only used for comparing string,int means other than NULL values.

So,if you write u.status = @status means u.status = Null,the condition will be false,so you will not get any row.

Just write:-

select u.* from tbluser u

where u.deleted=0 and

(u.username = @username or u.username is null) and

(u.profileid = @profileid or u.profileid is null) and

(u.status is null or u.status Is @status)

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

I got your problem.

See the below new one:-

If @status is null then dot pass this form UI
and if @status is not null then pass to this sp form UI.

Code:-

ALTER PROCEDURE GetData
(
	@username varchar(50),
	@profileid uniqueidentifier,
	@status bit = NULL
)
AS
BEGIN
	IF @username = ''
	BEGIN
		SET @username = NULL
	END
	ELSE
	BEGIN
		SET @username = '%' + @username + '%'
	END

	IF @profileid = '00000000-0000-0000-0000-000000000000'
	BEGIN
		SET @profileid = NULL
		SELECT u.* 
		FROM tbluser u
		WHERE u.deleted = 0 AND
			 (u.username = @username OR u.username IS NULL) AND
			 (u.profileid = @profileid OR u.profileid IS NULL) AND
			 (u.[status] = @status OR @status IS NULL)
	END
END

--execute GetData null,null,null

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Bandi - Tried your suggestion. It still doesnt give any output.

@Vishal - u.status is @status, doesnt compile in the first place.

@Allemahesh - it did not work. But could you please tell me how is this different from @bandi's..
You have set begin and end for each variable. But that doesnt make any difference...am I right? Also, [status] - what is the need for the bracket.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Good, I will give you all the answer for your questions.
Can you post your parameter passing c# code?

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Ah...your question got me....!
There's a problem here too. How do I send a null bool parameter in the first place :)
But its part of the task :(
The controls which send parameters are - textbox(for username), dropdownlist(for profileid) and dropdownlist(for status). This last dropdown will have 3 list items:

1. --All-- value is null
2. Active value is 0
3. Blocked value is 1.

So if user selects 'All', he should be able to see records of status 0 as well as 1.
Hope this makes sense.

If I write a code, it would be like:

public DataTable GetData(string sUsername, guid gid, bool bstatus)

{

  //call the stored procedure (say SP_DATA) here

  var qry = from p in MYDB.SP_DATA(sUsername, gid, bstatus)

            select new

             {

                 //reqd. col. names

             };

  datatable dt = qry.ToDataTable();

  return dt;

}

Any suggestions...

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Try the below one:-

public void GetData()
    {
        string statu = string.Empty;

        if (DropDownList1.SelectedValue == "0")
            statu = "0";
        else if (DropDownList1.SelectedValue == "1")
            statu = "1";
        DataTable dt = GetData("User Name", 10, statu);
    }

    public DataTable GetData(string sUsername, int gid, string bstatus)
    {
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand("SP_DATA");
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UnitId", sUsername);
        cmd.Parameters.AddWithValue("@SecurityId", gid);
        if (bstatus != string.Empty)
            cmd.Parameters.AddWithValue("@CreatedBy", Convert.ToBoolean(bstatus));
    }

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Try with this code-

If(@status is null) Then
set @status = 0;
else
set @status = 1;

pass this status value in query

select u.* from tbluser u

where u.deleted=0 and

(u.username = @username or u.username is null) and

(u.profileid = @profileid or u.profileid is null) and

(u.status is null or u.status = @status)

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Allemahesh - the SP has 3 parameters declared already. Based on your query, if string is empty, would it not send only 2 parameters?

@Vishal - What if the status sent is 0. Whatever the parameter is, the status will always become 1. correct me if I am wrong.

Also, please note that the column values in table for status are always 0 and 1 only. No null values.

I am thinking of something like, send status as 0, 1 and 2. Then, If status is 2
then the condition will be (status=0 or status=1)...

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Bandi. I have just emulated your example.
When I execute Getdata null, null, null, null, I get 0 rows.
I excluded the whole 'Status' part from query, even then its the same. Maybe, there is something else wrong in the query too.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Check the following query for results.. later add one by one conditions to the query.. then you will get to know what is the problem

select u.username , e.empname, p.profile, u.status
from tbluser u
left join tblemp e on u.empid = e.empid
left join tblprofile p on u.profileid = p.profileid
where u.deleted=0

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Bandi...
That was the first thing I tried :), and it shows all records.
when I add the first condition for username and check --execute getdata null, --- no records.


Thank you everyone for the support till now. The problem remains unfixed. If it helps, I have created a fiddle to check

http://sqlfiddle.com/#!2/0055a1/1

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Are you using MySQL ?

In the sqlfiddle there is no column called deleted in tblUser? Why?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Bandi...Thank you so very much. Its resolved now.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

You are welcome

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator