Need help designing a Stored Procedure...

Need help designing a Stored Procedure...

Post by oj » Thu, 07 Nov 2002 07:25:40



if the column does not allow null...

    CREATE PROCEDURE dbo.MySP
    (

    )
    AS
        SELECT  dbo.Table1.Field1,
                        dbo.Table1.Field2,
                        dbo.Table1.Field3
        FROM dbo.Table1

--
-oj
http://www.rac4sql.net


> Hi there!

> Let's I have tjhe following stored procedure...

>     CREATE PROCEDURE dbo.MySP
>     (

>     )
>     AS

>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

>     ELSE
>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

> Is there a way to code it without the IF..ELSE statement? My real stored
> procedure (this was a sample for those who disn't notice <g>) has 4
> parameters... It would make 16 possibilities of WHERE statements i'm sure
> there is a way to avoid that...

> TIA

> --
> Best Regards
> Zoury
> _________________________________
> Thanks to post replies to the newsgroup :O)

 
 
 

Need help designing a Stored Procedure...

Post by Ivan Demkovitc » Thu, 07 Nov 2002 07:26:43


Unfortunatelly there is no alternatives.
Only dinamic SQL

Also, '' in value is also value. Think about it...


> Hi there!

> Let's I have tjhe following stored procedure...

>     CREATE PROCEDURE dbo.MySP
>     (

>     )
>     AS

>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

>     ELSE
>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

> Is there a way to code it without the IF..ELSE statement? My real stored
> procedure (this was a sample for those who disn't notice <g>) has 4
> parameters... It would make 16 possibilities of WHERE statements i'm sure
> there is a way to avoid that...

> TIA

> --
> Best Regards
> Zoury
> _________________________________
> Thanks to post replies to the newsgroup :O)


 
 
 

Need help designing a Stored Procedure...

Post by Anith Se » Thu, 07 Nov 2002 07:32:33


 Try:

 SELECT dbo.Table1.Field1,
        dbo.Table1.Field2,
        dbo.Table1.Field3
   FROM dbo.Table1
  WHERE dbo.Table1.Field1 LIKE

--
- Anith

 
 
 

Need help designing a Stored Procedure...

Post by Tore » Thu, 07 Nov 2002 08:17:18


You can build a dynamic SQL statement and then execute it.  Something like:

    CREATE PROCEDURE dbo.MySP
    (




    )
    AS







    End If




    End If




    End If




    End If

    EXEC ('SELECT dbo.Table1.Field1,
                         dbo.Table1.Field2,
                         dbo.Table1.Field3

HTH,
Tore.


> Hi there!

> Let's I have tjhe following stored procedure...

>     CREATE PROCEDURE dbo.MySP
>     (

>     )
>     AS

>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

>     ELSE
>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

> Is there a way to code it without the IF..ELSE statement? My real stored
> procedure (this was a sample for those who disn't notice <g>) has 4
> parameters... It would make 16 possibilities of WHERE statements i'm sure
> there is a way to avoid that...

> TIA

> --
> Best Regards
> Zoury
> _________________________________
> Thanks to post replies to the newsgroup :O)

 
 
 

Need help designing a Stored Procedure...

Post by Zour » Thu, 07 Nov 2002 08:20:32


ahhh! I knew you would come up with something.. I should have wait before
coding all 16 possibilities (with copy/paste it's not that bad though...)
;O)
I'll test it as soon as possible.

I've got another question though... My SP looks like this..

SELECT      T1.Field1,
                    T1.Field2
FROM
(
    SELECT dbo.Table1.Field1 AS Field1,
                   dbo.Table1.Field2 AS Field2
    FROM dbo.Table1
    UNION

    SELECT dbo.Table2.Field1 AS Field1,
                   dbo.Table2.Field2 AS Field2
    FROM dbo.Table2
)T1
WHERE T1.Field1 LIKE

               T1.Field2 LIKE

ORDER BY T1.Field1,
                    T1.Field2

I suspect that I should drop the sub query and do it all in the same query
like this.. I am right?

    SELECT dbo.Table1.Field1 AS Field1,
                   dbo.Table1.Field2 AS Field2
    FROM dbo.Table1
    WHERE dbo.Table1.Field1 LIKE

                   dbo.Table1.Field2 LIKE

    ORDER dbo.Table1.Field1,
                  dbo.Table1.Field2
    UNION

    SELECT dbo.Table2.Field1 AS Field1,
                   dbo.Table2.Field2 AS Field2
    FROM dbo.Table2
    WHERE dbo.Table2.Field1 LIKE

                   dbo.Table2.Field2 LIKE

    ORDER dbo.Table1.Field1,
                  dbo.Table1.Field2

TIA

--
Best Regards
Zoury
_________________________________
Thanks to post replies to the newsgroup :O)

> Try:

>  SELECT dbo.Table1.Field1,
>         dbo.Table1.Field2,
>         dbo.Table1.Field3
>    FROM dbo.Table1
>   WHERE dbo.Table1.Field1 LIKE

> --
> - Anith

 
 
 

Need help designing a Stored Procedure...

Post by Zour » Thu, 07 Nov 2002 08:21:44


Thanks for your help! Unfortunatly, they do allow Null.. Anith solution
should work fine though :O)

--
Best Regards
Zoury
_________________________________
Thanks to post replies to the newsgroup :O)

> if the column does not allow null...

>     CREATE PROCEDURE dbo.MySP
>     (

>     )
>     AS
>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

> --
> -oj
> http://www.rac4sql.net



> > Hi there!

> > Let's I have tjhe following stored procedure...

> >     CREATE PROCEDURE dbo.MySP
> >     (

> >     )
> >     AS

> >         SELECT  dbo.Table1.Field1,
> >                         dbo.Table1.Field2,
> >                         dbo.Table1.Field3
> >         FROM dbo.Table1

> >     ELSE
> >         SELECT  dbo.Table1.Field1,
> >                         dbo.Table1.Field2,
> >                         dbo.Table1.Field3
> >         FROM dbo.Table1

> > Is there a way to code it without the IF..ELSE statement? My real stored
> > procedure (this was a sample for those who disn't notice <g>) has 4
> > parameters... It would make 16 possibilities of WHERE statements i'm
sure
> > there is a way to avoid that...

> > TIA

> > --
> > Best Regards
> > Zoury
> > _________________________________
> > Thanks to post replies to the newsgroup :O)

 
 
 

Need help designing a Stored Procedure...

Post by oj » Thu, 07 Nov 2002 08:31:06


are you sure?

create table #tmp(i int, j varchar(5))
insert #tmp select 1,''
union all select 2,'abc'
union all select 3,null
go

--#1

select *
from #tmp

select *
from #tmp

--#2

select *
from #tmp

select *
from #tmp

--
-oj
http://www.rac4sql.net


Quote:> Thanks for your help! Unfortunatly, they do allow Null.. Anith solution
> should work fine though :O)

> --
> Best Regards
> Zoury

 
 
 

Need help designing a Stored Procedure...

Post by Anith Se » Thu, 07 Nov 2002 08:42:55


Well, if the column allows NULLs, let it scan all the way :-)
Use CASE expressions or try:

--#1

--#2

--
- Anith

 
 
 

Need help designing a Stored Procedure...

Post by Zour » Thu, 07 Nov 2002 07:24:20


Hi there!

Let's I have tjhe following stored procedure...

    CREATE PROCEDURE dbo.MySP
    (

    )
    AS

        SELECT  dbo.Table1.Field1,
                        dbo.Table1.Field2,
                        dbo.Table1.Field3
        FROM dbo.Table1

    ELSE
        SELECT  dbo.Table1.Field1,
                        dbo.Table1.Field2,
                        dbo.Table1.Field3
        FROM dbo.Table1

Is there a way to code it without the IF..ELSE statement? My real stored
procedure (this was a sample for those who disn't notice <g>) has 4
parameters... It would make 16 possibilities of WHERE statements i'm sure
there is a way to avoid that...

TIA

--
Best Regards
Zoury
_________________________________
Thanks to post replies to the newsgroup :O)

 
 
 

Need help designing a Stored Procedure...

Post by oj » Thu, 07 Nov 2002 08:53:55


:~) neither works.



--
-oj


> Well, if the column allows NULLs, let it scan all the way :-)
> Use CASE expressions or try:

> --#1

> --#2

> --
> - Anith

 
 
 

Need help designing a Stored Procedure...

Post by Anith Se » Thu, 07 Nov 2002 08:56:26


Quote:>> it should only return row where i=3. <<

I don't think so. Take a look at the original post. If the
parameter is '' then he wants to return all the rows.

--
- Anith

 
 
 

Need help designing a Stored Procedure...

Post by Zour » Thu, 07 Nov 2002 09:09:45


Hi oj!

#1

value..

#2
    that's what I needed ;O)

--
Best Regards
Zoury
_________________________________
Thanks to post replies to the newsgroup :O)



> --
> -oj



> > Well, if the column allows NULLs, let it scan all the way :-)
> > Use CASE expressions or try:

> > --#1

> > --#2

> > --
> > - Anith

 
 
 

Need help designing a Stored Procedure...

Post by oj » Thu, 07 Nov 2002 09:08:02


don't have access to the original post anymore (and too lazy to google).
this particular design will return *wrong* data in the sense that it's not
returning data that matches the parameter.

'', null, and 'whatever' are 3 different types of data. if anything at all,
i would create a new type that represents ALL.

--
-oj


Quote:> >> it should only return row where i=3. <<

> I don't think so. Take a look at the original post. If the
> parameter is '' then he wants to return all the rows.

> --
> - Anith

 
 
 

Need help designing a Stored Procedure...

Post by Anith Se » Thu, 07 Nov 2002 09:15:41


OK, here is from the original post,


        SELECT  dbo.Table1.Field1,
                        dbo.Table1.Field2,
                        dbo.Table1.Field3
        FROM dbo.Table1

    ELSE
        SELECT  dbo.Table1.Field1,
                        dbo.Table1.Field2,
                        dbo.Table1.Field3
        FROM dbo.Table1
<<

Quote:>>will return *wrong* data in the sense that it's not

returning data that matches the parameter. <<

You are talking something else here. The point is to provide
a solution which can accomodate a dynamic parameter. The logic
was to return all rows when the parameter is '' or a specific
row when there is a specific value. If the columns in the
search predicate are nullable, the query I just posted will
still generate required solutions.

--
- Anith

 
 
 

Need help designing a Stored Procedure...

Post by oj » Thu, 07 Nov 2002 09:34:43


based on this original post, you're correct...

however, if field1 actually stores '' and he only wants to see those...

well, look like he's happy with your last soln. that's what matters.

--
-oj


> OK, here is from the original post,


>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1

>     ELSE
>         SELECT  dbo.Table1.Field1,
>                         dbo.Table1.Field2,
>                         dbo.Table1.Field3
>         FROM dbo.Table1
> <<

> >>will return *wrong* data in the sense that it's not
> returning data that matches the parameter. <<

> You are talking something else here. The point is to provide
> a solution which can accomodate a dynamic parameter. The logic
> was to return all rows when the parameter is '' or a specific
> row when there is a specific value. If the columns in the
> search predicate are nullable, the query I just posted will
> still generate required solutions.

> --
> - Anith

 
 
 

1. Help Store Procedure design

Hi All,

Using SQL 2000

I have a table, TABLE A which contains batch numbers.

I would like to create a store procedure which

1. Starts by going to the TABLE A getting a batch number (all the batch
number will be retrieve 1 by 1)
2. The returned batch number is then used in a SELECT WHERE query
3. After the query is processed, we go to TABLE A and get the next batch
number and process the SELECT WHERE query again.
4. The store procedure is completed when all the batch numbers in TABLE A is
processed wtih the SELECT WHERE query.

Can anyone provide with an outline approach.

Thanks in advance

Clive

2. Reducing Storage Space

3. Help: Need help creating a GetNextKey Stored Procedure

4. ODBC ? DAO ? OleDB ? OCI ? ADO ?

5. HELP: Stored procedure calling stored procedure

6. Installing PostgreSQL 7.1.2 on SCO Open Server 5.0.5

7. Help with stored procedure calling another stored procedure.

8. help - can i disable named pipes on sql2k\w2k clustering setup?

9. Need help with a stored procedure

10. Need Help w/error: Stored Procedure for Security

11. Newbie Q: Need help creating a stored procedure

12. Need help with Stored Procedures and VB

13. need help on stored procedure which can return a text colume