name value pair design

I want to store many different types of objects in a single table. I
was thinking of using the name value pair approach to achieve this.
Does anybody have any experience with a such a design?

The table might look like this
CREATE TABLE NV (pk int, type int, [name] varchar(100), value
varchar(100))

--Insert a manager - type = 1
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'FirstName', 'John')

INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'LastName', 'Smith')

INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'Position', 'CEO')

--Insert an employee - type = 2
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'FirstName', 'Joe')

INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'LastName', 'Blog')

INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'Position', 'Developer')

--Insert an inventory item - type = 3
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Name', 'Chair')

INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Color', 'White')

INSERT INTO NV (type, [name], val)
VALUES (3, 3, 'Price', '$150')rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402111626.6173889b@.posting.google.com>...
> I want to store many different types of objects in a single table. I
> was thinking of using the name value pair approach to achieve this.
> Does anybody have any experience with a such a design?
> The table might look like this
> CREATE TABLE NV (pk int, type int, [name] varchar(100), value
> varchar(100))
> --Insert a manager - type = 1
> INSERT INTO NV (pk, type, [name], val)
> VALUES (11, 1, 'FirstName', 'John')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (11, 1, 'LastName', 'Smith')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (11, 1, 'Position', 'CEO')
> --Insert an employee - type = 2
> INSERT INTO NV (pk, type, [name], val)
> VALUES (21, 2, 'FirstName', 'Joe')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (21, 2, 'LastName', 'Blog')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (21, 2, 'Position', 'Developer')
> --Insert an inventory item - type = 3
> INSERT INTO NV (type, [name], val)
> VALUES (13, 3, 'Name', 'Chair')
> INSERT INTO NV (type, [name], val)
> VALUES (13, 3, 'Color', 'White')
> INSERT INTO NV (type, [name], val)
> VALUES (3, 3, 'Price', '$150')

Generally speaking, this is not good design in a relational database -
each table should represent only one entity. Why not have two tables,
Employees and Inventory? (Managers are employees too.)

In any case, if you can explain what you're trying to do, and why you
are considering this design, then someone may be able to suggest a
better solution.

Simon|||The design is for a Biotech company. We were thinking about using
this
design because it is flexible. For example, we can use it to store
materials that we know about now. It can also be used in the future to
store new materials that we don't yet know about yet that might have
different attributes.

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402120128.7fdad08d@.posting.google.com>...
> rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402111626.6173889b@.posting.google.com>...
> > I want to store many different types of objects in a single table. I
> > was thinking of using the name value pair approach to achieve this.
> > Does anybody have any experience with a such a design?
> > The table might look like this
> > CREATE TABLE NV (pk int, type int, [name] varchar(100), value
> > varchar(100))
> > --Insert a manager - type = 1
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (11, 1, 'FirstName', 'John')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (11, 1, 'LastName', 'Smith')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (11, 1, 'Position', 'CEO')
> > --Insert an employee - type = 2
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (21, 2, 'FirstName', 'Joe')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (21, 2, 'LastName', 'Blog')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (21, 2, 'Position', 'Developer')
> > --Insert an inventory item - type = 3
> > INSERT INTO NV (type, [name], val)
> > VALUES (13, 3, 'Name', 'Chair')
> > INSERT INTO NV (type, [name], val)
> > VALUES (13, 3, 'Color', 'White')
> > INSERT INTO NV (type, [name], val)
> > VALUES (3, 3, 'Price', '$150')
> Generally speaking, this is not good design in a relational database -
> each table should represent only one entity. Why not have two tables,
> Employees and Inventory? (Managers are employees too.)
> In any case, if you can explain what you're trying to do, and why you
> are considering this design, then someone may be able to suggest a
> better solution.
> Simon|||rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402121628.70a8488f@.posting.google.com>...
> The design is for a Biotech company. We were thinking about using
> this
> design because it is flexible. For example, we can use it to store
> materials that we know about now. It can also be used in the future to
> store new materials that we don't yet know about yet that might have
> different attributes.

<snip
You might want to have a look at this article from SQL Server Magazine
(June 2003) on super/subtypes:

http://www.sqlmag.com/Articles/Inde...ArticleID=38656

This is one possible solution to the issue of modelling different
items which share some common attributes (Quantity, UnitPrice, etc.),
but have other attributes which only apply to one type of item
(Colour, NumberOfLegs).

Your approach would be extremely difficult to query, index, and
maintain. In fact, taken to an extreme, you would end up with only one
table in the database. Even if you finally decide to have some degree
of denormalization to handle very diverse inventory items, there's no
good reason to include employees in the same table.

Simonsql

name of the variable (228 row(s) affected)

hello,
after executing a insert in the query anlayser i get a info like (228 row(s)
affected)
How can i acces this value. Is there a internal variablename @.@....
I want to use this value for writing it in a logtable...
Is there any overview of the internal variables which can be used ...
thanks> How can i acces this value. Is there a internal variablename @.@....
Use @.@.ROWCOUNT. A good practice is to save the value in a local variable
for subsequent use.

> Is there any overview of the internal variables which can be used ...
See 'System Functions' in the Books Online for a list of these scalar
functions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:086F2B36-0CE9-460D-BA39-5D7633B4F286@.microsoft.com...
> hello,
> after executing a insert in the query anlayser i get a info like (228
> row(s)
> affected)
> How can i acces this value. Is there a internal variablename @.@....
> I want to use this value for writing it in a logtable...
> Is there any overview of the internal variables which can be used ...
> thanks|||SELECT @.@.ROWCOUNT;
Only available in the very next statement.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:086F2B36-0CE9-460D-BA39-5D7633B4F286@.microsoft.com...
> hello,
> after executing a insert in the query anlayser i get a info like (228
> row(s)
> affected)
> How can i acces this value. Is there a internal variablename @.@....
> I want to use this value for writing it in a logtable...
> Is there any overview of the internal variables which can be used ...
> thanks|||thanks
Xavier
"Dan Guzman" wrote:

> Use @.@.ROWCOUNT. A good practice is to save the value in a local variable
> for subsequent use.
>
> See 'System Functions' in the Books Online for a list of these scalar
> functions.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:086F2B36-0CE9-460D-BA39-5D7633B4F286@.microsoft.com...
>
>

name of month from int value

What is the best way to get the month name from an int value
within a mssql statement ?
(there is no need to consider the language settings)

e.g.
1 -> January
11 -> Novembre

My first approach seems to work but is very ugly:
CASE WHEN my_month_int<10
THEN DATENAME(month,"20000"+cast(my_month_int as varchar(2))+"15 00:00:00")
ELSE DATENAME(month,"2000"+cast(my_month_int as varchar(2))+"15 00:00:00")
END AS my_month_nameselect datename(m,dateadd(m,my_month_int-1,0)) as my_month_name

:cool:|||Thanx a lot, r937. Nice trick.sql

Name cannot begin with the '>' character, hexadecimal value 0x3E

Hi,

This is my first attempt at SSRS, I've added code section to the xml file. But when I use <> for not equal to, it is giving me this error. What should I do? The code is as posted here.

Thanks,

Debi

***************************************************************************************

<Code>
Dim PrevId as Object
Dim a_Count As Double
Dim b_Count As Double
Dim c_Count As Double
Dim d_Count As Double
Dim e_Count As Double
Dim Total As Double = 0
Function SumMPCount(ByVal CurrID As Object,ByVal code As Object, ByVal NewCount As Object) As Double

If(CurrID is Nothing or code is Nothing or NewCount is Nothing)Then
Exit Function
End if
If PrevId Is Nothing Then
PrevId = CurrId
End if
If CurrID <> PrevID Then
a_Count = 0
b_Count = 0
c_Count = 0
d_Count = 0
e_Count = 0
Total = 0
End if
Select Case mp_Code
Case "a"
a_Count = a_Count + NewCount
SumMPCount = a_Count
case "b"
b_Count = b_Count + NewCount
SumMPCount = b_Count
case "c"
c_Count = c_Count + NewCount
SumMPCount = c_Count
case "d"
d_Count = d_Count + NewCount
SumMPCount = d_Count
case "e"
Total = a_Count + b_Count + c_Count + d_Count + e_Count
SumMPCount = Total
case else
e_Count = e_Count + NewCount
SumMPCount = e_Count
End Select

PrevId = CurrId
End Function
</Code>

It looks like you directly copy&pasted the code snippet into the RDL(XML) file and therefore the <> is not encoded an is interpreted as invalid xml element.

You have two options - either copy&paste the code snippet into the report designer custom code window (and report designer will automatically encode the characters correctly), or replace the <> in the RDL file with &lt;&gt; (which is the encoded representation).

-- Robert

|||

Thanks for the reply. Where is the custom code window? When I right click on the report and select properties, it just shows me the usual Properties tab on the RHS which shows background color, image, etc..

I actually worked around it using Object variables and using "Is" instead of <> .

|||I think he might have meant
right click on the report.rdl -> Prooperties -> Code tab -> paste theresql

N' in criteria ?

Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
GinolaThe N denotes that the string is Unicode.
Andrew J. Kelly SQL MVP
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...

quote:

---

> Hi,
> I have new in SQL server. I am using Access for quite a bit. When I
> put the value under Criteria column, in query, It puts N' before the
> value, what's that mean ' Why they have to do it ?
> Thanks
>
> --
> Ginola

---

|||N indicates that the string after it is a Unicode string. Use N prefix, only
when comparing a value against a Unicode column, otherwise you will see
performance degradation, as an index may not be used.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...
Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
Ginola|||thanks all for helping.
On Mon, 12 Jan 2004 10:52:06 -0000, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

quote:

---

>N indicates that the string after it is a Unicode string. Use N prefix, onl
y
>when comparing a value against a Unicode column, otherwise you will see
>performance degradation, as an index may not be used.

---

Ginola

N' in criteria ?

Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
--
GinolaThe N denotes that the string is Unicode.
--
Andrew J. Kelly SQL MVP
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...
> Hi,
> I have new in SQL server. I am using Access for quite a bit. When I
> put the value under Criteria column, in query, It puts N' before the
> value, what's that mean ' Why they have to do it ?
> Thanks
>
> --
> Ginola|||N indicates that the string after it is a Unicode string. Use N prefix, only
when comparing a value against a Unicode column, otherwise you will see
performance degradation, as an index may not be used.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...
Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
Ginola|||thanks all for helping.
On Mon, 12 Jan 2004 10:52:06 -0000, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:
>N indicates that the string after it is a Unicode string. Use N prefix, only
>when comparing a value against a Unicode column, otherwise you will see
>performance degradation, as an index may not be used.
--
Ginola

mystery truncating

I have table in SQL Server 2000 that stores comments in one field of type
varchar with a length of 250. A test value entered is 249 characters long.
When I run the following:
select len(comment) from tblcomments where emplid = '241s'
the len returned is 249, as expected. However, once I try concatenating
date and user name info, funny stuff starts happening.
For example, if I run this code:
select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
Comment)
from tblcomments where emplid = '241S'
len returned is 284, which is correct. But when the code is run without
using the len() function the result returned has been truncated by 28
characters. I end up with a character output of only 256 characters. I get
the date, the ntusername and the first 221 characters of the comment sting.
For some reason, after concatenating the additional info I'm losing 28
characters and the output is limited to 256 characters. But again, when
using the len function I'm getting 284. What is happening? Any suggestions
?> characters. I end up with a character output of only 256 characters
Where, in QA?
Go to Tools - Options... - Results and increase "Maximum characters per
column". Max value allowed is 8K or 8192.
AMB
"kiloez" wrote:

> I have table in SQL Server 2000 that stores comments in one field of type
> varchar with a length of 250. A test value entered is 249 characters long
.
> When I run the following:
> select len(comment) from tblcomments where emplid = '241s'
> the len returned is 249, as expected. However, once I try concatenating
> date and user name info, funny stuff starts happening.
> For example, if I run this code:
> select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
> Comment)
> from tblcomments where emplid = '241S'
> len returned is 284, which is correct. But when the code is run without
> using the len() function the result returned has been truncated by 28
> characters. I end up with a character output of only 256 characters. I g
et
> the date, the ntusername and the first 221 characters of the comment sting
.
> For some reason, after concatenating the additional info I'm losing 28
> characters and the output is limited to 256 characters. But again, when
> using the len function I'm getting 284. What is happening? Any suggestio
ns?
>|||Alejandro, thank you. How can this be done when running the same code from
a
stored procedure? I don't see a similar option in Enterprise Mgr. The
problem was first noticed when running this code from a sp.
"Alejandro Mesa" wrote:
> Where, in QA?
> Go to Tools - Options... - Results and increase "Maximum characters per
> column". Max value allowed is 8K or 8192.
>
> AMB
> "kiloez" wrote:
>|||Where did you run the sp?
This is an internal setting of the client app (QA).
AMB
"kiloez" wrote:
> Alejandro, thank you. How can this be done when running the same code fro
m a
> stored procedure? I don't see a similar option in Enterprise Mgr. The
> problem was first noticed when running this code from a sp.
> "Alejandro Mesa" wrote:
>|||Alejandro, thanks again for replying. I found out what the problem was. I'
m
actually running this sp from a dataenvironment in VB6. After checking the
properties for the sp in the DE I saw that the length for the output
parameter was only 250. After changing it to 8000 output was as it should
be. The character length had already been changed in the sp itself.
"Alejandro Mesa" wrote:
> Where did you run the sp?
> This is an internal setting of the client app (QA).
>
> AMB
> "kiloez" wrote:
>

my value doesnt reset when i used sum(field, scope)

pls help what seems to be the problem? my sum doesnt restart as the group changes. instead it accumulates the sum? thanks.

use

RunningValue(Fields, Sum, scope)

my value doesnt reset when i used sum(field, scope)

pls help what seems to be the problem? my sum doesnt restart as the group changes. instead it accumulates the sum? thanks.

use

RunningValue(Fields, Sum, scope)

my procedure not returning any value

hi evry one
is there any problem with my code it dos'nt returen any value
please help
CREATE PROCEDURE [dbo].[mMaxId]
AS
set nocount on
declare @.Id bigint
declare @.mID bigint
select @.id = max(TransId) from tbltransaction
if (@.id is null)
begin
set @.mid = 1
end
if ( @.id is not null)
begin
set @.mid = @.id +1
end
return
GOWhat is it supposed to return ? Mid , ID ? Then you have to specify an outpu
t
paramter to catch the value from the calling procedure, if you have a
resultset you can use the resultset by inserting that in temp table while
executing
Insert into #SomeTable
EXEC(Someprocedure)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"mowafy" wrote:

> hi evry one
> is there any problem with my code it dos'nt returen any value
> please help
> CREATE PROCEDURE [dbo].[mMaxId]
> AS
> set nocount on
> declare @.Id bigint
> declare @.mID bigint
> select @.id = max(TransId) from tbltransaction
> if (@.id is null)
> begin
> set @.mid = 1
> end
> if ( @.id is not null)
> begin
> set @.mid = @.id +1
> end
> return
> GO
>|||Hi
Although you can use a select statement to return @.mid as a result set,
depending on what your requirements are, it may be more efficient to use an
output parameter
CREATE PROCEDURE [dbo].[mMaxId] @.mID bigint OUTPUT
AS
set nocount on
SET @.mid =ISNULL(SELECT max(TransId) from dbo.tbltransaction ),0) + 1
return
GO
DECLARE @.nextId bigint
EXEC [dbo].[mMaxId] @.nextId OUTPUT
...
If you are only wanting this number to allocate unique numbers to the
transaction id, then an identity column may be an easier way to implement
this functionality (although they may not be contiguous). You will need to
make sure that you don't write the code in such a way that two processes can
use the same transaction id.
John
"mowafy" wrote:

> hi evry one
> is there any problem with my code it dos'nt returen any value
> please help
> CREATE PROCEDURE [dbo].[mMaxId]
> AS
> set nocount on
> declare @.Id bigint
> declare @.mID bigint
> select @.id = max(TransId) from tbltransaction
> if (@.id is null)
> begin
> set @.mid = 1
> end
> if ( @.id is not null)
> begin
> set @.mid = @.id +1
> end
> return
> GO
>