IN Operator Using Table Function Not Working

Question

IN Operator Using Table Function Not Working

Tim ffitch

SSCommitted

Points: 1637
More actions
February 11, 2008 at 10:07 am

#378442

Hi.
We have a Select statement in a stored procedure that uses the IN Operator.
When we use the following style it works:
WHERE IN ('Value1', 'Value2', 'Value3')
However we have a pipe delimited string that is being turned into a single field table via a UDF and returned as a table and instead of a list of values as above, we have a Select statement as follows:
WHERE IN (SELECT MyField FROM MyFunction)
Everything works locally and we can see data, however when we use the latter method ADO is returning a closed recordset, what gives? I know stored procedures will not return memory defined tables but this is from a function.
What are we doing wrong.
I have used Functions to return tables which would fail if it were a stored procedure many times before, so I don't understand why this won't work.
Thanks
Tim.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Tim ffitch (2/11/2008)
Hi.
We have a Select statement in a stored procedure that uses the IN Operator.
When we use the following style it works:
WHERE IN ('Value1', 'Value2', 'Value3')
However we have a pipe delimited string that is being turned into a single field table via a UDF and returned as a table and instead of a list of values as above, we have a Select statement as follows:
WHERE IN (SELECT MyField FROM MyFunction)
Everything works locally and we can see data, however when we use the latter method ADO is returning a closed recordset, what gives? I know stored procedures will not return memory defined tables but this is from a function.
What are we doing wrong.
I have used Functions to return tables which would fail if it were a stored procedure many times before, so I don't understand why this won't work.
Thanks
Tim.

theory wiae, there's no problem using a function to return the table; the script contributions here on SSC has the famous SPLIT function, and I've used it plenty of times, calling it from ADO:

sql="SELECT * FROM SOMETABLE WHERE SOMEFIELD IN(SELECT strval FROM dbo.Split('Value1', 'Value2', 'Value3') )"

set rs = Conn.Execute(sql)

i think you'll need to post the specific sql command that is returning a closed recordset. It's going to be specific to the implementation...if the sql command returned no records, the recordset would still be open, and just be rs.EOF.

if you are using the recordset.OPEN command, did you set rs=New ADODB.Recordset beforte trying to .OPEN it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tim ffitch SSCommitted Points: 1637 More actions · Answer 2

Lowell (2/11/2008)
theory wiae, there's no problem using a function to return the table; the script contributions here on SSC has the famous SPLIT function, and I've used it plenty of times, calling it from ADO:
sql="SELECT * FROM SOMETABLE WHERE SOMEFIELD IN(SELECT strval FROM dbo.Split('Value1', 'Value2', 'Value3') )"
set rs = Conn.Execute(sql)
i think you'll need to post the specific sql command that is returning a closed recordset. It's going to be specific to the implementation...if the sql command returned no records, the recordset would still be open, and just be rs.EOF.
if you are using the recordset.OPEN command, did you set rs=New ADODB.Recordset beforte trying to .OPEN it?

The function is the "SPLIT" function just under another name. The ADO side of things is fine as it works if we use hard coded values in the SQL of the stored procedure.

Jonnie Pettersson SSC Veteran Points: 257 More actions · Answer 3

Insert...

SET NOCOUNT ON

after the AS clause in your stored procedure (in the beginning)...

A recordset can contain meny recordsets...and your subquery will be the first recordset and the data you want is in the next recordset...but if you insert the code above...the subquery will not produce a recordset and your data will be in the "first" recordset

make sense?

Tim ffitch SSCommitted Points: 1637 More actions · Answer 4

Jonnie Pettersson (2/13/2008)
Insert...
SET NOCOUNT ON
after the AS clause in your stored procedure (in the beginning)...
A recordset can contain meny recordsets...and your subquery will be the first recordset and the data you want is in the next recordset...but if you insert the code above...the subquery will not produce a recordset and your data will be in the "first" recordset
make sense?

It works, give that man a cigar!

I understand about the many recorsets, what I don't understand is why SET NOCOUNT would affect the result. All this does is stop the reporting of the number of records affected!

Is there any where I can read more about this to help me understand? BOL is not much help.

Thanks

Tim.

Jonnie Pettersson SSC Veteran Points: 257 More actions · Answer 5

Not much info but... http://support.microsoft.com/default.aspx/kb/235340

Quoted from Microsoft

"The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset."

Every "result" becomes a recordset in your RS and NOCOUNT turns that behaviour off.

Tim ffitch SSCommitted Points: 1637 More actions · Answer 6

Jonnie Pettersson (2/13/2008)
Not much info but... http://support.microsoft.com/default.aspx/kb/235340
Quoted from Microsoft
"The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset."
Every "result" becomes a recordset in your RS and NOCOUNT turns that behaviour off.

I get the idea, I was thinking long these lines from your earlier reply.

Thank you for your help.

Tim