code to return a dataset

Our database is normalized to the point that I need to build a dataset to
render a report from. The code to build the dataset has logic that I would
like to keep in a custom assembly. What is the best way to call that code
and have it pass the dataset back for use by the report?
Thanks,
ShawnYou'd make life alot easier for yourself if you build the code using a
stored procedure... Any reason not to? You can encrypt it if that is your
concern.
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from. The code to build the dataset has logic that I
> would
> like to keep in a custom assembly. What is the best way to call that code
> and have it pass the dataset back for use by the report?
> Thanks,
> Shawn|||Thanks, but the users that will be implementing the reports won't have the
ability to make stored procedures. Is there another way?
"Mary Bray [MVP]" wrote:
> You'd make life alot easier for yourself if you build the code using a
> stored procedure... Any reason not to? You can encrypt it if that is your
> concern.
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from. The code to build the dataset has logic that I
> > would
> > like to keep in a custom assembly. What is the best way to call that code
> > and have it pass the dataset back for use by the report?
> >
> > Thanks,
> > Shawn
>
>

COALESCE with parameters

I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):

select * from dbo.employee

where LastName>=COALESCE(@.ln,lastname) andLastName<=COALESCE(@.ln2,lastname) andFirstName>=COALESCE(@.fn,firstname) andFirstName<=COALESCE(@.fn2,firstname) andhiredate>=COALESCE(@.hire,hiredate) andhiredate<=COALESCE(@.hire2,hiredate) andcheckdate>=COALESCE(@.chk,checkdate) andcheckdate<=COALESCE(@.chk2,checkdate)

The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.

I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it?

Or is there a better way to use the COALESCE function?

Thanks all!

I would add a third parameter to the coalesce function. For instance COALESCE(@.var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.

-Chris

|||If you couldn't choose some value as "empty", you could add additional parameter like @.ln_is_empty and then use something like (@.ln_is_empty=1 or (Lastname>=@.ln and @.ln_is_empty=0) )|||

Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.

Once this has been done you can use:

WHERE ((checkdate >= COALESCE(@.chk,checkdate) and checkdate <= COALESCE(@.chk2,checkdate))

OR (@.checkdateisnull = 1 AND checkdate IS NULL))

As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.

Chris

|||

There are better ways to write this query than using COALESCE with column names. The above usage will negate use of any indexes on the columns. So you will be pretty much scanning the entire table for any combination of parameters. See the link below for various techniques that will help you solve the problem. Look for my name to see some techniques that use COALESCE/ISNULL but with better results. Erland also covers in detail other techniques that will help you get the best results.

http://www.sommarskog.se/dyn-search.html

|||

Thanks!!!

I'll be studying that document for quite some time!

COALESCE help

I have a pulldown menu which has like 4 options

producta productb productc and all

I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc

Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC

and I am trying to use coalesce but unable to get my result.

I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.

If I do the following with no max funciton, i see all the values but i just want max from each branch.

DECLARE @.buildListvarchar(100)

select @.buildlist=COALESCE(@.buildList+', ','')+convert(varchar(10),build)from results

where branchin('ProductA','Product B','ProductC')

select @.buildList

Please let me know how to do this.

Please post some sample data from the table and expected output..

|||

Package Branch maxBuildNumber

Package1 Product A 2001

Package1 Product B 3004

Package1 Product C 4003

I want it as

Package buildList

Package1 2001,3004,4003

or better yet

Package1 ProductA.2001,ProductB.3004,ProductC.4003

|||

Close...

Declare @.TTable (Packagevarchar(10), Branchvarchar(10), maxBuildNumberint)Insert into @.TSelect'Package1','ProductA', 2001unionallSelect'Package1','ProductB', 3004unionallSelect'Package1','ProductC', 4003DECLARE @.buildListvarchar(100)SELECT @.buildlist=COALESCE(@.buildList +', ','') +convert(varchar(10),branch) +'.' +convert(Varchar, T.maxBuildNumber )FROM @.T TWHERE T.Package ='Package1'Select @.buildList

|||

Hi ,

Thank you for the coalesce help.Now I have a small problem with in that.

I do not want the whole of the branch name to be displayed in my buildlist(name of my branches are pretty long ..so want to display a short name instead)

I have this coalesce in a scalar function where I am returning it as a varchar.

Now before returning it, is it possible to check this buildlist for a pattern and replace it ?

suppose it is being displayed as productA/xy/ABCD.301 ....i want to display it as ABCD.301.

The value coming for Branch from my results table is something like productA/xy/ABCD.

I tried using Contains and replace but do not seem to work on declared variables?

This is what is in my coalesce

SELECT @.buildlist=COALESCE(@.buildList+' ','')+convert(varchar(50),Branch)+'.'+convert(Varchar, v_allbranchinfo.MAXBuildNumber)

FROM v_allbranchinfoWHERE /*some where conditions*/

IF @.buildlistcontains(@.buildlist,"Orcas/pu/DDE")

replace(buildlist,"Orcas/pu/DDE","DDE")

Can you please with this?

|||

After getting the @.Buildlist you can do a replace..

IF CHARINDEX(@.buildlist,'Orcas/pu/DDE') > 0SET @.BuildList =REPLACE(@.buildlist,'Orcas/pu/DDE','DDE' )

|||

You are great!

Thank you very much!

|||

Another problem now with coalese, I am getting the achived result as having all the branch build numbers in one row but on my webpage when I am displaying these results, I use a hyper link which would show some addition info from the same results table such as runid,total etc corresponding to each build.Now when I have the buildlist, I am not sure on how to handle this .

My query now looks like this:

(SELECT T1.*, T1.BuildListAS dataFROM v_BuildListerAS T1INNERJOIN

(SELECT SKU, OS, OSLang, ProductLang, Branch,MAX(Build)AS MaxBuild

FROM dbo.ResultsGROUPBY SKU, OS, OSLang, ProductLang, Branch)AS T2ON T1.SKU= T2.SKUAND

T1.OS= T2.OSAND T1.OSLang= T2.OSLangAND T1.ProductLang= T2.ProductLang)AS T3ON

dbo.Results.ID= T3.IDWHERE(dbo.Results.OSArch='Intel')AND(dbo.Results.OSLang='English - United States')AND(dbo.Results.ProductLang='ENU'))

v_Buildlister is a view on results table and the view doesn't have this runid etc information.

Even if it does, since it is a buildlist and not single build...does not give me proper information.

previously my T1 was results table .

Any idea will be appreciated.