SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Iterating through a list of strings in a function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
47 Posts

Posted - 04/16/2014 :  12:04:49  Show Profile  Reply with Quote
I have declare a temporary table @OrgTable and I'm populating it with a single column of strings which represent organisation codes.
Prior to populating @OrgTable the codes are part of a continuous string which are delimited by a '|'.
I have written a function SplitString to do just that and the individual codes are then inserted into the table.
Finally I'm calling a second function, NBOCAP, into which I pass a start date, and end date and each organisation code from @OrgTable so I need to modify my script so it iterates through each organisation code. What form should my script take please? It needs to be backwards compatible with SQL2000.

		elem varchar(1000)

SELECT * FROM SplitString(@OrgCodeString, '|')

SELECT * FROM NBOCAP(@StartDate,@EndDate,organisation code)

In (Som, Ni, Yak)

17684 Posts

Posted - 04/16/2014 :  18:13:38  Show Profile  Reply with Quote
use the APPLY function

FROM   @OrgTable
       CROSS APPLY NBOCAP(@StartDate,@EndDate,elem)

EDIT : just saw the backward compatibly requirement. You can't use APPLY in 2000. For 2000, best is to everything in a function or to use stored procedure

Time is always against us

Edited by - khtan on 04/16/2014 18:14:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.08 seconds. Powered By: Snitz Forums 2000