No Result Set - 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

Transact-SQL (2000)

No Result Set

Author

Topic

atatham
Starting Member

4 Posts

Posted - 2003-06-27 : 09:49:19

Hi Guys & Girls - unlikely

Ok I'm trying to use a search example that I find on this site a few weeks ago. It seems simple enough but I'm having problems returning the resulting set.

When executed from the Query Analyser it runs ok and I get a nice list of IDs but when opened from an ADO recordset object e.g. ASP or VB it does not return a set.

In access, just used for testing of course, it gives me a 'The SP execute but did not return any records' dialog.

Now I just don't get this. Can you people help? Thanks.

ALTER PROCEDURE spSearchJobsForPhrase
(
@sPhraseArray varchar(100)
)
AS
DECLARE @separator_position int -- Locates each separator character
DECLARE @array_value varchar(1000) -- Holds each returned array value
DECLARE @like_text varchar (1000) -- Stores each phrase as they are inspected
DECLARE @separator varchar (1)

CREATE TABLE #SearchResults
(ItemID uniqueidentifier)

SET @separator = ','
SET @sPhraseArray = @sPhraseArray + @separator

WHILE patindex('%' + @separator + '%' , @sPhraseArray) <> 0
BEGIN -- patindex matches the a pattern against a string
SET @separator_position = patindex('%' + @separator + '%' , @sPhraseArray)
SET @array_value = left(@sPhraseArray, @separator_position - 1)
SET @like_text = '%' + @array_value + '%'

EXEC spSearchJobsInsert @like_text

INSERT INTO #SearchResults
SELECT [Job ID] FROM tblJobs WHERE [Job Number] like @like_text

SET @sPhraseArray = stuff(@sPhraseArray, 1, @separator_position, '')
END

SELECT * FROM #SearchResults
GO

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-27 : 10:00:49

quote:



ALTER       PROCEDURE spSearchJobsForPhrase
(
		@sPhraseArray varchar(100)
)
AS

SET NOCOUNT ON

DECLARE @separator_position int -- Locates each separator character
DECLARE @array_value varchar(1000) -- Holds each returned array value
DECLARE @like_text varchar (1000) -- Stores each phrase as they are inspected
DECLARE @separator varchar (1)

CREATE TABLE #SearchResults
(ItemID uniqueidentifier)

SET @separator = ','
SET @sPhraseArray = @sPhraseArray + @separator

WHILE patindex('%' + @separator + '%' , @sPhraseArray) <> 0
BEGIN -- patindex matches the a pattern against a string 
	SET @separator_position = patindex('%' + @separator + '%' , @sPhraseArray)
	SET @array_value = left(@sPhraseArray, @separator_position - 1)
	SET @like_text = '%' + @array_value + '%'

	EXEC spSearchJobsInsert @like_text

	INSERT INTO #SearchResults
	SELECT [Job ID] FROM tblJobs WHERE [Job Number] like @like_text

	SET @sPhraseArray = stuff(@sPhraseArray, 1, @separator_position, '')
END

SET NOCOUNT OFF

SELECT * FROM #SearchResults
GO

Also, you might like to change the select * to select col1, col2 etc..

-------
Moo.

Edited by - mr_mist on 06/27/2003 10:01:31

atatham
Starting Member

4 Posts

Posted - 2003-06-27 : 10:08:48

That makes no difference you still don't get a recordset returned.

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-27 : 10:14:07

quote:

That makes no difference you still don't get a recordset returned.

The SET NOCOUNT ON makes no difference?

That does surprise me.

I could test it but without data I would find it tricky.

hmm.

-------
Moo.

atatham
Starting Member

4 Posts

Posted - 2003-06-27 : 10:34:04

Thanks mr_mist

You where on the right track but it appears you must be particular about where the SET NOCOUNT is located. If it is outside the WHERE loop it doesn't work. Many thanks, I looked NOCOUNT up and I still don't understand. Can anyone explain exactly what this is doing?

See below:

ALTER PROCEDURE spSearchJobsForPhrase
(
@sPhraseArray varchar(100)
)
AS

DECLARE @separator_position int -- Locates each separator character
DECLARE @array_value varchar(1000) -- Holds each returned array value
DECLARE @like_text varchar (1000) -- Stores each phrase as they are inspected
DECLARE @separator varchar (1)

SET @separator = ','
SET @sPhraseArray = @sPhraseArray + @separator

CREATE TABLE #SearchResults
(ItemID uniqueidentifier)

WHILE patindex('%' + @separator + '%' , @sPhraseArray) <> 0
BEGIN -- patindex matches the a pattern against a string
SET @separator_position = patindex('%' + @separator + '%' , @sPhraseArray)
SET @array_value = left(@sPhraseArray, @separator_position - 1)
SET @like_text = '%' + @array_value + '%'

SET NOCOUNT ON

INSERT INTO #SearchResults
SELECT [Job ID] FROM tblJobs WHERE [Job Number] like @like_text

SET NOCOUNT OFF

SET @sPhraseArray = stuff(@sPhraseArray, 1, @separator_position, '')
END

SELECT [ItemID] FROM #SearchResults

DROP TABLE #SearchResults

Many Moo's

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-27 : 10:58:39

Yes. As I understand it, every time the INSERT is executed, a message is generated, which from the ASP side looks like an empty record set. You *could* leave out the set nocounts if you knew how many record sets you had to ignore before getting to the results.

The set nocount on means that the messages aren't generated, so the first resultset output is the one you actually want.

-------
Moo.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-06-27 : 11:03:36

NOCOUNT basically controls the display/output of "xx records found" messages....which if present can screw up stored procedures.

In general i would have SET NOCOUNT ON as the VERY 1st line in the code and SET NO COUNT OFF as the VERY last line of the code...there's no advantage setting it on/off within a loop.

Also in this case you drop the #SearchResults table. As it is a temporary table, it will be automatically dropped at the end of the procedure....it's overkill to drop it using code.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-27 : 11:11:00