Stored Procedure With Bit Parameter As Null

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure With Bit Parameter As Null

Author

Topic

sharpcnet
Starting Member

8 Posts

Posted - 2013-12-16 : 01:55:42

Hi.
The sql query is short, but would like to explain the scenario for better understanding. Bear the description plz.
I'm trying to write a stored procedure for a search criteria. A user will have few controls on ASP.Net webpage to enter/select the search criteria, based on which , it returns the data.

A Textbox( to enter Username), A Textbox (to enter Employee name),
A DropDownList (To select the profile. Its Id is sent as parameter) &
A DropDownList (To select status). This is where I am stuck.
The last dropdown has 3 list items
1. --All-- value is null/empty
2. Active value is 0
3. Blocked value is 1
Though the Database table will have only 0's and 1's for the status column, the user should be able to see all records when -all-- is selected.

The foll. query isnt giving any output. Where am I going wrong.
If it helps - I have created a fiddle to check
http://sqlfiddle.com/#!2/0055a1/1

ALTER PROCEDURE GetData
(
@username nvarchar(50),
@EmpName nvarchar(50),
@profileid uniqueidentifier,
@status bit
)
AS
BEGIN

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 u.username is null) and
(e.empname like @EmpName or e.empname is null) and
(u.profileid = @profileid or u.profileid is null) and
(u.status = @status or @status is null)
END
--execute GetData null,null,null,null

I also tried excluding the status parameter and its condition, but no luck either way.[url][/url][url][/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 03:48:14

you'cve not set default values for parameters
Also where clause conditions need to be changed as below


ALTER PROCEDURE GetData
(
@username nvarchar(50) = null,
@EmpName nvarchar(50) = null,
@profileid uniqueidentifier = null,
@status bit = null
)
AS
BEGIN

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sharpcnet
Starting Member

8 Posts

Posted - 2013-12-16 : 08:48:20

Thank You Very Much. It worked..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 10:09:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources