substring NOT working

Question

substring NOT working

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 1

Thank you for your patience, and I'm back .....

The following example has two sp_executesql(s) which I got the result of the first one passing to the second one. First one passed but second one got stuck. Code as below

create table dbo.dynamictab(CustID varchar(20),col1 int, col2 int, col3 int, col4 int)

insert into dbo.dynamictab values('aa',10,20,30,40)

insert into dbo.dynamictab values('aa',20,30,40,50)

insert into dbo.dynamictab values('aa',30,40,50,60)

insert into dbo.dynamictab values('bb',40,50,60,70)

declare @sql_1 nvarchar(max)

declare @sql_2 nvarchar(max)

declare @col varchar(2000)

declare @col2 varchar(2000)

declare @count1 int

declare @sum2 int

set @sql_1 = ''

set @sql_2 = ''

Set @sql_1 = @sql_1 + 'select @colOUT=substring(@col,@count1*4+1,4) from dbo.dynamictab';

EXEC sp_executesql @sql_1, N'@col varchar(2000), @count1 int, @colOUT varchar(200) OUTPUT', @count1=0,@col='col1col2col3col4',@colOUT=@col2 OUTPUT;

select @col2;

--now passing @col2 to @sql_2

Set @sql_2 = @sql_2 + 'select @sumOUT=sum([@col3]) from dbo.dynamictab';

EXEC sp_executesql @sql_2, N'@col3 varchar(2000), @sumOUT int OUTPUT', @col3=@col2, @sumOUT=@sum2 OUTPUT;

select @sum2;

:angry:

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

huh?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 3

Either 'huh' or 'hug', both have the same meaning to me. I got it. Thanks to ALL of you !:Whistling:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Francis Yee-483501 (10/3/2009)
Thank you for your patience, and I'm back .....
The following example has two sp_executesql(s) which I got the result of the first one passing to the second one. First one passed but second one got stuck. Code as below
create table dbo.dynamictab(CustID varchar(20),col1 int, col2 int, col3 int, col4 int)
insert into dbo.dynamictab values('aa',10,20,30,40)
insert into dbo.dynamictab values('aa',20,30,40,50)
insert into dbo.dynamictab values('aa',30,40,50,60)
insert into dbo.dynamictab values('bb',40,50,60,70)
declare @sql_1 nvarchar(max)
declare @sql_2 nvarchar(max)
declare @col varchar(2000)
declare @col2 varchar(2000)
declare @count1 int
declare @sum2 int
set @sql_1 = ''
set @sql_2 = ''
Set @sql_1 = @sql_1 + 'select @colOUT=substring(@col,@count1*4+1,4) from dbo.dynamictab';
EXEC sp_executesql @sql_1, N'@col varchar(2000), @count1 int, @colOUT varchar(200) OUTPUT', @count1=0,@col='col1col2col3col4',@colOUT=@col2 OUTPUT;
select @col2;
--now passing @col2 to @sql_2
Set @sql_2 = @sql_2 + 'select @sumOUT=sum([@col3]) from dbo.dynamictab';
EXEC sp_executesql @sql_2, N'@col3 varchar(2000), @sumOUT int OUTPUT', @col3=@col2, @sumOUT=@sum2 OUTPUT;
select @sum2;
:angry:

So, you provided code, but what are you expecting as output?

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 5

You're back. Thank you.

create table dbo.dynamictab(CustID varchar(20),col1 int, col2 int, col3 int, col4 int)

insert into dbo.dynamictab values('aa',10,20,30,40)

insert into dbo.dynamictab values('aa',20,30,40,50)

insert into dbo.dynamictab values('aa',30,40,50,60)

insert into dbo.dynamictab values('bb',40,50,60,70)

declare @sql_1 nvarchar(max)

declare @sql_2 nvarchar(max)

declare @col varchar(2000)

declare @col2 varchar(2000)

declare @count1 int

declare @sum2 int

set @sql_1 = ''

set @sql_2 = ''

Set @sql_1 = @sql_1 + 'select @colOUT=substring(@col,@count1*4+1,4) from dbo.dynamictab';

EXEC sp_executesql @sql_1, N'@col varchar(2000), @count1 int, @colOUT varchar(200) OUTPUT', @count1=0,@col='col1col2col3col4',@colOUT=@col2 OUTPUT;

select @col2;

--now passing @col2 to @sql_2

Set @sql_2 = @sql_2 + 'select @sumOUT=sum([' + @col2 + ']) from dbo.dynamictab';

EXEC sp_executesql @sql_2, N'@col2 varchar(2000), @sumOUT int OUTPUT', @col2=@col2, @sumOUT=@sum2 OUTPUT;

select @sum2;

The above is just a testing sample to get the sum total of column 'col1' (this case when @count1=0). I could change to max\min\avg ...

Now, if you do not mind, is it possible to combine sql_1 and sql_2 together. (Is it possible to have sp_executesql ON sp_executesql ?)

Thank you. (Did you mention to provide more reading material ?)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

First, I'm trying to figure out why you are trying to nest dynamic queries. Second, if this is the type of code you have inherited, I'd be real concerned and look at how it may be rewritten as this just doesn't seem to be a very proficient means for processing data.

Yes, you may be possible to nest a dynamic query inside another dynamic query, but WHY? This isn't something I would be looking at doing.

Maybe if you explain the business case for doing it and actually show us what you are working with we could provide better answers.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Francis Yee-483501 (10/2/2009)
First, THANK YOU a lot guys for the extreme support.
The code I quoted is just one tenth of the original program.
My purpose is very straight forward and the database I involved is just for one simple reason, it is popular.
And it is NOT my original intention to "mislead" helpers to get the quick solution in terms of "FirstName", "LastName", this I have to apologize.
Any way I just want my dynamic sql query to run and the technique I could apply in order that SUBSTRING to activate when the sql is run, and now what I got is just the 'string name' (namely "FirstName").
Salute to ALL !!!

I have to admit, I have no faintest clue what you're trying to say here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 8

Guys, thank you for the efforts and I got the solution already.

Thanks again.:satisfied:

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Francis Yee-483501 (10/5/2009)
Guys, thank you for the efforts and I got the solution already.
Thanks again.:satisfied:

Cool. Can you post your solution, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)