Name value pair

[Chris]

Hi,

I need to display a dataset where everything is dynamic.

e.g. I have a table with columns "Code", "Descriptio n" and "Inspected" and
another table with columns "UserCode", "Name", "PostCode" and "Town" etc

And I need to dislay data like this from a single db proc with parameters:-
(TableName, ColumnName, ColumnValue)


Procedure called with these parameters (CodeTable, Code, TD001) would return
a dataset like this:-
----------------------------------
|Code | TD001
|Description| Printer
|Inspected | Y
---------------------------------
Not
----------------------------------
|TD001
|Printer
|Y
---------------------------------

Procedure called with these parameters (UserTable, UserCode, CP1) would
return a dataset like:-
---------------------------------
|UserCode | CP1
|Name | Charles
|PostCode | 2000
|Town | Sydney
---------------------------------
Not
---------------------------------
|CP1
| Charles
| 2000
| Sydney
---------------------------------
Any ideas how I would code the database proc, I did consider using XML but
not sure.

Thanks

AJP

[Erland Sommarskog]

Re: Name value pair

Chris (chrispow88NOSP AM@hotmail.com) writes:

I need to display a dataset where everything is dynamic.
>
e.g. I have a table with columns "Code", "Descriptio n" and "Inspected" and
another table with columns "UserCode", "Name", "PostCode" and "Town" etc
>
And I need to dislay data like this from a single db proc with
parameters:- (TableName, ColumnName, ColumnValue)
>
>
Procedure called with these parameters (CodeTable, Code, TD001) would
return a dataset like this:-
----------------------------------
>|Code | TD001
>|Description | Printer
>|Inspected | Y
---------------------------------
Not
----------------------------------
>|TD001
>|Printer
>|Y
---------------------------------

But why not:

Code Description Inspected
TD001 Printer Y

and then do the presentation in the client layer?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Plamen Ratchev]

Re: Name value pair

Try this:

CREATE TABLE CodeTable(
Code VARCHAR(4) NOT NULL,
Description VARCHAR(200) NOT NULL
CONSTRAINT DF_Description DEFAULT (''),
Inspected VARCHAR(1) NOT NULL
CONSTRAINT DF_Inspected DEFAULT (''),
RowTimestamp ROWVERSION NOT NULL)

INSERT INTO CodeTable(Code, Description, Inspected)
VALUES ('A1', 'A1 Description', 'Y')
INSERT INTO CodeTable(Code, Description, Inspected)
VALUES ('B1', 'B1 Description', 'Y')
INSERT INTO CodeTable(Code, Description, Inspected)
VALUES ('C1', 'C1 Description', 'N')

GO

CREATE PROCEDURE SearchMyCodes
@column_name VARCHAR(100),
@column_where_v alue VARCHAR(100),
@table_name VARCHAR(100)

AS
BEGIN
SET NOCOUNT ON

DECLARE @sql NVARCHAR(4000)

SET @sql = ''

SELECT @sql = @sql +
N'SELECT ''' + column_name + ''' AS column_name, ' +
column_name + ' AS column_value ' +
' FROM ' + @table_name +
' WHERE ' + @column_name + ' = @column_value ' +
' UNION ALL '
FROM INFORMATION_SCH EMA.COLUMNS
WHERE table_name = @table_name
AND data_type <'timestamp'

SET @sql = LEFT(@sql, LEN(@sql) - 9)

DECLARE @params NVARCHAR(100)

SET @params = N'@column_value VARCHAR(100)'

EXEC sp_executesql @sql, @params, @column_value = @column_where_v alue

END

GO

EXEC SearchMyCodes 'Code', 'A1', 'CodeTable'


Results:

column_name column_value
----------- ---------------
Code A1
Description A1 Description
Inspected Y


HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Ed Murphy]

Re: Name value pair

Chris wrote:

Not sure I understand I may end up with this:-
Code Description Inspected
Y TD001 Printer

Can you simply build and execute the following SQL statement:

select * from CodeTable where Code = 'TD001'

then create a front-end that accepts dynamic output columns and displays
it as required?

You're trying to use information_sch ema to get output with static
columns and dynamic rows. But what if the number of rows in the
original table matching your criteria is more (or less) than one?