Dynamic Query View?

This summary is not available. Please click here to view the post.

Dynamic Query in View

Can I use variable name in the view? because the result set is depend on
different database not the same database, so I am wondering if I can use
this as a dynanice query in the view then my problem would be solved.
Thanks in advance
Views cannot have parameters, but table-valued user-defined functions can.
They don't necessarily have the same performance as views, but this sounds
like what you want. You can do subqueries on TVF too, unlike resultsets that
are returned from stored procedures.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Rogers" <naissani@.hotmail.com> wrote in message
news:uF1y3UlLGHA.3100@.tk2msftngp13.phx.gbl...
> Can I use variable name in the view? because the result set is depend on
> different database not the same database, so I am wondering if I can use
> this as a dynanice query in the view then my problem would be solved.
> Thanks in advance
>

Dynamic query in a function

Greetings:
I am trying to write a view that includes a column derived from a function
call.
The function is written thus:
CREATE FUNCTION dbo.fnc_CapArrayValue
(@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
RETURNS REAL
AS
BEGIN
Declare @.Result REAL
SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id =
@.measurementid) and (tracking_id = @.trackingid))
RETURN @.Result
END
GO
What am I doing wrong here? When I run the view this function resides in, I
get a timeout error.Does the same query executed against the view return results in a resonable
time?
If not then posting the view definition and the definitions and indexes of
the underlying tables would give a few clues.
Is this an extract of somethimg much more complicated - if not I cannot see
much reason for using the function - unless you need it in a constraint or
similar.
Re-reading your post - is the function being used in a view definition, and
itself retriving data from a view (assuming vw_CapArrayData is a view?)
BTW you may get a deluge saying prefixing vw on the front of view names is a
bad idea, which I happen to think it is - be warned.
I suspect that the combination of a view based on a function, based on a
view has caused the optimiser to get a tad and produce a poor
execution plan.
Mike John
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:y2ikg.3677$Oh1.923@.news01.roc.ny...
> Greetings:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL
> AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id
> =
> @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
> What am I doing wrong here? When I run the view this function resides in,
> I
> get a timeout error.
>|||You cannot use Dynamic SQL inside functions. The workaround is to use a CASE
expression like:
SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
WHEN 'value_2' THEN col_2
..
WHEN 'value_n' THEN col_n
END
FROM dbo.vw_CapArrayData
WHERE measurement_id = @.m_id
AND tracking_id = @.t_id ) ;
Alternatively you can create a procedure to get this information, however
you'll have to make careful considerations for security and performance.
Anith|||Thomas Mick (nospam@.nospam.com) writes:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData
> where (measurement_id = @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
This function appears funny to me. It will return the value in @.JET if
there is a matching row, else it will return NULL. This could be expressed
more clearly.
Avoid scalar UDFs that performs data access. This can cause quite severe
performance degradation, in queries that calls the UDF, as the query
more or less gets converted to a cursor behind the scenes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I am trying to write a view that includes a column derived from a functio
n call. <<
Take a freshman course in BASIC Software Engineering. Try to be sober
on the days that that coupling and cohesion are discussed. It will
keep you from public embarassment in the future. You might also want
to Google over to Larry O'Brien's current column this month for a
reminder.
You even even put prefixes like "fnc_" on names! You never read
ISO-11179. Measurement_id? There is no name possible in a data model.
So very wrong! BIGINTs because you have more data than there are atoms
in the Universe!!
Almost everything. Forget the crashes; your whole mindset is wrong.
Post specs and we can try to get you something that is correct.
--CELKO--|||Are you always this condescending? I am relatively new to the world of
databases, not a seasoned veteran; hence the reason I am asking a question.
I appreciate what you have inputted, but not the attitude used to deliver
it.
BTW, I am in no way embarrassed as I am working on curing my ignorance and
appreciate all the help I can get.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
> Take a freshman course in BASIC Software Engineering. Try to be sober
> on the days that that coupling and cohesion are discussed. It will
> keep you from public embarassment in the future. You might also want
> to Google over to Larry O'Brien's current column this month for a
> reminder.
> You even even put prefixes like "fnc_" on names! You never read
> ISO-11179. Measurement_id? There is no name possible in a data model.
> So very wrong! BIGINTs because you have more data than there are atoms
> in the Universe!!
>
> Almost everything. Forget the crashes; your whole mindset is wrong.
> Post specs and we can try to get you something that is correct.
> --CELKO--
>|||Thank you for your response, Mike,
Allow me to better describe what I am attempting to do without divulging
confidential information.
I have two views that I am joining together with related data. On the one
side is yield data related to particular failures. On the other side is data
from a related process generated during the manufacturing processes prior to
generating the yield data; this data is related by three of their fields.
The two views link together predictably and the data returned is valid.
However, I must also get a related value from an array table to insert into
the view I am creating; hence the reason I was exploring the use of a
function.
I hope this is more understandable, if not I'm screwed; this problem has me
at my wits end right now.
Thomas Mick
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uyz7m8KkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Does the same query executed against the view return results in a
> resonable time?
> If not then posting the view definition and the definitions and indexes of
> the underlying tables would give a few clues.
> Is this an extract of somethimg much more complicated - if not I cannot
> see much reason for using the function - unless you need it in a
> constraint or similar.
> Re-reading your post - is the function being used in a view definition,
> and itself retriving data from a view (assuming vw_CapArrayData is a
> view?) BTW you may get a deluge saying prefixing vw on the front of view
> names is a bad idea, which I happen to think it is - be warned.
> I suspect that the combination of a view based on a function, based on a
> view has caused the optimiser to get a tad and produce a poor
> execution plan.
> Mike John
> "Thomas Mick" <nospam@.nospam.com> wrote in message
> news:y2ikg.3677$Oh1.923@.news01.roc.ny...
>|||Erland,
Thank you for your constructive response. I truly appreciate it and will
learn accordingly.
Thomas Mick
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E429816C9FYazorman@.127.0.0.1...
> Thomas Mick (nospam@.nospam.com) writes:
> This function appears funny to me. It will return the value in @.JET if
> there is a matching row, else it will return NULL. This could be expressed
> more clearly.
> Avoid scalar UDFs that performs data access. This can cause quite severe
> performance degradation, in queries that calls the UDF, as the query
> more or less gets converted to a cursor behind the scenes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just ignore him Thomas, most be do - he's an ignorant, condesending idiot
who has a high opinion of himself, oh and watch out for his stuck in the
80's model thinking.
Don't let this single fool put you off posting here again!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:Hdnkg.3705$Oh1.2508@.news01.roc.ny...
> Are you always this condescending? I am relatively new to the world of
> databases, not a seasoned veteran; hence the reason I am asking a
> question. I appreciate what you have inputted, but not the attitude used
> to deliver it.
> BTW, I am in no way embarrassed as I am working on curing my ignorance and
> appreciate all the help I can get.
> Thomas
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
>|||I have a feeling I already know the answer to this, but I have to ask
anyway; can a stored procedure be run from within a function? Or
alternatively, can I run a stored procedure from within a view?
You see, I need to insert this value into a column of a view I am creating
for an engineer; a stored procedure would most likely be the best method of
retrieving what I need.
Thomas Mick
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eWGUFjMkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> You cannot use Dynamic SQL inside functions. The workaround is to use a
> CASE expression like:
> SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
> WHEN 'value_2' THEN col_2
> ...
> WHEN 'value_n' THEN col_n
> END
> FROM dbo.vw_CapArrayData
> WHERE measurement_id = @.m_id
> AND tracking_id = @.t_id ) ;
> Alternatively you can create a procedure to get this information, however
> you'll have to make careful considerations for security and performance.
> --
> Anith
>

Dynamic Message Box In Report

Hello Sir,

I m working in Microsoft SQL Server Analysis Services Designer
Version 9.00.1399.00 for creating reports .

Now, I m facing a problem to view a run time dialog box at time of my report.
Means ,
In my report procedure , i have fired 2 delete queries after firing of these queries i wanna show a message box with Message "Some Records are Deleted". and one Ok Button !!

and after showing this dialog box i wanna view my report output !!

The procedure of my report is :
--
create procedure MyTemp
as

-- Query1
delete from myTempTable1 where Id = 5

-- Query2
delete from myTempTable2 where Id = 10

-- After exectuing above two queries Query1 and Query2.
-- here i want to view amessage dialog box with message "Some Records are Deleted"

select Id, name from myTempTable1,myTempTable2
--

So, how can i do this !!
I will change in my proceudre or report !!
Please suggest me about solution of this problem .

If u dont have any solution regaring this then please suggest me where i will get
the solution of this problem .

There is no messagebox in reporting services. Yout will have to give the results back as a dataset and render it in the report if your want to display it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
Jamie
Hi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie
|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pas
s
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
--
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> > Is there a method to send getdate() to a function without the function
> > returning an error: (functions is called from a view)
> > Incorrect syntax near '('
> >
> > example:
> > Select * from myfunction(getdate())
> >
> > create function myfunction (@.Today datetime)
> > returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> > Begin
> > --do stuff
> > return
> > End
> >
> >
> >
> > --
> > Regards,
> > Jamie
>
>

Dynamic Crosstab

Say I have the following output from a view on SQL Server 2000;
RegID StudID SeqNo EnrolNo Name Status
Prog AttendDate AttendCode
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 22/8/2005 O
How do I massage this using a Stored Procedure into the following output?
RegID StudID SeqNo EnrolNo Name Status
Prog 8/8/2005 15/8/2005 22/8/2005
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A X X O
Bearing in mind that the date columns are dynamic and can be anything from 0
to 18.
I have found some scripts but I couldn't get the mto work with dates :(
Thanks.See if these help.
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Mark Parter" wrote:

> Say I have the following output from a view on SQL Server 2000;
> RegID StudID SeqNo EnrolNo Name Status
Prog AttendDate AttendCode
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 22/8/2005 O
> How do I massage this using a Stored Procedure into the following output?
> RegID StudID SeqNo EnrolNo Name Status
Prog 8/8/2005 15/8/2005 22/8/200
5
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A X X O
> Bearing in mind that the date columns are dynamic and can be anything from
0
> to 18.
> I have found some scripts but I couldn't get the mto work with dates :(
> Thanks.

Dynamic create table, create index based upon a given database

Can I dynamically (from a stored procedure) generate
a create table script of all tables in a given database (with defaults etc)
a create view script of all views
a create function script of all functions
a create index script of all indexes.
(The result will be 4 scripts)

Arno de Jong,
The Netherlands.The following will create a script for each type of object:

EXEC XP_CMDSHELL '"C:\Program Files\Microsoft SQL
Server\MSSQL\Upgrade\SCPTXFR" /I /D DATABASE_NAME /S SERVER_NAME /F
C:\PATH_NAME'

--
David Portas
----
Please reply only to the newsgroup
--

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bpkpq8$7vi$1@.reader10.wxs.nl...
> Can I dynamically (from a stored procedure) generate
> a create table script of all tables in a given database (with defaults
etc)
> a create view script of all views
> a create function script of all functions
> a create index script of all indexes.
> (The result will be 4 scripts)
> Arno de Jong,
> The Netherlands.

Dyamic view/function based on table data (?)

Hey,

First, sorry if this post appear twice, because, I can not find my post
recently send, trying to post it once again.
I'm out of ideas, so, I thought, will search help here again :(

I'm trying to prepare a view for ext. app. This is in normal cases very
easy, but what if the view structure should be dynamic?!

Here is my point (I will siplify the examples).

I have a table:

create table t_data (
id bigint identity (1,1) not null,
valvarchar(10) not null,
data varchar(100) not null
constraint [PK_t_data] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )

go

insert into t_data (val, data) values
('1111111111','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('2222222222','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz12345abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('3333333333','12345abcdefghijklmnoprstuvwxyz12345 67890abcdefghijklmnoprstuvwxyz1234567890abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('4444444444','67890abcdefghijklmnoprstuvwxyz12345 67890abcdefg12345hijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')
insert into t_data (val, data) values
('5555555555','1230abcdefghijklmnoprst12345uvwxyz1 234567890abcdefghijklmnoprstuvwxyz67890abcdefghijk lmnoprstuvwxyz')
go

create table t_dataVal (
id bigint identity (1,1) not null,
valvarchar(10) not null,
fill varchar(4) not null
constraint [PK_t_dataVal] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )

go

insert into t_dataVal (val, fill) values ('1111111111','AAAA')
insert into t_dataVal (val, fill) values ('2222222222','KKKK')
insert into t_dataVal (val, fill) values ('3333333333','DDDD')
insert into t_dataVal (val, fill) values ('4444444444','ZZZZ')
insert into t_dataVal (val, fill) values ('5555555555','CCCC')
go

create table t_conf (
id bigint identity (1,1) not null,
start int not null,
length int not null,
description varchar(20) not null,
constraint [PK_t_conf] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )
go

insert into t_conf (start, length, description) values (1,10,'value_1')
insert into t_conf (start, length, description) values (11,3,'value_2')
insert into t_conf (start, length, description) values
(55,15,'value_3')
insert into t_conf (start, length, description) values (33,2,'value_4')
insert into t_conf (start, length, description) values (88,1,'value_5')
insert into t_conf (start, length, description) values (56,7,'value_6')
go

Now here is the issue:
table t_conf contain data, which can be modified by user. The user is
seting the appropriate values.
Now, there should be a view, which returns:
- as headers (collumn names) this what is defined in description column
of t_conf (for example: value_1, value_2 ... value_6)
- as values, substrings of all data from t_data, cutted with start and
length values for appropriate decription from t_conf.
- first two columns of view, should be column val and fill of t_dataVal
table

So the effect should be like this:
valfillvalue_1value_2value_3value_4value_5value6
1111111111AAAA1234567890abc...
2222222222KKKK1234567890abc...
3333333333DDDD12345abcdefgh...
4444444444ZZZZ67890abcdefgh...
5555555555CCCC1230abcdefghi...

of course, for all other value_x should be the appropriate substrings
shown.

Sounds simple, hm?
Well, I'm trying to do this, since yesterday evening, and can not :(

In real life, the call of view/function might happend a lot.
The table t_data might have around 4000 records, but the data string is
longer (around 3000 characters).

Application, might acess a udf, which returns table, and I was focusing
in that.
Was trying, to create local temp table in function, to insert values,
using cursor over t_conf.
Unfortunately, everything what I get, is just a vertical representation
of the data, and I need it horizontal :(
The other problem in function is, that I can not use exec() (wll known)
so I can not even create a table,
dynamicly, using as column names description value from table t_conf,
and as size of field length from this table.

Sorry, that the description is maybe not exactly for my problem, but
this is because I'm not even sure, which way to use :(

any help will be appreciated!

Thank You - MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Now here is the issue:
table t_conf contain data, which can be modified by user. The user is
seting the appropriate values.
Now, there should be a view, which returns:
- as headers (collumn names) this what is defined in description column
of t_conf (for example: value_1, value_2 ... value_6)
- as values, substrings of all data from t_data, cutted with start and
length values for appropriate decription from t_conf.
- first two columns of view, should be column val and fill of t_dataVal
table

A view, just like a table, has a fixed number of columns, so it can't
be dynamic. Theoretically, you could have a trigger on the configuration
table that recreates the view each time some adds or removes a value.
I'm not really sure that I like this alternative a lot.

Overall, the entire design seems unsound to me. What would the point be
to store all data in a single column, and then use another table to
specify where the value boundaries are. I could guess that this is an
import table, but even in that case I would question if the field
definition should be apply before the data makes it to SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx