Advanced: sp with alternate parameter data types?

Advanced: sp with alternate parameter data types?

Post by Dean Slinde » Thu, 04 Apr 2002 08:23:29



Is it possible to code a stored procedure with totally different input data
types and use the
one that is not null?  Here is the general idea, but I need help in getting
*real* syntax.  I want
to get a recordset EITHER by Customer OR by a Begin/End date range.  Use
Customer
value if it is not null, otherwise use the Begin/End date range.  Feel free
to totally rewrite.
Thanks, Dean

Alter Procedure dbo.stpGetQA4031CPA_pCoalesce



As


    When ISNULL


    else


    end

return

 
 
 

Advanced: sp with alternate parameter data types?

Post by oj » Thu, 04 Apr 2002 08:29:02


before joe c jumps on you regarding case/when...


As
set nocount on


problem

    else

return

--
-oj
Feeling Lucky Today?! Download a copy of RAC v2.0
and register for our weekly sweepstake.
http://www.rac4sql.com


> Is it possible to code a stored procedure with totally different input
data
> types and use the
> one that is not null?  Here is the general idea, but I need help in
getting
> *real* syntax.  I want
> to get a recordset EITHER by Customer OR by a Begin/End date range.  Use
> Customer
> value if it is not null, otherwise use the Begin/End date range.  Feel
free
> to totally rewrite.
> Thanks, Dean

> Alter Procedure dbo.stpGetQA4031CPA_pCoalesce




> As


>     When ISNULL

>         SELECT * FROM dbo.tblQA4031CPA WHERE followupDate Between

>     else


>     end

> return


 
 
 

Advanced: sp with alternate parameter data types?

Post by Harlan Messinge » Thu, 04 Apr 2002 08:43:50



Quote:> before joe c jumps on you regarding case/when...

What's his beef with it?
 
 
 

Advanced: sp with alternate parameter data types?

Post by Dean Slinde » Thu, 04 Apr 2002 12:35:15


Thanks, that works for me!

> before joe c jumps on you regarding case/when...

> Alter Procedure dbo.stpGetQA4031CPA_pCoalesce



> As
> set nocount on


you
> problem
>         SELECT * FROM dbo.tblQA4031CPA WHERE followupDate Between

>     else

> return

> --
> -oj
> Feeling Lucky Today?! Download a copy of RAC v2.0
> and register for our weekly sweepstake.
> http://www.rac4sql.com



> > Is it possible to code a stored procedure with totally different input
> data
> > types and use the
> > one that is not null?  Here is the general idea, but I need help in
> getting
> > *real* syntax.  I want
> > to get a recordset EITHER by Customer OR by a Begin/End date range.  Use
> > Customer
> > value if it is not null, otherwise use the Begin/End date range.  Feel
> free
> > to totally rewrite.
> > Thanks, Dean

> > Alter Procedure dbo.stpGetQA4031CPA_pCoalesce




> > As


> >     When ISNULL

> >         SELECT * FROM dbo.tblQA4031CPA WHERE followupDate Between

> >     else


> >     end

> > return

 
 
 

1. SQL Server comparisons between columns and sp parameters with different data types

SQL Programmers:

From what I have observed it appears that if you have a stored
procedure with parameters that are a different datatype than those
columns that are being compared to it can cause a different execution
plan and possibly different join strategy (like loop join instead of a
hash).   I would like to know if there is a support article or
something/someone else that can thoroughly explain this?  Here is
related support article for constants

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q271566

Any thoughts on this are appreciated.

Kirk Cooper

2. Capture all SQL statements and archive to file in real time

3. Oracle SP Parameter Error - Invalid Parameter Type

4. TOP n doesn't work

5. Regarding SP parameter types

6. US-NE-Omaha - Oracle Database Administrator - DBA SQL TANDEM C INFORMIX UNIX TAL

7. host data type in data environment command parameters

8. Loading/unloading multiple tables via HPL...Pls Help

9. Advanced Queuing: ORA-25215 user_data type and queue type do not match

10. Data Transfer from Long data type to SQL Server Text data type column

11. About table data type in SP

12. SP with money data types

13. Restrict Data in an SP with a comma delimited parameter