Stored Procedure With Bit parameter [Resolved]

Posted by Sharpcnet under Sql Server on 12/15/2013 | Points: 10 | Views : 1453 | Status : [Member] | Replies : 17
Fiddle - http://sqlfiddle.com/#!2/0055a1/1

One of the parameters is of Bit datatype. Though the values in DB table are only 0's and 1's, when sending the parameter from interface, it can also be a null. The foll. query gives 0 rows. I suspect the problem could be with this bit parameter.
ALTER PROCEDURE GetData
(
@username varchar(50),
@profileid uniqueidentifier,
@status bit
)
AS
BEGIN
if @username = ''
set @username = null
else
set @username = '%' + @username + '%'

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

select u.* from tbluser u
where u.deleted=0 and
(u.username like @username or u.username is null) and
(u.profileid = @profileid or u.profileid is null) and
(u.status = @status or (@status is null and u.status is null ))
END

--execute GetData null,null,null





Responses

Posted by: Bandi on: 12/15/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Bandi on: 12/16/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: Bandi on: 12/16/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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

Posted by: vishalneeraj-24503 on: 12/15/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down

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

Posted by: Allemahesh on: 12/16/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Sharpcnet on: 12/16/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Allemahesh on: 12/16/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Sharpcnet on: 12/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Allemahesh on: 12/16/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: vishalneeraj-24503 on: 12/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sharpcnet on: 12/16/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Sharpcnet on: 12/16/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Bandi on: 12/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sharpcnet on: 12/16/2013 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Bandi on: 12/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sharpcnet on: 12/16/2013 [Member] Starter | Points: 25

Up
0
Down
@Bandi...Thank you so very much. Its resolved now.

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

Posted by: Bandi on: 12/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response