Call Function inside a SQL Query

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

Call Function inside a SQL Query

Author

Topic

pranilrao
Starting Member

3 Posts

Posted - 2010-07-28 : 13:21:05

I have a SQL function which accepts comma seperated string and returns a table
for e.g 1,2,3 gets converted to 1
2
3

I want to use this function in a SP.
The query in the sp gets two columns and one of them is the one with comma seperated data.
e.g. select itemnumber,grades from items
Here grades are stored comma seperated in the table.

While returning this data to the UI, I want to convert the comma seperated format to table format.
so for e.g. if current data is like
ItemNumber Grades
12345 1,2,3
67890 3,4,5

I want to send to UI as
ItemNumber Grades
12345 1
12345 2
.. so on

Currently this is being done using cursors, but I want to avoid them and trying to do in a single query.
Is there any way to do this?

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 13:35:51

Does your function accepts ItemNumber to return a table of Grades against each ItemNumber?
If yes then you can do something like this


select * from yourtable
cross apply(yourfunction(itemnumber))t

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

pranilrao
Starting Member

3 Posts

Posted - 2010-07-28 : 13:54:06

the function accepts comma seperated Grades and return them in tabular format

pranilrao
Starting Member

3 Posts

Posted - 2010-07-28 : 14:00:39

cool it works!
thanks a lot for that.
this is what I did,

select ItemNumber,item from CoreItems
cross APPLY dbo.fnSplit(Grade,',')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources