Reporting Stored Procedure on SQL Server 2000

I want to adapt a Stored Procedure like the following for reporting purposes.
select
frmInstId,
frmName,
frmDueDate,
country,
description,
frmLastStatus
flgStat1,
flgStat2,
flgStat3,
flgStat4,
flgStat5
FROM
tblFrmDef,
tblFrmInst,
tblFrmHist,
tblObjects
WHERE
tblFrmDef.frmDefId = tblFrmInst.frmDefId and
tblFrmInst.objId = tblObjects.objId and
tblFrmInst.frmInstId = tblFrmHist.frmInstId and
tblFrmInst.flgActive=1
The problem is that the report can potentially take numerous permutations of
numerous parameters, including:
-tblFrmInst.frmDueDate (range possible)
-tblObjects.user
-tblFrmInst.flgActive (1 or 0)
-tblDef.frmName,
-tblFrmHist.frmLastStatus,
-flgStat1,
-flgStat2,
-flgStat3,
-flgStat4
-flgStat5
Short of building a heavily nested if...then...else statement to build the
correct select statement, are there any better ideas?Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Patrick" wrote:
> I want to adapt a Stored Procedure like the following for reporting purposes.
> select
> frmInstId,
> frmName,
> frmDueDate,
> country,
> description,
> frmLastStatus
> flgStat1,
> flgStat2,
> flgStat3,
> flgStat4,
> flgStat5
> FROM
> tblFrmDef,
> tblFrmInst,
> tblFrmHist,
> tblObjects
> WHERE
> tblFrmDef.frmDefId = tblFrmInst.frmDefId and
> tblFrmInst.objId = tblObjects.objId and
> tblFrmInst.frmInstId = tblFrmHist.frmInstId and
> tblFrmInst.flgActive=1
>
> The problem is that the report can potentially take numerous permutations of
> numerous parameters, including:
> -tblFrmInst.frmDueDate (range possible)
> -tblObjects.user
> -tblFrmInst.flgActive (1 or 0)
> -tblDef.frmName,
> -tblFrmHist.frmLastStatus,
> -flgStat1,
> -flgStat2,
> -flgStat3,
> -flgStat4
> -flgStat5
> Short of building a heavily nested if...then...else statement to build the
> correct select statement, are there any better ideas?|||Also, some of our parameters take in list
and I want to do filter like WHERE tblObjects.user in
('jblogg','helloWorld','smith')
how could I do that? I try to make the parameter a varchar(1000) parameter,
but SQL2000 doesn't seem to treat my comma seperated list as a list, but
rather treating it as a single string/value!?
"Alejandro Mesa" wrote:
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "Patrick" wrote:
> > I want to adapt a Stored Procedure like the following for reporting purposes.
> > select
> > frmInstId,
> > frmName,
> > frmDueDate,
> > country,
> > description,
> > frmLastStatus
> > flgStat1,
> > flgStat2,
> > flgStat3,
> > flgStat4,
> > flgStat5
> > FROM
> > tblFrmDef,
> > tblFrmInst,
> > tblFrmHist,
> > tblObjects
> > WHERE
> > tblFrmDef.frmDefId = tblFrmInst.frmDefId and
> > tblFrmInst.objId = tblObjects.objId and
> > tblFrmInst.frmInstId = tblFrmHist.frmInstId and
> > tblFrmInst.flgActive=1
> >
> >
> > The problem is that the report can potentially take numerous permutations of
> > numerous parameters, including:
> > -tblFrmInst.frmDueDate (range possible)
> > -tblObjects.user
> > -tblFrmInst.flgActive (1 or 0)
> > -tblDef.frmName,
> > -tblFrmHist.frmLastStatus,
> > -flgStat1,
> > -flgStat2,
> > -flgStat3,
> > -flgStat4
> > -flgStat5
> >
> > Short of building a heavily nested if...then...else statement to build the
> > correct select statement, are there any better ideas?|||Hello Alejandro,
You shall use two single quotation for a string in dynamical query. For
example, you could run this in QA to test
exec ('select * from employees where fname=''test''')
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Reporting Stored Procedure on SQL Server 2000
| thread-index: AcWcy8MVucGjmJIKT72QFaaxe01p2g==| X-WBNR-Posting-Host: 198.240.128.75
| From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@.newsgroup.nospam>
| References: <5A2BD817-28A5-433D-9556-3E82983B39DD@.microsoft.com>
<915A2183-FE52-4005-A8A9-4CBF10BC8586@.microsoft.com>
| Subject: RE: Reporting Stored Procedure on SQL Server 2000
| Date: Tue, 9 Aug 2005 03:19:01 -0700
| Lines: 58
| Message-ID: <0C9CE207-7DD9-4EB3-9B8C-0091DFE447CF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:66307
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Also, some of our parameters take in list
| and I want to do filter like WHERE tblObjects.user in
| ('jblogg','helloWorld','smith')
|
| how could I do that? I try to make the parameter a varchar(1000)
parameter,
| but SQL2000 doesn't seem to treat my comma seperated list as a list, but
| rather treating it as a single string/value!?
|
| "Alejandro Mesa" wrote:
|
| > Dynamic Search Conditions in T-SQL
| > http://www.sommarskog.se/dyn-search.html
| >
| >
| > AMB
| >
| > "Patrick" wrote:
| >
| > > I want to adapt a Stored Procedure like the following for reporting
purposes.
| > > select
| > > frmInstId,
| > > frmName,
| > > frmDueDate,
| > > country,
| > > description,
| > > frmLastStatus
| > > flgStat1,
| > > flgStat2,
| > > flgStat3,
| > > flgStat4,
| > > flgStat5
| > > FROM
| > > tblFrmDef,
| > > tblFrmInst,
| > > tblFrmHist,
| > > tblObjects
| > > WHERE
| > > tblFrmDef.frmDefId = tblFrmInst.frmDefId and
| > > tblFrmInst.objId = tblObjects.objId and
| > > tblFrmInst.frmInstId = tblFrmHist.frmInstId and
| > > tblFrmInst.flgActive=1
| > >
| > >
| > > The problem is that the report can potentially take numerous
permutations of
| > > numerous parameters, including:
| > > -tblFrmInst.frmDueDate (range possible)
| > > -tblObjects.user
| > > -tblFrmInst.flgActive (1 or 0)
| > > -tblDef.frmName,
| > > -tblFrmHist.frmLastStatus,
| > > -flgStat1,
| > > -flgStat2,
| > > -flgStat3,
| > > -flgStat4
| > > -flgStat5
| > >
| > > Short of building a heavily nested if...then...else statement to
build the
| > > correct select statement, are there any better ideas?
|