October 19, 2016 at 5:51 pm
i have a store proc like this
create procedure p_name(@id int, @name varchar(50)
as
select * from tableA where ID = @id and name = @name
go
now name can be null in table, so when i am executing like this
execute p_name 1,null
its giving me nothing .it should return some rows.
what i am doing wrong
October 19, 2016 at 7:05 pm
Something like this maybe?
DECLARE @NameTest varchar(20)
DECLARE @id INT = 1
SELECT *
FROM NameList WHERE FirstName = COALESCE(@NameTest,FirstName)
AND ID = @id;
October 20, 2016 at 12:13 am
where ID = @id
and (name = @name or (name is null and @name is null))
_____________
Code for TallyGenerator
October 20, 2016 at 2:12 am
create procedure p_name(@id int, @name varchar(50)
as
if @name is not null
select * from tableA where ID = @id and name = @name
else
select * from tableA where ID = @id
go
Igor Micev,My blog: www.igormicev.com
October 20, 2016 at 4:12 am
Igor Micev (10/20/2016)
create procedure p_name(@id int, @name varchar(50)as
if @name is not null
select * from tableA where ID = @id and name = @name
else
select * from tableA where ID = @id
go
That's another method, may be even preferable in some situations.
Bu to be correct it must have a little addition:
else
select * from tableA where ID = @id and name is null
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply