does MSSQL have the ability to do "named row types?"

Hi all. We have a mix of informix and mssql server and I want to know
if something we do in informix has an analogous feature in MSSQL. We
can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we
do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids
having different developers build the same type of field in more than
one way, different lengths, etc.

Is there a similar function in MSSQL server?sumGirl (emebohw@.netscape.net) writes:
> Hi all. We have a mix of informix and mssql server and I want to know
> if something we do in informix has an analogous feature in MSSQL. We
> can define a "row type" in informix, like so:
> create row type name_1(fname char(20),lname char(20));
> The when we create any table that includes a first and last name, we
> do so using this row type like so:
> create table sometable(name name_1, some column,...etc)
> This allows us to set a standard for certain common fields and avoids
> having different developers build the same type of field in more than
> one way, different lengths, etc.
> Is there a similar function in MSSQL server?

In SQL 2000, no.

In SQL 2005, which currently is in beta, you can define structured types
in CLR languages like Visual Basic or C#, but it's not really the same
thing as the Informafix feature. I assume that in the Informix case you
can still say:

SELECT fname FROM sometable WHERE ...

and thus refer to the individual columns directly. With a CLR type in
SQL2005 you would have to say:

SELECT name.fname FROM sometable WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Do While Skip in Selecting...

Hello:

I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?

Here is a code snippet of what is going on:

 Do While varCount < varRecordCount
 conSqlConnect.Open()
 cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@.idnumber and date<@.date", conSqlConnect)
 cmdSelect.Parameters.Add( "@.accountnumber", "10000" )
 cmdSelect.Parameters.Add( "@.date", dtnow )
 dtrdatareader = cmdSelect.ExecuteReader()
 While dtrdatareader.Read()
 If IsDbNull(dtrdatareader("value")) = false Then
 varValue = dtrdatareader("value")
 End If
 End While
 dtrdatareader.Close()
 conSqlConnect.Close()
'#####The information above is copied to another table here
'#####The record where the information was received is deleted.
 varCount = varCount + 1
 Loop

Any ideas?After playing around with this for a while, I found that the select statement is incorrect. The part where it says date<@.date... this selects more than one row, instead of just selecting one row.

I read articles that discuss using select MAX(column) but that will only return one column... how can I select a row based on a column with the maximum value?|||ok... great got it working now... just post the answer here for future reference... I went ahead and did Select Top 1 * instead of Select *... it solved everything...