Create "Object Type" in sql server

I'm facing problem while creating object type in sql server.
ex:sql query
"create type student as object ( name varchar2 ( 12) , no number ( 5))" is working fine in Oracle where 'student' can be used in any table as datatype, but its not working in sqlserver. Please can you help me how to create object type in sql server.

Thanks and Regrads,

Suzan:

To create a user defined datatype you need to use the sp_addtype procedure. You might want to look this up in books online. There is an example of use on this page:

http://msdn2.microsoft.com/en-us/library/aa259606(SQL.80).aspx

|||

Thanks , but what i am looking for is different.

i am looking for somting in sql server similar to Oracle OBJECT type

CREATE TYPE Pet_t AS OBJECT (
tag_no INTEGER,
name VARCHAR2(60),
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN Pet_t
);

is there a way in sql server to do so....

Thanks

|||

If you're using SQL Server 2005 then you can create CLR user-defined types.

Check out this BOL link for more info:

http://msdn2.microsoft.com/en-us/library/ms131120.aspx

Chris

|||

i have found that this is possible using SQL server 2005, but i need a way to do it in sql server 200.

is it possible to do so?

suzan

|||

Nope.. It is not posible in SQL Server 2000.

SQL SERVER 2000 uses UDT as synonyms for pre-defined types (example - PhoneNumber := Varchar(24) ,etc).

The possible alternate solution is storing your data as BINARY. The issue here is you wont retrive your data on SQL statements (not visible on your QA), you have to depend on your UI/BL to retrive and view the data.

Other simple solution may be storing the data as XML.

|||Thanks ...|||Thanks , but do u have a link to where i can find how to store the data as xml

counting DONE and NOT DONE totals (was "Newbie SQL problem")

Hello all,

i'm a newbie in sql, and currently i am facing some problems in sql. My questions is as shown:

I have a table with about 30,000 records, the sample columns of this table is as following:

--------------------
CPN | MPN | Status | Requester | Project_Name | ReceivedDate
--------------------
CN8 CNA1 DONE John SUN 2006/03/01
CN8 CNA2 NOT_DONE John SUN 2006/03/01
BF7 GHE1 DONE Alex MICRO 2006/04/01
BF8 GHE2 DONE Alex MICRO 2006/04/01
BF9 GHE3 NOT_DONE Alex MICRO 2006/04/01
BF0 GHE4 NOT_DONE Alex MICRO 2006/04/01

How can i make it to become:
--------------------
Project_Name | Requester | ReceivedDate | Total_DONE(%) |Total_CPN(%)| DONE | NOT_DONE | Total |

SUN | John | 2006/03/01 | 50% | 100% | 1 | 1 | 2
MICRO| Alex | 2006/04/01 | 50% | 50% | 2 | 2 | 4

Total_DONE(%) is base on how many % of MPN done
Total_CPN(%) is base on how many % of CPN done

For example, in John case, 'CNA1' is done while 'CNA2' is not done, so Total_DONE(%) = 50%, but since both MPN is under same CPN (CN8), so once one of the MPN under this CPN is done, it considered done for Total_CPN(%), so Total_CPN(%) = 100%

Any unclear and doubt please ask me in the thread.
Your advices and help is much appreciated, thanks!Hi edx. What have you tried so far? regards, Fazza|||So far i still cant figure out how to make it done, do u have any idea?|||Here's a hint. Try using a CASE statement to convert your DONE and NOT_DONE to zeros and ones. Then SUM and divide the results to get your percentages.|||Thanks for the reply urquel, ya, it's work by using the 'case' method for Total_DONE(%), but how bout the Total_CPN(%)?Since the Total_CPN have nested group method involved, whereby after group by 'project_name', i need to group again by the CPN, means if one of the MPN under same CPN is DONE, it's considered DONE..

Here is my progress so far:)

----------------------
SELECT project_name , requester, ReceiveDate,
COUNT(CASE WHEN Status = 'DONE' THEN 1 END) * 100 / COUNT(*) as [Total_DONE(%)],
COUNT(CASE WHEN Status = 'DONE' THEN 1 END) as DONE,
COUNT(CASE WHEN Status = 'NOT_DONE' THEN 1 END) as X_DONE,
COUNT(*) as TOTAL,
GROUP BY Project_Name, Requester, ReceivedDate

----------------------

Still having trouble for the CPN(%)...anyone able to help?