SQL Server Procedure/Function param issue

• sikka.sourav

  SSC Rookie

  Points: 39

  More actions

  August 22, 2018 at 9:36 am

  #415496

  I am new to SQL Server Stored Procedure/Functions. need Suggestion for Creating below layout.

  Input Param: EmployeeId, Add1, Add2 , A1 (This will I get From Table)

  FileType=A1 (If Filetype=A1 then Create Data in below Format & put values in their respective positions.)

  I have created below Fixed length code 
  SELECT '637'
+ 'EMPLOYEEID' + + SPACE(9- LEN(RTRIM(LTRIM(ISNULL('EMPLOYEEID','')))))
+ '98'
+ CONVERT(VARCHAR(6), GETDATE(), 12)
+ SPACE(9)
+ 'A1'
+ ISNULL(LEFT('HomeAddr1',30),'') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT('HomeAddr1',30),'')))))
+ ISNULL(LEFT('HomeAddr2',30),'') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT('HomeAddr1',30),'')))))
+ SPACE(1)
+ SPACE(8) AS Data

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  August 22, 2018 at 9:45 am

  #2002856

  I wouldn't do this in T-SQL.  It looks like you're preparing a fixed width file, in which case you should use bcp.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sikka.sourav

  SSC Rookie

  Points: 39

  More actions

  August 22, 2018 at 9:51 am

  #2002860

  I don't have access to BCP. Can we do it using Procedure ??

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738327

  More actions

  August 22, 2018 at 10:00 am

  #2002863

  I'm not quite sure what you are trying to accomplish here. I assume this is some formatting of the data all on one line. Is that correct?

  Can you show a sample with a few rules of what you're trying to achieve?

• Thom A

  SSC Guru

  Points: 99143

  More actions

  August 22, 2018 at 10:02 am

  #2002865

  sikka.sourav - Wednesday, August 22, 2018 9:51 AM

  I don't have access to BCP. Can we do it using Procedure ??

  SSIS then? Otherwise, what ETL tool are you using/have access to? Drew is right, doing this within T-SQL is the the wrong choice, you should be doing to format definition in your ETL tool (whatever that is).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk