function and select - 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

SQL Server Development (2000)

function and select

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-09 : 06:19:21

i have a function that i got her in the past which splits a splitted list like '5,9,15' to table with rows from the delimited string.
what i want to to is to this function, but with a select inside of it that its returned value will be passed to the function so,mething like :
select type1 from tbl1 where type1 in (dbo.splitFunction(select id from tbl22))

how can i do this?is it possible at all?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-09 : 06:34:36

This is not possible with table function.

Post your table structure, some sample data and what you want to achieve ? Maybe there is a workaround ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 06:51:13

Search for where in @MyCSV in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Also refer
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-09 : 06:57:55

this is the code :


SELECT     *
FROM Categories
WHERE     (ID IN
                          (SELECT     (SELECT     id
                                                    FROM         dbo.fnSplitter(Category)) AS xx
                            FROM          UsersCat))

the Categories contians an ID,names coliumns
and the UsersCat holds : userid,Category=list of id's like : 13,15,99
so what i want it all the name of the categories that defined for each user.
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 08:24:08

Read the links I specified
Also read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-09 : 08:26:29

I dont think you can pass the column name as one of the parameter to the table function.. if you can make use of dynamic sql then just look at this sample..


Declare @QryIn varchar(8000)
Declare @Qry varchar(8000)

Set @QryIn = '(' + char(10)
Select @QryIn = QryIn +  ID + ','   From tbl22
Set @QryIn = Left(@QryIn ,Len(@QryIn)-1) + ')'

Set @Qry = 'Select * From Categories Where ID In  ' + @QryIn

Exec(@Qry)

Hope this helps..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 08:45:44

Dynamic SQL is not needed if you follow the first link I posted

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources