Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Put first line as column name Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 11:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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
Post #1469672
Posted Tuesday, July 2, 2013 12:07 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:57 PM
Points: 546, Visits: 1,062
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)

Post #1469697
Posted Tuesday, July 2, 2013 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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')
Post #1469731
Posted Tuesday, July 2, 2013 2:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1469737
Posted Tuesday, July 2, 2013 2:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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
Post #1469757
Posted Tuesday, July 2, 2013 3:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1469776
Posted Tuesday, July 2, 2013 4:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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
Post #1469784
Posted Monday, July 8, 2013 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
Hi guys,

Could somebody help me? This is a bit urgent



Best regards
Post #1471172
Posted Monday, July 8, 2013 9:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471246
Posted Monday, July 8, 2013 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
Thanks to Erin for pointing out that you had posted ddl and sample data in your first post.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse