1

Topic: Dynamic request in a table valued function

Hello!
SQL Server 2008R2
Compatibility level 100.
[spoiler There is a modeling DB of the following structure]

CREATE TABLE dbo. SourceTable
(
REC_ID varbinary (8) NOT NULL
,sFIELD varchar (10) NULL
,iFIELD int
,vFIELD varbinary (8)
,CONSTRAINT PK_SourceTable_REC_ID PRIMARY KEY CLUSTERED (REC_ID)
);
CREATE TABLE dbo. ScriptTable
(
REC_ID varbinary (8) NOT NULL
,sQUERY varchar (1000) NULL
,iFIELD int
,CONSTRAINT PK_ScriptTable_REC_ID PRIMARY KEY CLUSTERED (REC_ID)
);
CREATE TABLE dbo. DataTableOne
(
REC_ID varbinary (8) NOT NULL
,sSomeDataName varchar (10) NULL
,iSomeDataCode int
,CONSTRAINT PK_DataTableOne_REC_ID PRIMARY KEY CLUSTERED (REC_ID)
);
CREATE TABLE dbo. DataTableTwo
(
REC_ID varbinary (8) NOT NULL
,sVALUE varchar (100) NULL
,iCODE int
,CONSTRAINT PK_DataTableTwo_REC_ID PRIMARY KEY CLUSTERED (REC_ID)
);

[/spoiler]
Where:
SourceTable - the table with a specification;
ScriptTable - the table with texts of requests;
DataTableOne and DataTableTwo - reference manuals.
[spoiler Filling by given]

INSERT INTO SOURCETABLE (REC_ID, sFIELD, iFIELD, vFIELD)
VALUES
(0x800000000001, ' Znachenie1 ', 1,0x800000000001)
,(0x800000000002, ' Znachenie2 ', 1,0x800000000001)
,(0x800000000003, ' Znachenie3 ', 2,0x800000000001)
,(0x800000000004, ' Znachenie4 ', 1,0x800000000002)
,(0x800000000005, ' Znachenie5 ', 2,0x800000000002)
,(0x800000000006, ' Znachenie6 ', 1,0x800000000003)
,(0x800000000007, ' Znachenie7 ', 2,0x800000000002)
,(0x800000000008, ' Znachenie8 ', 1,0x800000000004)
,(0x800000000009, ' Znachenie9 ', 2,0x800000000003)
,(0x800000000010, ' Znachenie10 ', 1,0x800000000002);
INSERT INTO SCRIPTTABLE (REC_ID, sQUERY, iFIELD)
VALUES
(0x800000000001, ' SELECT @VALUE = COALESCE (iSomeDataCode, ") FROM DataTableOne WHERE REC_ID = ', 1)
,(0x800000000002, ' SELECT @VALUE = COALESCE (sVALUE, ") FROM DataTableTwo WHERE REC_ID = ', 2);
INSERT INTO DataTableOne (REC_ID, iSomeDataCode, sSomeDataName)
VALUES
(0x800000000001,1, ' Forks ')
,(0x800000000002,2, ' Knifes ')
,(0x800000000003,3, ' Spoons ')
,(0x800000000004,4, ' Sticks ')
,(0x800000000005,5, ' Kopalki ')
,(0x800000000006,6, ' Flashers ')
,(0x800000000007,7, ' ItemNejm1 ')
,(0x800000000008,8, ' ItemNejm2 ')
,(0x800000000009,9, ' ItemNejm3 ');
INSERT INTO DataTableTwo (REC_ID, sVALUE, iCODE)
VALUES
(0x800000000001, ' 001= ', 1)
,(0x800000000002, ' 002= ', 2)
,(0x800000000003, ' 003= ', 3)
,(0x800000000004, ' 004= ', 4)
,(0x800000000005, ' 005= ', 5)
,(0x800000000006, ' 006= ', 6)
,(0x800000000007, ' 007= ', 7);

[/spoiler]
Logic of interaction:
The field iFIELD informs tables SourceTable on the table in which the data under link SourceTable.vFIELD = (DataTableOne) DataTableTwo is stored. REC_ID.
Field SCRIPTTABLE.iFIELD of the table corresponds to field SourceTable.iFIELD.
Table SCRIPTTABLE contains the text of request which returns necessary value from DataTableOne or DataTableTwo.
[spoiler There is a table valued function]

CREATE FUNCTION dbo. GetData (@TBLCODE INT, @REC_ID VARBINARY (8))
RETURNS @retTABLE TABLE
(
RETVAL VARCHAR NULL
)
AS
BEGIN
DECLARE
@QUERY NVARCHAR (1000);
@PARAM NVARCHAR (100);
@VALUE VARCHAR (1000);
SET @PARAM = ' @VALUE VARCHAR (100) OUTPUT ';
SELECT @QUERY = COALESCE (sQUERY+CONVERT (VARCHAR (MAX) ,@PARAM,1), ") FROM SCRIPTTABLE WHERE iFIELD = @TBLCODE;
IF @QUERY <>"
BEGIN
EXECUTE sp_executesql @QUERY, @PARAM, @VALUE OUTPUT;
END
INSERT INTO @retTABLE (RETVAL) VALUES (@VALUE)
RETURN
END;

[/spoiler]
Usage in a type is meant:

SELECT
SOURCETABLE.sFIELD
,DICT_DATA.RETVAL
FROM
SOURCETABLE OUTER APPLY
dbo. GetData (SOURCETABLE.iFIELD, SOURCETABLE.vFIELD)) AS DICT_DATA;

At present it at all does not work, for in a table valued function it is impossible to launch stored procedure.
I ask to prompt as it is possible to get out of the given situation.

2

Re: Dynamic request in a table valued function

To get out simply. It is necessary to forget about universal functions with dynamics. Well or to replace a DBMS.

Oomel wrote:

Table SCRIPTTABLE contains the text of request which returns necessary value from DataTableOne or DataTableTwo.

and, yes, for text saving  on server side invented different objects - representations, procedures, functions. In the table to push the text of requests it is not necessary.

3

Re: Dynamic request in a table valued function

Sergey Alekseevich wrote:

And, yes, for text saving  on server side invented different objects - representations, procedures, functions. In the table to push the text of requests it is not necessary.

It is finite yes, but such approach is inconvenient in cases when in a DB> 300 objects for which it will be necessary to describe representations or function.
The essence of usage of dynamics just consists what to add in the table a new line or to change existing much easier, than to change object of a DB.
Plus the code from such heap, suppose, representations swells hardly more than actually it would be desirable.
Without necessity of such unsafe, plus to everything, the method would not be selected.
Therefore the question remains actual: whether there is a possibility dynamic to select the table at request performance, being based on field value (SourceTable.iFIELD) in this case.
It is desirable without using the cursor . in  at the table, whose simplified variant is SourceTable, billions records.

4

Re: Dynamic request in a table valued function

Oomel wrote:

It is finite yes, but such approach is inconvenient in cases when in a DB> 300 objects for which it will be necessary to describe representations or function.

And for 299 objects of type it would be easier?

Oomel wrote:

the Essence of usage of dynamics just consists what to add in the table a new line or to change existing much easier, than to change object of a DB.

is easier"? It is not necessary to do knee-bend and""to speak before object change? Or in what simplicity?

Oomel wrote:

Plus the code from such heap, suppose, representations swells hardly more than actually it would be desirable.

Announce admissible for you "plumpness" of the code then.

Oomel wrote:

Without necessity of such unsafe, plus to everything, the method would not be selected.

the Galaxy ?... But. In what danger?

Oomel wrote:

Therefore the question remains actual: whether there is a possibility dynamic to select the table at request performance, being based on field value (SourceTable.iFIELD) in this case.

15 years as in FAQ the method [/url] is described [url=http://www.sql.ru/faq/faq_topic.aspx?fid=104].

Oomel wrote:

It is desirable without using the cursor . in  at the table, whose simplified variant is SourceTable, billions records.

Then at you the architect should be, why you to it do not set all these questions?

5

Re: Dynamic request in a table valued function

On points:
1. Not easier. Complexity consists in possible necessity to finish such objects in case of change of structure of a DB.
All DB is a part of a certain product which is developed not by us. Any interference in DB structure can complicate the further attending very strongly.
2. So it turned out that at present only I have some skill for development on SQL.
If I too complicate current finishing there can be problems on its attending at other experts.
3. Since in the core the code is developed not on SQL it would be desirable to be restricted to small constructions within 500 lines.
4. To the given table users have access. There it is possible and drop table to write.
5. Actually, sp_executesql also it would be desirable to use. As to use it in leaking SELECT I do not know, and in a table valued function it does not work.
6. The architect is rather large developer who will not change structure of a DB of the product for one company.

6

Re: Dynamic request in a table valued function

Oomel wrote:

the Essence of usage of dynamics just consists what to add in the table a new line or to change existing much easier, than to change object of a DB.

Oomel wrote:

It is finite yes, but such approach is inconvenient in cases when in a DB> 300 objects for which it will be necessary to describe representations or function.
The essence of usage of dynamics just consists what to add in the table a new line or to change existing much easier, than to change object of a DB.
Plus the code from such heap, suppose, representations swells hardly more than actually it would be desirable.
Without necessity of such unsafe, plus to everything, the method would not be selected.

This all personal predilections.
Objectively to add in the table a new line or to create  there is a performance of one command T-SQL
The heap of representations is one record in the table on representation, is equal as record in your table SCRIPTTABLE. It is is Only better made. It swells in the same way as your table SCRIPTTABLE, is no more.
Oh, yes, at you 300 !!! !!! In typical trading system 10  objects, and it does operation with it programmers idle time and . Think, if typical system to thrust in one  all will be in ? There are no tables there are no glitches?
Understand, key here that clever people already wrote here is all - SCRIPTTABLE, QUERY, iFIELD, TBLCODE, this all only is called a little on another.
They carefully thought over the data model, all it is debugged and checked by decades of maintenance, the fast reliable engine for handling (Database Engine) is made, and even there is a special language (T-SQL) that it would be possible to do all not only  in interfaces, but also normal programming, with usage sors-kontrolov and sharing of operations. Did all these are best of the best, thousand experts of ten years.
And you want to make better, and forces of five (yes though 50) young students (yes let you even enticed 50 best  from a microsoftware and )? Besides for creation of one trading system (it is good, for Google changeover)?
Be not engaged in nonsenses, use that is.

7

Re: Dynamic request in a table valued function

Oomel wrote:

On points:
1. Not easier. Complexity consists in possible necessity to finish such objects in case of change of structure of a DB.
All DB is a part of a certain product which is developed not by us. Any interference in DB structure can complicate the further attending very strongly.
2. So it turned out that at present only I have some skill for development on SQL.
If I too complicate current finishing there can be problems on its attending at other experts.
3. Since in the core the code is developed not on SQL it would be desirable to be restricted to small constructions within 500 lines.
4. To the given table users have access. There it is possible and drop table to write.
5. Actually, sp_executesql also it would be desirable to use. As to use it in leaking SELECT I do not know, and in a table valued function it does not work.
6. The architect is rather large developer who will not change structure of a DB of the product for one company.

1. Make separate basis for your superstructure over another's product.
Objects from another's product register synonyms.
2. It  to yours SCRIPTTABLE will be written by requests? Then what difference? Or they a bear will outline requests in somebody your application? And  requests already therefrom, save as .
3. . "The small code" in 500 lines :-) It is necessary for you good SQL the programmer that would write the code of no more than 50 lines :-)
4. Item 2 see
5. It it is impossible to do. More precisely, it is possible to write extended or CLR function.

8

Re: Dynamic request in a table valued function

Oomel wrote:

Actually, sp_executesql also it would be desirable to use. As to use it in leaking SELECT I do not know, and in a table valued function it does not work

In any way. Study  at first, and then generate ideas.

9

Re: Dynamic request in a table valued function

wrote:

[For example cause procedure with parameter "a temporary table name", on an output receive the temporary table with the necessary data.

From this place - more slowly!
I write down!
.
. Well to create #t and to fill it in procedure is I be able.
But  to create #t and even ##t in procedure and to return is a current  80-level on forces.

10

Re: Dynamic request in a table valued function

aleks222;
Yes, the visibility area is accessible only to the master smile

11

Re: Dynamic request in a table valued function

wrote:

Table SCRIPTTABLE contains the text of request which returns necessary value from DataTableOne or DataTableTwo.

The brother, exhale! Or   phone of the dealer!
If it is serious, "representation" and is "the request text" which "to be stored in the table". Only kind people from  already for you made all . You want with parameters? Not a question, write  functions. These are same "representations" a type with a side.
lines to fence the  when all is already made for you 9 years ago?

12

Re: Dynamic request in a table valued function

Cammomile wrote:

all is made for you 9 years ago?

And what is made 9 years ago?

13

Re: Dynamic request in a table valued function

aleks222 wrote:

But  to create #t and even ##t in procedure and to return is a current  80-level on forces.

I probably did not understand a message. But after all same it is trivial enough, for ## it is exact. If it at us as an input parameter for procedure us, by and large, and to return nothing it is necessary. We already have this data...

14

Re: Dynamic request in a table valued function

iap,  in 2008 server added  functions which we can use as  representations, and they just and are "the request text" which wants to store .

15

Re: Dynamic request in a table valued function

Cammomile wrote:

iap,  in 2008 server added  functions which we can use as  representations, and they just and are "the request text" which wants to store .

TO informs: inline-functions added  in MS SQL 2000.

16

Re: Dynamic request in a table valued function

Well and it is nice. Alex, answer my question above.

17

Re: Dynamic request in a table valued function

Oomel;
Not so. Store requests about the server, the client should request. I.e. the technological interlayer of type of an application server is necessary to you. On SQL it is not necessary to fence it. In construction and performance of requests the interlayer let is engaged.

18

Re: Dynamic request in a table valued function

Cammomile;
we ask,  in last post.

CREATE TABLE dbo. Queries (ID int, Qtext nvarchar (max))
INSERT INTO dbo. Queries SELECT 1, ' SELECT [Text] = "Hello SQL.ru!" FOR XML PATH ("Data")'
INSERT INTO dbo. Queries SELECT 2, ' SELECT TOP 5 object_id, name FROM sys.all_columns FOR XML PATH ("Data")'
GO
CREATE PROC dbo. Proc_1
@TempTableName nvarchar (600)
, @QueryID int
AS
DECLARE @Query nvarchar (max) = (SELECT Qtext FROM dbo. Queries WHERE ID = @QueryID)
DECLARE @SQL nvarchar (max)
SET @SQL = ' CREATE TABLE ' + @TempTableName + ' (TabelData xml)
DECLARE @XML XML = (' + @Query + ') INSERT INTO ' + @TempTableName + ' SELECT @XML'
EXECUTE (@SQL)
GO
CREATE PROC dbo. Proc_2
@TempTableName nvarchar (600)
AS
DECLARE @SQL nvarchar (max) = ' SELECT * FROM ' + @TempTableName
EXEC (@SQL)
SET @SQL = ' DROP TABLE ' + @TempTableName
EXEC (@SQL)
GO
EXEC dbo. Proc_1 ' ##SomeGlobalTable ', 1
EXEC dbo. Proc_2 ' ##SomeGlobalTable'
EXEC dbo. Proc_1 ' ##SomeGlobalTable ', 2
EXEC dbo. Proc_2 ' ##SomeGlobalTable'
GO
DROP PROC dbo. Proc_1
DROP PROC dbo. Proc_2
DROP TABLE dbo. Queries

19

Re: Dynamic request in a table valued function

;
The main thing is casual  not to launch?

20

Re: Dynamic request in a table valued function

, finish phrase-mongering. You spoke that it is impossible, to you showed that is possible. If you meant not it, but something another, uncover the thought.
To you resulted the working proof , and you move down on a particular.

21

Re: Dynamic request in a table valued function

*  manoeuvres begin that they meant "date transmission between sessions"...

22

Re: Dynamic request in a table valued function

Lamb, and that this decision? How to make  through a campaign to the proctologist in strict sequence? ....

23

Re: Dynamic request in a table valued function

aleks222 wrote:

But  to create #t and even ##t in procedure and to return is a current  80-level on forces.

TaPaK wrote:

aleks222, yes, the visibility area is accessible only to the master smile

Alex states what to create ## in procedure and  upward it is very abrupt skill.
You agree with it.
To you set an example that it is skill of level "read about types of temporary tables".
You include any not clear manoeuvres. At you on business is what to tell?

24

Re: Dynamic request in a table valued function

wrote:

it is passed...
Just about, and I speak - any chemistry
[spoiler  that did not speak, and so it works everywhere]

create procedure dbo.test (@name varchar (50))
as
declare @s nvarchar (max)
set @s ='select top 10 * into ' + @name + ' from businessresult'
exec sp_executesql @s
go
exec dbo.test ' ##trampampa'
select * from ##trampampa
drop table ##trampampa
drop procedure dbo.test

[/spoiler]

Look , and truth works!
It will be necessary to remember such possibility...

25

Re: Dynamic request in a table valued function

Cammomile wrote:

it is passed...
it is passed...
Alex states what to create ## in procedure and  upward it is very abrupt skill.
You agree with it.
To you set an example that it is skill of level "read about types of temporary tables".
You include any not clear manoeuvres. At you on business is what to tell?

On business: and why a lamb not a statics created? If  it is free, what for grids ?