Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simplify stored procedure

Status
Not open for further replies.

peac3

Technical User
Jan 17, 2009
226
AU
Hi all,

I have table structure and stored proc which has two queries to return from 2 dataset from the same table:

Code:
TABLE STRUCTURE

parent children column1 column2 column3
1        A       test1    test2   test3
1        B       test1    test2   test3
1        C       test1    test2   test3
2        D       test1    test2   test3
2        E       test1    test2   test3
3        F       test1    test2   test3

Code:
declare @childrenid int,
        @parentid int

if @childrenid is not null
begin
    select 
    column_1,
    column_2,
    column_3
    where table.children = @childrenid
end


else
begin
    select 
    column_1,
    column_2,
    column_3
    where table.parent = @parentid
end

I was looking to simplify these two queries into one for better standard practice.
Please help...

Any input would be much appreciated.
Thanks,

 
clarification for the stored proc that actually assign null as per default.

Code:
declare @childrenid int = null,
        @parentid int = null

if @childrenid is not null
begin
    select 
    column_1,
    column_2,
    column_3
    where table.children = @childrenid
end


else
begin
    select 
    column_1,
    column_2,
    column_3
    where table.parent = @parentid
end
 
One possibility...

Code:
IF (@childrenid IS NOT NULL) SET @parentid = NULL

SELECT column_1, column_2, column_3
  FROM table
 WHERE children = @childrenid OR parent = @parentid
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top