Help with RTRIM using DYNAMIC SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with RTRIM using DYNAMIC SQL

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-11-02 : 08:54:51

I am trying to add right trims to the following dynamic sql code.
In my example below, my TEST2 column is not trimming.

Can someone please help me out with the code?

SET @mysql = '"select distinct ''TEST'', TEST_ID, '''', ' + rtrim('TEST2') + ', convert(varchar(50), TEST3) as TEST3, convert(varchar(50), TEST4) as TEST4 from MYDB..MYTABLE where TEST5 = ''AAA'' and TEST6 = ''' + rtrim(@vName) + '''"'

Thanks
Q

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-02 : 09:08:10

If TEST2 is a column in the table MYDB..MYTABLE then there is no need for this:
+ rtrim('TEST2') +
in the select list.

Just use rtrim(TEST2) the same way you did it with convert() and TEST3.

No, you're never too old to Yak'n'Roll if you're too young to die.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-11-02 : 09:22:16

Got to love easy fixes like that....!

What if I want to add a check for not null? Is there something I need to do different for the is not null?

SET @mysql = '"select distinct ''TEST'', TEST_ID, '''', ' + rtrim('TEST2') + ', convert(varchar(50), TEST3) as TEST3, convert(varchar(50), TEST4) as TEST4 from MYDB..MYTABLE where TESTXXX IS NOT NULL AND TEST5 = ''AAA'' and TEST6 = ''' + rtrim(@vName) + '''"'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-02 : 09:29:04

looks ok.
Just try it.

No, you're never too old to Yak'n'Roll if you're too young to die.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-11-03 : 11:50:53

This is driving me crazy... I can get the below code to work.
But as soon as I try to trim field City, State, Zip, etc the result set breaks. All of my columns referenced are defined as varchar in the source table.... Argh...! Any idea what I am doing wrong?

declare @mysql varchar(500)

SET @mysql = '"select distinct ''xxx'', rtrim(ID), '''', rtrim(Name), rtrim(convert(nvarchar(50), Attention)), rtrim(Address1), rtrim(Address2), City, State, Zip, Country, Phone, rtrim(Email), '''', '''', '''', '''', '''', rtrim(convert(nvarchar(50), Method)) from MYDB..MYTABLE where Company = ''ABC'' and SUB = ''' + rtrim(@vName) + '''"'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-03 : 12:23:02

the result set breaks

What do you mean?
Are there any line feeds in your result?

Then try this:
SET @mysql = '"select distinct ''xxx'', replace(rtrim(ID),char(10),''''), '''', replace(rtrim(Name),char(10),''''), replace(rtrim(convert(nvarchar(50), Attention)),char(10),''''), replace(rtrim(Address1),char(10),''''), replace(rtrim(Address2),char(10),''''), City, State, Zip, Country, Phone, replace(rtrim(Email)char(10),''''), '''', '''', '''', '''', '''', replace(rtrim(convert(nvarchar(50), Method)),char(10),'''') from MYDB..MYTABLE where Company = ''ABC'' and SUB = ''' + rtrim(@vName) + '''"'

No, you're never too old to Yak'n'Roll if you're too young to die.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-11-03 : 13:01:37

I am getting there... I am taking my dynamics SQL and passing it to the BCP command.

I think my BCP arguments is causing the problem. Specifically the use of -c.

SET @bcpCommand = 'bcp ' + @mysql + ' queryout "'
SET @bcpCommand = @bcpCommand + @tFileName + '" -T -t, -c'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources