July 2, 2013 at 11:03 am
Hi people,
I am creating a function to be used in a specific application that a user can insert any kind of information into a table.
This code below:
IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_HEADER')
CREATE TABLE TB_HEADER
(ID_HEADER INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
CODIGO VARCHAR(50) UNIQUE,
COL1_H VARCHAR(100),
COL2_H VARCHAR(100),
COL3_H VARCHAR(100),
COL4_H VARCHAR(100),
COL5_H VARCHAR(100),
COL6_H VARCHAR(100),
COL7_H VARCHAR(100),
COL8_H VARCHAR(100),
COL9_H VARCHAR(100),
COL10_H VARCHAR(100)
)
GO
IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_DADOS')
CREATE TABLE TB_DADOS
(ID_DADOS INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
ID_HEADER INT,
COL1_D VARCHAR(100),
COL2_D VARCHAR(100),
COL3_D VARCHAR(100),
COL4_D VARCHAR(100),
COL5_D VARCHAR(100),
COL6_D VARCHAR(100),
COL7_D VARCHAR(100),
COL8_D VARCHAR(100),
COL9_D VARCHAR(100),
COL10_D VARCHAR(100)
)
GO
INSERT INTO TB_HEADER
SELECT
CODIGO = 'CARRO_01',
COL1_H = 'MODELO_CARRO',
COL2_H = 'ANO',
COL3_H = 'DATA_COMPRA',
COL4_H = 'COR',
COL5_H = 'PLACA',
COL6_H = 'COL6',
COL7_H = 'COL7',
COL8_H = 'COL8',
COL9_H = 'COL9',
COL10_H = 'COL10'
GO
INSERT INTO TB_HEADER
SELECT
CODIGO = 'IMOVEL_01',
COL1_H = 'TIPO_DE_IMOVEL',
COL2_H = 'LOCALIZAÇÃO',
COL3_H = 'VALOR_ENTRADA',
COL4_H = 'QTD_PARCELA',
COL5_H = 'VALOR_PARCELA',
COL6_H = 'MOEDA',
COL7_H = 'COL7',
COL8_H = 'COL8',
COL9_H = 'COL9',
COL10_H = 'COL10'
GO
INSERT INTO TB_DADOS
SELECT
ID_HEADER = ID_HEADER,
COL1_D = 'PALIO',
COL2_D = '2012',
COL3_D = '05/02/2013',
COL4_D = 'VERMELHO',
COL5_D = 'XPT 1515',
COL6_D = 'COL6',
COL7_D = 'COL7',
COL8_D = 'COL8',
COL9_D = 'COL9',
COL10_D = 'COL10'
FROM TB_HEADER
WHERE CODIGO = 'CARRO_01'
GO
INSERT INTO TB_DADOS
SELECT
ID_HEADER = ID_HEADER,
COL1_D = 'CRV',
COL2_D = '2011',
COL3_D = '23/06/2012',
COL4_D = 'PRATA',
COL5_D = 'ABC 2020',
COL6_D = '',
COL7_D = '',
COL8_D = '',
COL9_D = '',
COL10_D = ''
FROM TB_HEADER
WHERE CODIGO = 'CARRO_01'
GO
INSERT INTO TB_DADOS
SELECT
ID_HEADER = ID_HEADER,
COL1_D = 'APARTAMENTO',
COL2_D = 'BARRA DA TIJUCA',
COL3_D = '200.000,00',
COL4_D = '48',
COL5_D = '8.000,00',
COL6_D = 'R$',
COL7_D = '',
COL8_D = '',
COL9_D = '',
COL10_D = ''
FROM TB_HEADER
WHERE CODIGO = 'IMOVEL_01'
GO
CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))
RETURNS TABLE
AS
RETURN
SELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H
FROM TB_HEADER
WHERE CODIGO = @codigo
UNION ALL
SELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_D
FROM TB_DADOS
INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER
WHERE CODIGO = @codigo
GO
Although big query, that is a query function creation. The problem is: How can I return first lines from "tb_Header" to be column name?, I thought in "sp_rename", but I afraid it can be slow in a big table. I'd like to emphasize that columns are always dynamic.
Part of code below
CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))
RETURNS TABLE
AS
RETURN
SELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H
FROM TB_HEADER
WHERE CODIGO = @codigo
UNION ALL
SELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_D
FROM TB_DADOS
INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER
WHERE CODIGO = @codigo
GO
grateful for your attention
July 2, 2013 at 12:07 pm
Something like this might get you started:
declare @strSQL as varchar(max)
set @strsql = (select 'select COL1_D as ' + col1_h +
', COL2_D as ' + col2_h +
', COL3_D as ' + col3_h +
', COL4_D as ' + col4_h +
', COL5_D as ' + col5_h +
', COL6_D as ' + col6_h +
', COL7_D as ' + col7_h +
', COL8_D as ' + col8_h +
', COL9_D as ' + col9_h +
', COL10_D as ' + col10_h +
' from tb_dados ' +
'inner join tb_header on tb_dados.ID_header = tb_header.ID_Header ' +
'where tb_header.codigo = ''CARRO_01''' from tb_header where codigo = 'CARRO_01'
)
exec (@strSQL)
July 2, 2013 at 1:35 pm
Thanks for respond me.
I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one
SELECT * FROM DBO.UF_DADOS ('CARRO_01')
July 2, 2013 at 2:09 pm
ulisseslourenco (7/2/2013)
Thanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one
SELECT * FROM DBO.UF_DADOS ('CARRO_01')
No in SQL a function cannot use any dynamic sql.
I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2013 at 2:55 pm
Sean Lange (7/2/2013)
ulisseslourenco (7/2/2013)
Thanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one
SELECT * FROM DBO.UF_DADOS ('CARRO_01')
No in SQL a function cannot use any dynamic sql.
I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.
The other functions work as well, the problem occurs because the programmer can insert a several of information into that tables and union statement into function could replace header table lines in a column name in function return. As I wrote in initial post I thought use sp_rename, however I afraid decreasing performance.
In relation, use other language, I don't know if it's possible, because this process will work into an application that runs sql only.
best regards
July 2, 2013 at 3:26 pm
ulisseslourenco (7/2/2013)
Sean Lange (7/2/2013)
ulisseslourenco (7/2/2013)
Thanks for respond me.I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one
SELECT * FROM DBO.UF_DADOS ('CARRO_01')
No in SQL a function cannot use any dynamic sql.
I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.
The other functions work as well, the problem occurs because the programmer can insert a several of information into that tables and union statement into function could replace header table lines in a column name in function return. As I wrote in initial post I thought use sp_rename, however I afraid decreasing performance.
In relation, use other language, I don't know if it's possible, because this process will work into an application that runs sql only.
best regards
Not really sure what you mean by the other functions work as well. You cannot execute dynamic sql in a function in sql server.
I think you missed my point about making this so generic. In a programming language you would not create a method that does everything. It seems that is what you are trying to do here. You are creating a procedure/function that can do anything with any data of any type.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2013 at 4:02 pm
Yes, you are right about dynamic queries into functions. When I wrote about "the other functions work as well" I said the other functions are inserting information into a table correctly , but I have a function that joins two tables to show a result
for example:
COL1 | COL2 |COL3 | COL4 |
CAR MODEL| Year | Purchase data | color |
VW Bettle | 2009 | 2009-01-01 | red |
Ford Ka | 2013 | 2013 -05-16 | silver |
Based on the function
CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))
RETURNS TABLE
AS
RETURN
SELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H
FROM TB_HEADER
WHERE CODIGO = @codigo
UNION ALL
SELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_D
FROM TB_DADOS
INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER
WHERE CODIGO = @codigo
I should have a result like a this:
CAR MODEL| Year | Purchase data | color |
VW Bettle | 2009 | 2009-01-01 | red |
Ford Ka | 2013 | 2013 -05-16 | silver |
Replacing Col1, Col2, Col3 and Col4 for CAR MODEL,Year,Purchase,data and color. Actually I haven't gotten yet.
Best Regards
July 8, 2013 at 7:13 am
Hi guys,
Could somebody help me? This is a bit urgent
Best regards
July 8, 2013 at 9:13 am
ulisseslourenco (7/8/2013)
Hi guys,Could somebody help me? This is a bit urgent
Best regards
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2013 at 2:13 pm
Thanks to Erin for pointing out that you had posted ddl and sample data in your first post. :blush:
Now your most recent post does not match that new sample data that you mentioned. Please help me understand what you are trying to do here.
I will go back to my original comment of why are you making everything so overly generic? You have violated so many normalization rules in your ddl I don't know where to begin. You have mixed datatypes in a single column, you have a different number of columns per row. Data integrity, validation, etc etc is impossible here. You can't do any kind of calculations or date conditions because of all the mixed data.
I will help you figure out the short term fix but honestly you need a long term solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply