Max function

Hi,

The question is about the way max function works in SQL. I have to implement a database design where in i have to write a stored procedure which will perform an insert in the table/tables. This table/tables will in the long run have a large number of records, i have been instructed to create another table (lets call it tableA) which will have the primary keys of all the other tables and their respective maximum values being used. While performing an insert into one of these tables, i will have to probe the tableA and find out the currently used id for that table ( in which i have to insert a new row) and use the value for insertion.

According to me, we can use a max clause in the stored procedure and achieve the following or use an Indentity column to do the same.

I will need insights into how max function works when it tells me the number of records (does it traverse thru the entire table or has the number of rows stored somewhere? I just need the number of rows in the table and nothing else but would like to know the max function working)
Also how will identity column perform under these situations.

Any information required, i will be glad to give.

VihangJust use the identity field, it's what it's designed for. If you need a table which reflects the maximum id field in each for some reason (but hopefully you don't any more), then use a view which queries the max(id) field from each table, and a 'union all' to show the lot. Something like this:

create view maxids as
select 'table1' as tablename, max(id) as maxid
from table1
union all
select 'table2' as tablename, max(id) as maxid

from table2
union all
...etc

I hope this helps...

Rob

--
Rob Farley
President - Adelaide SQL Server User Group
rob_farley@.hotmail.com (Email &

Msgr)
http://msmvps.com/blogs/robfarley|||Hi Rob,

Thank you for your prompt input. I appreciate it, I have understood what you have suggested.

I need to understand how does the max function work? does it go thru all the rows to calculate the maximum of a column?.

Regards,
Vihang|||Not when there's a useful index it can use. As these 'id' fields will be your primary keys (if not, they should be!), then the system can very easily find out what the maximum value is. Being identity columns, the system is designed to be able to very quickly report what the largest value is.|||Hi Rob,

Thanks for your insights, I now know how it really works.

Regards,
Vihang

Matrix Help

I need some help, this is my first matrix report. My stored procedure
pulls data in the following format.
Internal_User user_id expense_user_id Amount
category Period month year
Doe, John 99 1234567 385.7900
Airfare May-2007 5 2007
ETC.
I have the matrix set up currently with Row Groups on Internal_user
and Category, and column group on Period. This looks good, what I need
though is a total by internal user for all the categories. Then I
need a category total in the last column. When I try to do a subtotal
on the Internal_User rowgroup it says total, but only spits out the
first category value.
Heres what it looks like
Jan-2007
Feb-2007 Mar-2007 Apr-2007 May-2007
Doe, John
AirFare
385.79
Entertainment 1587.22
556.2 537.39 1996.30
Taxi 100.00
100.00 200.00
Total 1587.22
556.2 537.39 1996.61 358.79
I want the total like for Jan-2007 for example to be 1687.22. I
appreciate any help.Disregard...found the answer.

Matrix dataset filter out null

I have a dataset which has a ContractNo column which can take a null value. I
cannot carry out the filter on the stored procedure which generates my
dataset.
This dataset is used in a matrix. I need to filter out all rows with a null
ContractNo. I thought I could do this on the matrix properties filter tab
with an expression like:
Fields!ContractNo.Value<>Nothing
However there doeas not appear to be a <> (or not equal to) operator in the
drop-down list in RS2000.
How do I achieve this filter?
What is the != operator?
TIA,
Mageno_manDont know if this will help, but the way I have used to get around it is:
Fields!ContractNo.Value<>Nothing in the formula and make the operator the =and then the value "=true"
Expression Operator
Value
Fields!ContractNo.Value<>Nothing ==true
I hope this makes sense.
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:75AD1157-EBF6-4E95-8A46-A568A0B5933C@.microsoft.com...
>I have a dataset which has a ContractNo column which can take a null value.
>I
> cannot carry out the filter on the stored procedure which generates my
> dataset.
> This dataset is used in a matrix. I need to filter out all rows with a
> null
> ContractNo. I thought I could do this on the matrix properties filter tab
> with an expression like:
> Fields!ContractNo.Value<>Nothing
> However there doeas not appear to be a <> (or not equal to) operator in
> the
> drop-down list in RS2000.
> How do I achieve this filter?
> What is the != operator?
> TIA,
> Mageno_man|||!= is 'does not equal to'
does it solve your problem? :)
"magendo_man" wrote:
> I have a dataset which has a ContractNo column which can take a null value. I
> cannot carry out the filter on the stored procedure which generates my
> dataset.
> This dataset is used in a matrix. I need to filter out all rows with a null
> ContractNo. I thought I could do this on the matrix properties filter tab
> with an expression like:
> Fields!ContractNo.Value<>Nothing
> However there doeas not appear to be a <> (or not equal to) operator in the
> drop-down list in RS2000.
> How do I achieve this filter?
> What is the != operator?
> TIA,
> Mageno_man|||Had to put an equals sign in expression, i.e expression is
=Fields!ContractNo.Value<>Nothing
"Ben Watts" wrote:
> Dont know if this will help, but the way I have used to get around it is:
> Fields!ContractNo.Value<>Nothing in the formula and make the operator the => and then the value "=true"
> Expression Operator
> Value
> Fields!ContractNo.Value<>Nothing => =true
> I hope this makes sense.
>
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:75AD1157-EBF6-4E95-8A46-A568A0B5933C@.microsoft.com...
> >I have a dataset which has a ContractNo column which can take a null value.
> >I
> > cannot carry out the filter on the stored procedure which generates my
> > dataset.
> >
> > This dataset is used in a matrix. I need to filter out all rows with a
> > null
> > ContractNo. I thought I could do this on the matrix properties filter tab
> > with an expression like:
> >
> > Fields!ContractNo.Value<>Nothing
> >
> > However there doeas not appear to be a <> (or not equal to) operator in
> > the
> > drop-down list in RS2000.
> >
> > How do I achieve this filter?
> >
> > What is the != operator?
> >
> > TIA,
> > Mageno_man
>
>

Master-Detail w/Gridview-DetailsView Stored Procedure Problem

I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control.

SelectCommandType specifies that you are using a stored proc. SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.

Is it even possible or do I have to forget about using the DetailsView control altogether?

you should use an sql parameter like this in your sql data source:

here a dropdown list value is taken as a parameter:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:urConnString %>"

SelectCommand="urSP"SelectCommandType="StoredProcedure"EnableCaching="True">

<SelectParameters>

<asp:ControlParameterControlID="dropdown"Name="urparamname"PropertyName="SelectedValue"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

hope this helps.

|||

Thanks Raj, but I have already done that. My code, as you suggested, looks like this:

<asp:SqlDataSource ID="DetailsViewSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ForexturtleConnectionString %>"
DeleteCommand="swsp_DeleteCompany" DeleteCommandType="StoredProcedure" InsertCommand="swsp_InsertCompany"
InsertCommandType="StoredProcedure"SelectCommand="swsp_GetCompany" SelectCommandType="StoredProcedure"
UpdateCommand="swsp_UpdateCompany" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="swCompanyId" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>

The stored procedure (swsp_GetCompany) requires the record ID as a parameter to grab the correct record. It works great against the database. GridView1 had previously selected the correct record, but I don't see how it supplies swCompanyId to DetailsViewSqlDataSource:

<asp:SqlDataSource ID="GridViewSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ForexturtleConnectionString %>"
SelectCommand="swsp_GetCompanies" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="swCompanyId" DataSourceID="GridViewSqlDataSource">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="swCompanyId" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="swCompanyId" />
<asp:BoundField DataField="swName" HeaderText="Company" SortExpression="swName" />
</Columns>
</asp:GridView>

I still get an error that says, "Procedure or function 'swsp_GetCompany' expects parameter '@.id', which was not supplied."

|||

I am not sure as how to pass the value from a gridview to detailsview as a parameter...

one thing u can check is the name of the parameter... if u have used "@.id" in ur SP, then try to use the same name from .aspx page also...but here u r passing 'swcompanyID' which is not expected by the SP...

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="swCompanyId" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>

hope this helps you...

|||

Thanks so much for your help! As it turned out, I modified the name of the parameter in the stored procedure from @.id to @.swCompanyId and everything worked great. Thanks again for your help.

master.dbo.xp_fixeddrives

SQL Server 2000
master.dbo.xp_fixeddrives stored procedure is reporting different values
when executed by 'sa' and a regular user.
Results:
(When executed by 'sa')
drive MB free
-- --
C 18432
D 118784
E 16384
(3 row(s) affected)
(When executed by 'UserA')
drive MB free
-- --
C 8192
D 68608
E 3072
(3 row(s) affected)
Is there any reason for this difference?
Thanks,
--payyans
sorry this might be stating the obvious but,
ensure that UserA and sa are logged on to the same server.
"payyans" wrote:

> SQL Server 2000
> --
> master.dbo.xp_fixeddrives stored procedure is reporting different values
> when executed by 'sa' and a regular user.
> Results:
> (When executed by 'sa')
> drive MB free
> -- --
> C 18432
> D 118784
> E 16384
> (3 row(s) affected)
> (When executed by 'UserA')
> drive MB free
> -- --
> C 8192
> D 68608
> E 3072
> (3 row(s) affected)
> Is there any reason for this difference?
> Thanks,
> --payyans
>

master.dbo.xp_fixeddrives

SQL Server 2000
--
master.dbo.xp_fixeddrives stored procedure is reporting different values
when executed by 'sa' and a regular user.
Results:
(When executed by 'sa')
drive MB free
-- --
C 18432
D 118784
E 16384
(3 row(s) affected)
(When executed by 'UserA')
drive MB free
-- --
C 8192
D 68608
E 3072
(3 row(s) affected)
Is there any reason for this difference?
Thanks,
--payyanssorry this might be stating the obvious but,
ensure that UserA and sa are logged on to the same server.
"payyans" wrote:

> SQL Server 2000
> --
> master.dbo.xp_fixeddrives stored procedure is reporting different values
> when executed by 'sa' and a regular user.
> Results:
> (When executed by 'sa')
> drive MB free
> -- --
> C 18432
> D 118784
> E 16384
> (3 row(s) affected)
> (When executed by 'UserA')
> drive MB free
> -- --
> C 8192
> D 68608
> E 3072
> (3 row(s) affected)
> Is there any reason for this difference?
> Thanks,
> --payyans
>