joining tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

joining tables

Author

Topic

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 19:19:25

hi

i want to join the results of my query:

------------------------
declare @strPrefix varchar(8000)
set @strPrefix = '113,133,176' + ',000'

declare @strPercent varchar(1)
set @strPercent = '%'

declare @chrComma varchar(1)
set @chrComma = ','

declare @ctr int
set @ctr = 0

declare @lastCharIndexPlusOne int
set @lastCharIndexPlusOne = 1

declare @strSQL varchar(8000)
set @strSQL = ''

while @ctr < 3
begin
set @strSQL = @strSQL + ' left(ItemCode, 7) like substring(''' + @strPrefix + ''', cast('''+ cast(@lastCharIndexPlusOne as varchar) + ''' as int), (charindex(''' + @chrComma + ''', ''' + @strPrefix + ''', cast(''' + cast(@lastCharIndexPlusOne as varchar) + ''' as int))) - (cast(''' + cast(@lastCharIndexPlusOne as varchar) + ''' as int))) + ''' + @strPercent + ''' OR'
set @lastCharIndexPlusOne = (charindex(',', @strPrefix, @lastCharIndexPlusOne)) + 1
set @ctr = @ctr + 1
end

set @strSQL = left(@strSQL, (len(@strSQL) - 3))
set @strSQL = 'select StoreID, OrderNumber, ItemCode, AdjQty, ItemStatus from tblOrderDetails where' + @strSQL
exec(@strSQL)
----------------------

to another table? how am i going to do it?

is it possible without using a temptable? i only want to use a derived table.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-06 : 20:54:28

Yes you can use derived table


select *
from   another_table a
inner join
(
    select StoreID, OrderNumber . . .
    from   tblOrderDetails
    where  . . .
) b
on a.some_col = b.some_col

The codes in blue is your existing query.

Question : Any particular reason you are using Dynamic SQL ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-07 : 01:13:52

Dont use concatenated SQL. Use stored Procedure with parameters

Madhivanan

Failing to plan is Planning to fail

hybridGik
Starting Member

26 Posts

Posted - 2006-07-07 : 01:31:17

yes i will use the exact concatenated SQL in a stored procedure....

that's a working SQL already? but how am i going to join that to a non concatenated SQL.

like:

select *
from tbl1 a
inner join ( "concatenated SQL/dynamic SQL" ) b

that's what i need to do, but i dont know how.

thanks.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-07 : 01:42:39

Sometimes dynamic SQL is a necessary evil....

I don't think it's possible without reverting to temp tables. Unless you can built your entire statement (including the JOIN syntax) as a dynamic string, which is what khtan was probably alluding to.

It might be beneficial to post exactly what you're trying to do because it might be possible to do it differently that what you are envisaging.

HTH,

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources