is it possible to open a recordset and loop through the records in a scalar valued function?

[RayMilhon]

 I think I did this before but it's been a few years and I don't remember how I did it.  I'm having trouble finding it in the documentation as well.  Can this be done in SQL Server 2012?  I have a report I have to produce which was being done in Access however due to new security protocols I can no longer do this in Access and I need to create a function that has 2 parameters.  a patient ID and an admit date.  The function then checks all patients hospital stays and returns true if the admit date for their admission is within 2 days of them being discharged from another admission.  Can someone point me in the right direction?

[#2 · Accepted solution]

Hi @RayMilhon 

> is it possible to open a recordset and loop through the records in a scalar valued function?

Yes, but using SQL Server, in most cases, this is a very bad idea!

You can use CURSOR for the task.

check the following docs:

https://learn.microsoft.com/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-...

Basically you declare a cursor using the query that returns the set of rows -> then you can open the cursor and move row by row using FETCH NEXT

> Can this be done in SQL Server 2012? 

yes. Cursor is supported in 2012.

Here is a post from 2012: https://www.c-sharpcorner.com/UploadFile/898089/how-to-fetch-record-using-cursor-in-sql-server-2012/

>  I have a report I have to produce which was being done in Access however due to new security protocols I can no longer do this in Access and

This doesn't sound in first glance as something you should do in SQL Server but in the client side. SQL Server is not best solution for working row by row as it was design to work on SET of rows. All the algorithm it based on are for improving performance when working with set and not looping.

> I need to create a function that has 2 parameters.  a patient ID and an admit date.  The function then checks all patients hospital stays and returns true if the admit date for their admission is within 2 days of them being discharged from another admission.

In first glance this seems like something you can so on all rows in one time without any loop using one query.

For more information we will need to reproduce the scenario so we will have something to work with. If the above did not cover your needs then please provide:

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).

[#3]

@RayMilhon 

Hi, Ray.

The question itself is somewhat flawed in an SQL Server context since a scalar function doesn't return rows - that's the remit of table-valued functions.

Scalar functions are typically used within other artefacts such as views, stored procedures, or even table-valued functions.

Setting functions aside for the moment, taking a cursor approach isn't necessary and is wildly inefficient - as noted by @Ronen_Ariely. And from what you've outlined in your scenario, I don't see any requirement for taking that approach - unless the client application imposes some requirement to do so.

There's a number of alternative approaches that would be more efficient including but not limited to:

• Leveraging a view that uses self-joining;
• Leveraging a view that uses a subquery (this might be the easier approach if you need to handle multiple rows for a given query - i.e. multiple discharges within the previous two days criteria.)

You might subsequently use these approaches inside a function or stored procedure definition (likely, given you're looking to use variables), but these views are what would be doing the heavy lifting.

It would be useful to know what T-SQL query your client application issues, and what result(s) is expects back, i.e. simply the TRUE/FALSE value from the function; the actual rows where a previous discharge is within two days of the current admission; perhaps even a query that produces the full report's dataset, etc.

Don't use cursors just because that was how it was done in Access. Take the opportunity to leverage better constructs from SQL Server if your client application lets you.

Cheers,

Lain