Can it be done? WinForm consumes report, then prints direct to printer

It seems like there must be a way to do this basic task. A winform app
calls a report, passes the parameters and then outputs that report directly
to the printer without user intervention, or browser sessions.
I really dont want to go back to Crystal to do this.
Thanks, MikeNot easily. To integrate with RS from a winform you have to use either web
services or url integration. You can either handle the printing yourself
(render to PDF, save the file to disk, print the PDF) or use the client side
printing but although the client side printing could be driven by URL
integration (which would require a browser control) it will pop up a
printing dialog box. There is no way I know of to prevent this. My
suggestion is to render to PDF, save to disk etc.
You could try beta 2 of VS 2005 which have a winform and webform control but
that is a beta.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Morisoli" <michael@.removeme.vieon.net> wrote in message
news:OyKPcNVYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> It seems like there must be a way to do this basic task. A winform app
> calls a report, passes the parameters and then outputs that report
> directly
> to the printer without user intervention, or browser sessions.
> I really dont want to go back to Crystal to do this.
> Thanks, Mike
>

can I use the value of field in last record?

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

Yes you can use them in a given scope (table/list):

Fields!a1.Value

Last(Fields!a1.Value, "Dataset1")

Fields!a1.Value(RowNumber("Dataset1")+1)

Shyam

Can I Turn off Auto Execution of Report when all parameters have default values?

My situation....

I deploy a report in Report Server. Ther report has 5-10 parameters. When I design the report, I supply a default value for each parameter, similiar to the "Add a select all option" tutorials.

So I end up with defaults like "Select a Project Manager", "Select a Fiscal Year", etc... I use dynamic queries to read the parameters and insert the appropriate logic into the where string of my main dataset. If the user selects a value from my parameter lists, I append a clause to the SQL string, if they choose the default option, I ignore the parameter, and that effectively returns all of the records (like Select * from ....)

My Issue...

When I provide a default paramater for ALL of the parameters available, the report auto-executes when the user picks it from the folder, and isn;t given the opportunity to refine the default criteria I have provided.

My Question....

Is there any way to turn the autoexecution off?

Thanks for any help...

There is no method to turn off the auto execution of the report if all parameters have default values.

As you have probable already found out, you can populate all but one of the parameters to get the result you desire. But that makes the user have to supply a value of the parameter.

Can I See What A Parameter is Passing to a Sproc?

I've got a report with 4 parameters to a stored procedure. The parameters
all work in the stored procedure when I run it in Query Analyzer, but only
one of them works in the report.
Is there a way to see what's being passed to the sproc? I don't have admin
rights to the DB so I can't use the profiler.
Any troubleshooting ideas for parameters would be welcome.
Thanks,
LizSince you cannot access the database, what you can do is first you can give
litral values for the stored proc in the Data tab and see whether it works
and remove one by one and see whether it works..
I feel the problem is due to some Null values which may not have been
handled in the stored proc.
Amarnath
"Liz" wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
> Any troubleshooting ideas for parameters would be welcome.
> Thanks,
> Liz|||Everything works fine in the Data tab. However, it fails in the Preview
tab. To test the parameters, I tweaked the sproc to only return what it had
been sent in parameters. I created another report to display what it had
sent to the sproc. Everything checks out. The report is sending what it's
supposed to. So now I'm back to the original report.
How can I troubleshoot the Preview? I suspect there's something in the
stored procedure that it doesn't like but that would be hard to narrow down.
The other thing might be that I orignally created this sproc and report with
one parameter - the one that works. I then added the other three - they
don't work. Do you have add all the parameters at the outset? Can you not
add parameters later?
What kind of null values are you talking about? Null values in the data?
Or null values in the parameters? There are no nulls in the parameters. I
will now go and make sure I'm not sending any nulls back to RS.
Thanks.
"Amarnath" wrote:
> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
> Amarnath
> "Liz" wrote:
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz|||I just went and bulletproofed every field to make sure that a null would not
be returned to RS and that, unfortunately, made no difference.
I'm desperate for any ideas you or anyone might have.
Thanks.
"Amarnath" wrote:
> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
> Amarnath
> "Liz" wrote:
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz|||My apologies if this is a double-post but I just bulletproofed all the fields
to make sure that no nulls are returned to RS but that didn't make any
difference.
So I'm still searching for ideas on this one.
"Amarnath" wrote:
> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
> Amarnath
> "Liz" wrote:
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz|||>>
Do you have add all the parameters at the outset? Can you not
add parameters later?
<<
Yes you can definitely add parameters later. However sometimes there is
something subtly different in the *way* you added the other three that might
be making a difference.
I would look at the code (the XML) instead of trying to handle this in the
Designer. Take a look at all of the parameter definitions and find out what
makes that one different from the others.
There also might be something different about the params in the sproc -- for
example, do they all have default values, or only one/some?
FWIW -- in your other message you say you " just bulletproofed all the
fields to make sure that no nulls are returned to RS" but I don't think
Amarnath was concerned about nulls RETURNED to RS. I think s/he was
concerned about the way the *sproc* might handle a null. I know you said
that there are no nulls in the params you are sending -- just clarifying.
Is there any possibility we have a type or casting issue here?
>L<
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D64A0349-6597-4A1D-9C09-51DE7B9FD0BA@.microsoft.com...
> Everything works fine in the Data tab. However, it fails in the Preview
> tab. To test the parameters, I tweaked the sproc to only return what it
> had
> been sent in parameters. I created another report to display what it had
> sent to the sproc. Everything checks out. The report is sending what
> it's
> supposed to. So now I'm back to the original report.
> How can I troubleshoot the Preview? I suspect there's something in the
> stored procedure that it doesn't like but that would be hard to narrow
> down.
> The other thing might be that I orignally created this sproc and report
> with
> one parameter - the one that works. I then added the other three - they
> don't work. Do you have add all the parameters at the outset? Can you
> not
> add parameters later?
> What kind of null values are you talking about? Null values in the data?
> Or null values in the parameters? There are no nulls in the parameters.
> I
> will now go and make sure I'm not sending any nulls back to RS.
> Thanks.
> "Amarnath" wrote:
>> Since you cannot access the database, what you can do is first you can
>> give
>> litral values for the stored proc in the Data tab and see whether it
>> works
>> and remove one by one and see whether it works..
>> I feel the problem is due to some Null values which may not have been
>> handled in the stored proc.
>> Amarnath
>> "Liz" wrote:
>> > I've got a report with 4 parameters to a stored procedure. The
>> > parameters
>> > all work in the stored procedure when I run it in Query Analyzer, but
>> > only
>> > one of them works in the report.
>> >
>> > Is there a way to see what's being passed to the sproc? I don't have
>> > admin
>> > rights to the DB so I can't use the profiler.
>> >
>> > Any troubleshooting ideas for parameters would be welcome.
>> >
>> > Thanks,
>> > Liz|||Liz wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
> Any troubleshooting ideas for parameters would be welcome.
> Thanks,
> Liz
Aside from the SQL Profiler (as you mentioned), I would suggest
creating a stored procedure that just accepts the 4 parameter values
and inserts them into a basic table with 4 varchar/nvarchar defined
columns. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 24, 11:42 am, Liz <L...@.discussions.microsoft.com> wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
> Any troubleshooting ideas for parameters would be welcome.
> Thanks,
> Liz
Oh, I forgot, you could also create 4 textbox controls in the report
and add an expression similar to this for each one that way it will
display the values in the report itself.
=Parameters!Parameter1Name.Value
Also, if the SP is erroring out, temporarily replace it with a basic
query that definitely works. Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant

Can I Restrict Values in a Parameter Dropdown based on the User?

I've got a puzzler here. I have a report with 4 different dropdown lists for
parameters. One of them is Department Id. For some users, I want them to be
able to only run the report against their own department id but still be able
to select any of the values for the other parameters. Other users will be
able to select any value for department id and the other parameters.
I can figure out how to write the query to populate the Department ID
dropdown but cannot figure how to access the current userid programatically.
Is there a way to access the user ID in order to restrict the values in a
parameter dropdown?
I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
Thanks,
LizThis is really quite easy. There is a global parameter called User!UserID it
returns the domain and user ID of the user running the report. To see the
format of this create a report with a single textbox (no datasets). Set the
text box to an expression and set it to this (case sensitive):
=User!UserID
You might want to strip the domain off.
OK, the second part of this is that you need to realize that query parameter
and report parameter are two different things. You do not have to have a
query parameter mapped to a report parameter. It can be mapped to an
expression instead.
So, let's say you have this query
Select department from mydepartmentlist where userID = @.UserID
RS will create a report parameter for this called UserID. You will not use
it. You will delete it. Instead in the dataset tab click on the ... go to
the parameters tab, click on the dropdown box where the query parameter is
being mapped to report parameter and pick expression instead. Then set it to
the expression above. If you don't want the domain then use something like
this:
=mid(User!UserID, LengthToCutOffTheFront)
Of course for all of this to work you need a table somewhere that maps the
userid to the departments.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> I've got a puzzler here. I have a report with 4 different dropdown lists
> for
> parameters. One of them is Department Id. For some users, I want them to
> be
> able to only run the report against their own department id but still be
> able
> to select any of the values for the other parameters. Other users will be
> able to select any value for department id and the other parameters.
> I can figure out how to write the query to populate the Department ID
> dropdown but cannot figure how to access the current userid
> programatically.
> Is there a way to access the user ID in order to restrict the values in a
> parameter dropdown?
> I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> Thanks,
> Liz
>|||Thanks! That's very helpful and I've already succeeded at stripping off the
domain name.
I know you can pass parameter values through the URL and query string to the
report. If I wanted to pass the department ID from the calling application
to populate the dropdown, is there a way to pass that one parameter value and
let the user select the other three?
Thanks for your help,
Liz
"Bruce L-C [MVP]" wrote:
> This is really quite easy. There is a global parameter called User!UserID it
> returns the domain and user ID of the user running the report. To see the
> format of this create a report with a single textbox (no datasets). Set the
> text box to an expression and set it to this (case sensitive):
> =User!UserID
> You might want to strip the domain off.
> OK, the second part of this is that you need to realize that query parameter
> and report parameter are two different things. You do not have to have a
> query parameter mapped to a report parameter. It can be mapped to an
> expression instead.
> So, let's say you have this query
> Select department from mydepartmentlist where userID = @.UserID
> RS will create a report parameter for this called UserID. You will not use
> it. You will delete it. Instead in the dataset tab click on the ... go to
> the parameters tab, click on the dropdown box where the query parameter is
> being mapped to report parameter and pick expression instead. Then set it to
> the expression above. If you don't want the domain then use something like
> this:
> =mid(User!UserID, LengthToCutOffTheFront)
> Of course for all of this to work you need a table somewhere that maps the
> userid to the departments.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Liz" <Liz@.discussions.microsoft.com> wrote in message
> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> > I've got a puzzler here. I have a report with 4 different dropdown lists
> > for
> > parameters. One of them is Department Id. For some users, I want them to
> > be
> > able to only run the report against their own department id but still be
> > able
> > to select any of the values for the other parameters. Other users will be
> > able to select any value for department id and the other parameters.
> >
> > I can figure out how to write the query to populate the Department ID
> > dropdown but cannot figure how to access the current userid
> > programatically.
> > Is there a way to access the user ID in order to restrict the values in a
> > parameter dropdown?
> >
> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> >
> > Thanks,
> > Liz
> >
> >
>
>|||Sure. You have have a hidden parameter that gets filled in by the calling
application. The user only selects from the list you provide. Look at hidden
parameters in the books online.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D0F372BF-DA85-4BD6-A01C-ED2708F14BAC@.microsoft.com...
> Thanks! That's very helpful and I've already succeeded at stripping off
> the
> domain name.
> I know you can pass parameter values through the URL and query string to
> the
> report. If I wanted to pass the department ID from the calling
> application
> to populate the dropdown, is there a way to pass that one parameter value
> and
> let the user select the other three?
> Thanks for your help,
> Liz
>
> "Bruce L-C [MVP]" wrote:
>> This is really quite easy. There is a global parameter called User!UserID
>> it
>> returns the domain and user ID of the user running the report. To see the
>> format of this create a report with a single textbox (no datasets). Set
>> the
>> text box to an expression and set it to this (case sensitive):
>> =User!UserID
>> You might want to strip the domain off.
>> OK, the second part of this is that you need to realize that query
>> parameter
>> and report parameter are two different things. You do not have to have a
>> query parameter mapped to a report parameter. It can be mapped to an
>> expression instead.
>> So, let's say you have this query
>> Select department from mydepartmentlist where userID = @.UserID
>> RS will create a report parameter for this called UserID. You will not
>> use
>> it. You will delete it. Instead in the dataset tab click on the ... go to
>> the parameters tab, click on the dropdown box where the query parameter
>> is
>> being mapped to report parameter and pick expression instead. Then set it
>> to
>> the expression above. If you don't want the domain then use something
>> like
>> this:
>> =mid(User!UserID, LengthToCutOffTheFront)
>> Of course for all of this to work you need a table somewhere that maps
>> the
>> userid to the departments.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Liz" <Liz@.discussions.microsoft.com> wrote in message
>> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
>> > I've got a puzzler here. I have a report with 4 different dropdown
>> > lists
>> > for
>> > parameters. One of them is Department Id. For some users, I want them
>> > to
>> > be
>> > able to only run the report against their own department id but still
>> > be
>> > able
>> > to select any of the values for the other parameters. Other users will
>> > be
>> > able to select any value for department id and the other parameters.
>> >
>> > I can figure out how to write the query to populate the Department ID
>> > dropdown but cannot figure how to access the current userid
>> > programatically.
>> > Is there a way to access the user ID in order to restrict the values in
>> > a
>> > parameter dropdown?
>> >
>> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
>> >
>> > Thanks,
>> > Liz
>> >
>> >
>>

Can I pass parameters from list to chart?

I have a list that is grouped on OrganizationID. Inside the list is a chart
that uses a different dataset. The chart dataset should be passed the
OrganizationID from the list grouping. I''ve tried a few different things,
but always end up with build errors, such as:
Error 1 [rsAggregateInGroupExpression] A group expression for the chart
'chart1' includes an aggregate function. Aggregate functions cannot be used
in group expressions. C:\Projects\AAC.SchoolPlan\2006\SchoolPlan
Reports\SchoolPlan Reports\schoolplan reports\cat6_nosubreport.rdl 0 0
When I look at the Expression editor in the chart, the selected dataset in
the metadata is from the list, but the chart dataset is dsChart. I tried to
add the dataset identifier, but when I do this I'm forced into using an
aggregate function.
Do the data for the list and the chart need to come from the same dataset?
Background:
I was using a subreport for this, but we switched to the Dundas for RS
charting, and MS has an issue where only the last chart is rendered. It's
my understanding that this is due to an issue in SSRS 2005 with the custom
control interfaces when accessed in a subreport controlled by a list. Word
on the street is that it should be fixed in SSRS 2005 SP1.
Thanks,
Steve MunLeeuwI may need to use subreport and wait for sp1. Read: "
Re: Loopig Each Record in dataset"
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:ex0YWYGIGHA.3144@.TK2MSFTNGP10.phx.gbl...
>I have a list that is grouped on OrganizationID. Inside the list is a
>chart that uses a different dataset. The chart dataset should be passed
>the OrganizationID from the list grouping. I''ve tried a few different
>things, but always end up with build errors, such as:
> Error 1 [rsAggregateInGroupExpression] A group expression for the chart
> 'chart1' includes an aggregate function. Aggregate functions cannot be
> used in group expressions. C:\Projects\AAC.SchoolPlan\2006\SchoolPlan
> Reports\SchoolPlan Reports\schoolplan reports\cat6_nosubreport.rdl 0 0
> When I look at the Expression editor in the chart, the selected dataset in
> the metadata is from the list, but the chart dataset is dsChart. I tried
> to add the dataset identifier, but when I do this I'm forced into using an
> aggregate function.
> Do the data for the list and the chart need to come from the same dataset?
> Background:
> I was using a subreport for this, but we switched to the Dundas for RS
> charting, and MS has an issue where only the last chart is rendered. It's
> my understanding that this is due to an issue in SSRS 2005 with the custom
> control interfaces when accessed in a subreport controlled by a list.
> Word on the street is that it should be fixed in SSRS 2005 SP1.
> Thanks,
> Steve MunLeeuw
>|||Solution: Reworked child dataset to include fields from parent, then use
grouping in parent.
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:ufwelhGIGHA.344@.TK2MSFTNGP11.phx.gbl...
>I may need to use subreport and wait for sp1. Read: "
> Re: Loopig Each Record in dataset"
>
> "Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
> news:ex0YWYGIGHA.3144@.TK2MSFTNGP10.phx.gbl...
>>I have a list that is grouped on OrganizationID. Inside the list is a
>>chart that uses a different dataset. The chart dataset should be passed
>>the OrganizationID from the list grouping. I''ve tried a few different
>>things, but always end up with build errors, such as:
>> Error 1 [rsAggregateInGroupExpression] A group expression for the chart
>> 'chart1' includes an aggregate function. Aggregate functions cannot be
>> used in group expressions. C:\Projects\AAC.SchoolPlan\2006\SchoolPlan
>> Reports\SchoolPlan Reports\schoolplan reports\cat6_nosubreport.rdl 0 0
>> When I look at the Expression editor in the chart, the selected dataset
>> in the metadata is from the list, but the chart dataset is dsChart. I
>> tried to add the dataset identifier, but when I do this I'm forced into
>> using an aggregate function.
>> Do the data for the list and the chart need to come from the same
>> dataset?
>> Background:
>> I was using a subreport for this, but we switched to the Dundas for RS
>> charting, and MS has an issue where only the last chart is rendered.
>> It's my understanding that this is due to an issue in SSRS 2005 with the
>> custom control interfaces when accessed in a subreport controlled by a
>> list. Word on the street is that it should be fixed in SSRS 2005 SP1.
>> Thanks,
>> Steve MunLeeuw
>

Can I pass '=' or '>' as parameters?

Hi
I'm moving SQL in my app to Stored Procedures.

My SQL string is currently created within my app, and I have two check boxes and this C# code...

if(chkPartTime.Checked&&!chkFullTime.Checked) { sql+=" AND P0_Hours = 0"; }

if(!chkPartTime.Checked&&chkFullTime.Checked) { sql+=" AND P0_Hours > 0"; }

where sql is my query string

If both boxes are checked I don't care what value P0_Hours is, but at least one of them will be checked.

What I want to know is how I transfer such logic into a Stored Procedure, or whether I will need to creat 2 similar sp's and choose which to call in my app.

It will combined with a query (currently 60 lines) comprising of 3 UNIONs

TIA

Sure why not...

you could literally pass a char(1) of '>' or '=' and use it to build the sql string

However wouldn't it be better to pass 'bit' fields like this:

SP paremeters:

@.chkPartTime bit

@.chkFullTime bit

then in the code:

in the Where clause:

Code Snippet

and ((p0_hours = 0 and @.chkPartTime = 1 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1 and @.chkPartTime = 0))

or better yet... just have one parameter:

@.chkFullTime bit and then 0 = Parttime 1 = FullTime

so then the code would look like:

Code Snippet

and ((p0_hours = 0 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1))

-Robert

|||

use the flag parameter (new param) & pass the appropriate flag value to the sp.

for example on your code,

Code Snippet

int iFlag = 0;

if(chkPartTime.Checked && !chkFullTime.Checked) { iFlag = 1; }

if(!chkPartTime.Checked && chkFullTime.Checked) { iFlag = 2; }

//Pass this flag variable as parameter while calling the SP

on your SP,

Check the flag using IF condition are on where condition to get the required result.

Code Snippet

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

@.Flag = 0

Or

(@.Flag = 1 And P0_Hours =0)

Or

(@.Flag = 2 And P0_Hours >0)

--or

If @.Flag = 0

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

If @.Flag = 1

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours =0

If @.Flag = 2

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours > 0

|||

I don't know, I usually would just suggest a dynamic SQL stored procedure in these cases, particularly if there is any real heft to your data. The other solutions suggested are a good possibilities too, but the complexities of coding tend to turn things like this into a nightmare both to maintain, and to get a decent query plan anyhow.

Louis

|||

Thank you all

It was difficult to choose which to mark as the answer!

I had not heard of dynamic sp's (I come from a DB2 background, now using SQL Server) and I have learnt from all 3 answers, still not made my mind up which way to go, but thanks.