Update fails via dynamic SQL & Substring

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

General SQL Server Forums

New to SQL Server Programming

Update fails via dynamic SQL & Substring

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-03 : 17:04:28

I am working to update a column using this statement:

update Test3
set InterfacesName = substring (InterfacesName, 1, len(InterfacesName)-charindex (' ', reverse(InterfacesName)))

Ex: The InterfacesName column has data like this: ABCD SQLTRUNCATE 34
After i run the above substring query, i get the output as ABCD SQLTRUNCATE

Now to do it the dynamic way, i have declared the variable,
I tried this way,

SELECT @SQL2 = 'update '
SELECT @SQL2 = @SQL2 + @TableName
select @vary2 = ' set InterfacesName ' + '=' + ' substring'
+ '(InterfacesName, 1, len(InterfacesName)' + ' -charindex'
SELECT @vary3 = '(' + ''' ''' + ',' + ' reverse(InterfacesName)))'
select @vary3 = @vary2 + @vary3
select @final2 = @SQL2 + @vary3
select @final2
--Exec ( @final2)

I can see the correct select statement.
But no data will be affected because of this. Which is not right. The data from the last space should be truncated, which is not happening.
If I run just the commands manually like the first sql as above works.

Only when i bring it into dynamic way with quotes, it does not work.
I am not sure what could the error.

Can someone help me out here.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 01:58:25

One of the variable might have less length to have data

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 02:47:45

Or maybe the column InterfacesName has trailing spaces.

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

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 08:19:20

Hi Madhi and webfred,

I variable space, I have set it to around max value 7000.
The update operation will not apply to the column specified. No error.
It says operation complete, but with no effect.

Any suggestion please.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 08:21:39

You should execute it using

EXEC(SQL)

Madhivanan

Failing to plan is Planning to fail

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 08:34:02

In the above example , select @final2 = @SQL2 + @vary3,
so should'nt i exec(@final2)?

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 08:42:39

Madhi,

I found the problem, there were 2 extra space's before the reverse..i had quotes for space, before the reverse word. I made a select on the statement and executed separately, they did not work, after removing the 2 extra spaces, it worked.

Thank you.
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-04 : 08:44:39

i dont see a reason of having @vary2 and @vary3, why cant they be in a single variable. also first test whatever update statement you're forming and then adjust dynamic sql accordingly.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 09:08:18

Hi Rohit,

I had one vary only earlier, and also i had tested the update statement prior to making it dynamic.

I made it to 2 vary thinking there might be some length restriction, as i was not seeing the last few character of the statement, hence i had to make vary 3. but now then its working, i will go back to one vary and reduce the statements.

Thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 10:11:03

It can be reduced to

SELECT @SQL2 = 'update ', @TableName='test'
SELECT @SQL2 = @SQL2 + @TableName+
' set InterfacesName ' + '=' + ' substring'
+ '(InterfacesName, 1, len(InterfacesName)' + ' -charindex'
+'(' + ''' ''' + ',' + ' reverse(InterfacesName)))'
select @sql2

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 10:35:46

alter PROCEDURE TESTING
@Tablename varchar(100)
AS
Declare @SQL2 VarChar(7000)

select @sql2=''
SELECT @SQL2 = 'select t1.Column1,t2.Column2 from DB1.Table1 as t1 join DB2.'+ @Tablename+
' as t2 on t1.Column1 = t2.Column2'
EXEC(@SQL2)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 10:37:32

Did you delete any post?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 10:38:49

I think he did.
I was on my way to answer and made a short refresh and it was away.

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

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 10:40:11

I deleted my last post, since i almost after 2mins of posting, i found the solution. sorry about that.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 10:45:15

Sorry about that webfred. Since I found a solution almost immediately after i posted, i felt i should not disturb you guys on this, and removed it immediately.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-04 : 10:59:44

quote:
Originally posted by webfred

I think he she did.

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 11:17:58

Yes

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-04 : 11:58:12

Hi,

I was trying to produce the result of a SP to a text file.
This SP has input parameters.

this is the code i added to the end of the SP for the text file output.

select @SQL2 = 'bcp '
select @SQL2 = @SQL2 + @Tablename
select @SQL2 = @SQL2 + '_Final out "C:\UtilizationData\UtilData.txt" -c -t -SLOCALHOST'
--SELECT @SQL2
EXEC master.sys..xp_cmdshell @SQL2

Error:Could not find server 'master' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Please advise.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 12:01:39

EXEC master..xp_cmdshell @SQL2

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 12:03:23