Stored Procedure Not Returning Recordset

[jody_baldwin]

I'm in the processs of creating a Dashboard site for a customer. I've written several stored procedures that returned the needed recordsets for the website however when I try to create either a command or recordset using the stored procedures I get an error message "No Recordset was Returned". The stored procedures work fine when I test them from SQL Query Analyzer.

I'm using Dreamweaver 8 with the lastest updates. Any help on this would be greatly appreicated!

Here is a the code for one of the stored procedures I'm using:

CREATE PROCEDURE [dbo].[spGetServiceLevel2]
AS
DECLARE @Sql nvarchar(1500)

SET @Sql = N'SELECT lob.lob_id, LOB.lob_name AS lob_name, row_date, SUM(cms.acdcalls) AS acdcalls, SUM(cms.acceptable) AS acceptable,

today_calls = CASE FLOOR(CONVERT(float, GETDATE()))
WHEN FLOOR(CONVERT(float, row_date)) Then SUM(cms.acdcalls)
ELSE 0
END,
today_acceptable = CASE FLOOR(CONVERT(float, GETDATE()))
WHEN FLOOR(CONVERT(float, row_date)) Then SUM(cms.acceptable)
ELSE 0
END,

mtd_calls = CASE Month(GETDATE())
WHEN month(row_date) Then SUM(cms.acdcalls)
ELSE 0
END,

mtd_acceptable = CASE Month(GETDATE())
WHEN month(row_date) Then SUM(cms.acceptable)
ELSE 0
END,

ytd_calls = CASE Year(GetDate())
WHEN Year(Row_Date) Then SUM(cms.acdcalls)
ELSE 0
END,

ytd_acceptable = CASE Year(GetDate())
WHEN Year(Row_Date) Then SUM(cms.acceptable)
ELSE 0
END



FROM dbo.tbl_entity_to_line_of_business e2lob INNER JOIN
dbo.tbl_lines_of_business LOB ON e2lob.lob_id = LOB.lob_id INNER JOIN
dbo.tbl_entities E ON e2lob.entity_id = E.entity_id INNER JOIN
dbo.kcpl_hsplit cms ON E.entity_num = cms.split
WHERE entity_type_id = 1
GROUP BY lob.lob_id, LOB.lob_name, row_date'

CREATE TABLE #Source

( lob_id INT
,lob_name VARCHAR(100)
,row_date DATETIME
,acdcalls INT
,acceptable INT
,today_calls FLOAT
,today_acceptable FLOAT
,mtd_calls FLOAT
,mtd_acceptable FLOAT
,ytd_calls FLOAT
,ytd_acceptable FLOAT
)



CREATE TABLE #Temp

( lob_id INT
,lob_name VARCHAR(100)
,today_calls FLOAT
,today_acceptable FLOAT
,mtd_calls FLOAT
,mtd_acceptable FLOAT
,ytd_calls FLOAT
,ytd_acceptable FLOAT
,today_sl FLOAT
,mtd_sl FLOAT
,ytd_sl FLOAT
)



INSERT #Source

( lob_id
,lob_name
,row_date
,acdcalls
,acceptable
,today_calls
,today_acceptable
,mtd_calls
,mtd_acceptable
,ytd_calls
,ytd_acceptable
)



EXEC sp_executesql @Sql

INSERT #Temp

( lob_id
,lob_name
,today_calls
,today_acceptable
,mtd_calls
,mtd_acceptable
,ytd_calls
,ytd_acceptable
,today_sl
,mtd_sl
,ytd_sl
)

SELECT lob_id, lob_name, SUM(today_calls) AS today_calls, SUM(today_acceptable) AS today_acceptable, SUM(mtd_calls) AS today_acceptable,
SUM(mtd_acceptable) AS mtd_acceptable,SUM(ytd_calls) AS ytd_calls, SUM(ytd_acceptable) AS ytd_acceptable,

today_sl=
Case Sum(today_calls)
WHEN 0 THEN 0
ELSE (Sum(today_acceptable) / Sum(today_calls)) * 100
END,

mtd_sl=
Case Sum(mtd_calls)
WHEN 0 THEN 0
ELSE (Sum(mtd_acceptable) / Sum(mtd_calls)) * 100
END,

ytd_sl=
Case Sum(ytd_calls)
WHEN 0 THEN 0
ELSE (Sum(ytd_acceptable) / Sum(ytd_calls)) * 100
END
FROM #Source
GROUP BY lob_id, lob_name

SELECT lob_id,
lob_name,
str(today_sl,5,2) as today_sl,
str(mtd_sl,5,2) as mtd_sl,
str(ytd_sl,5,2) as ytd_sl
FROM #Temp
WHERE lob_id = 1

DROP TABLE #Temp

DROP TABLE #Source
GO

[Newsgroup_User]

You may be getting tripped up by meta-data recordsets. Suppress these by
using SET NOCOUNT ON immediately after AS in your procedure, and adding SET
NOCOUNT OFF just before GO to restore normal behavior.
There's also no reason to use EXEC inside the procedure.

INSERT INTO #Source(fields)
SELECT ...stuff...

is perfectly acceptable.

[jody_baldwin]

Your advice was right on. They're working fine now. Thanks!!

[Newsgroup_User]

No problem.

"jody_baldwin" <webforumsuser@macromedia.com> wrote in message
news:e64csv$er1$1@forums.macromedia.com...
> Your advice was right on. They're working fine now. Thanks!!