sp won't return records after cursor manip

[evilmousse]

i need to do:
<query with insert and sumvals>
<manipulate cursor>
<return records from another query>

and by &quot;return records&quot; i mean be able to
hook the sp directly to a report. not
just &quot;sp executed successfully but did not
return records&quot;.

these cases return records:

<query with insert and NO sumvals>
<manipulate cursor>
<return records from another query>

<query with sumvals and NO insert>
<manipulate cursor>
<return records from another query>

<query with insert and sumvals>
<return records from another query>
<manipulate cursor>

There's just something about running
a q with both sumvals and an insert,
then minipulating a cursor, that
keeps the final q from returning records.

-g

[flush]

 

[SQLSister]

Please post your code if you expect help on a problem of this nature.

 

[evilmousse]

I did before in several forums and it sat unanswered.

My ultimate goal is to get the last query
(SELECT * FROM #t_banana) to return records
after uncommenting just the &quot;query with both&quot;,
so that i can hook a report to this sp via
recordsource = &quot;exec sp3&quot;. As it stands, the
procedure &quot;completed successfully but did not
return records&quot;.

here's some less-simplified code:
(posting the true queries would be too confusing)

CREATE PROCEDURE sp3 AS
SET NOCOUNT ON

CREATE TABLE #t_temp1 ( fund_month datetime, fm_count int, sum_purchbal int)
CREATE TABLE #t_temp2 ( client_id int, mildate datetime)

/*query with just insert, no sumvals
INSERT INTO #t_temp1 (fund_month)
SELECT dbo.t_funding_client.f_dt_funding_client AS fund_month
FROM ..blahblah...
GROUP BY ..blahblah...
*/

/*query with just sumvals, no insert
SELECT t_funding_client.f_dt_funding_client AS fund_month, COUNT(DATENAME(m, dbo.t_funding_client.f_dt_funding_client) + SPACE(1) + CONVERT(varchar, DATEPART(yy, dbo.t_funding_client.f_dt_funding_client))) AS fm_count, SUM(dbo.t_funding_client.f_purchase_balance_client) AS sum_purchbal
FROM ..blahblah...
GROUP BY ..blahblah...
*/

/*query with both
INSERT INTO #t_temp1 (fund_month, fm_count, sum_purchbal)
SELECT t_funding_client.f_dt_funding_client AS fund_month, COUNT(DATENAME(m, dbo.t_funding_client.f_dt_funding_client) + SPACE(1) + CONVERT(varchar, DATEPART(yy, dbo.t_funding_client.f_dt_funding_client))) AS fm_count, SUM(dbo.t_funding_client.f_purchase_balance_client) AS sum_purchbal
FROM ..blahblah...
GROUP BY ..blahblah...
*/

SELECT DISTINCT client_id INTO #t_banana FROM dbo.t_client ORDER BY client_id

DECLARE FCursor CURSOR FORWARD_ONLY STATIC FOR SELECT DISTINCT product_code FROM dbo.t_mando
OPEN FCursor
DECLARE @pcode varchar(15)
Fetch NEXT FROM FCursor INTO @pcode
CLOSE FCursor
DEALLOCATE FCursor

SELECT * FROM #t_banana
SET NOCOUNT OFF


-g

 

[SonOfEmidec1100]

What about putting SET NOCOUNT OFF before the final select ?

 

[evilmousse]

tried it before. no good.

thx anyway.

-g

 

[SQLSister]

how about you create a table variable as an output variable. Then set it equal to the select statement?

 

[evilmousse]

sqlsister:

I'd love to! ..but i didn't see any table datatype
in my sql reference. where can I find more about
a table parameter? If it works, it'd definetly
solve my problem.

-g

 

[SQLSister]

WHat version of SQL server are you using? In SQL Server 2000 you can use table variables. Look up table variable in Books on line for more information. Basically you set it up as a variable in your stored procedure declaration
CREATE PROCEDURE usp_SampleProcedure
@TableVar1 table (client_id int Not Null) Output
AS
Then use a regular insert statement to insert the select statement into it

Insert into @TableVar1
SELECT DISTINCT client_id INTO #t_banana FROM dbo.t_client ORDER BY client_id

 

[evilmousse]

ok, i'm testing this out now, thanks for the advice.
right now I'm trying to make it a stored function with a table return val, but the SF doesn't like my use of temp tables. i'll keep trying, and mark your solution if it comes to bear. thanks again!

-g