no idea how to change the output of my SP

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 idea how to change the output of my SP

Author

Topic

indieman
Starting Member

12 Posts

Posted - 2006-03-09 : 21:56:11

Hi,

I just wondering if someone knows the answer to this. im newish to sql but have created this stored procedure to query my database. The script see's which consultant(s) are free on a particular day and then loops through a table of dates to find who is free in the future.

this brings the results back perfectly. however i am in dreamweaver trying to show the resultset but it only shows the first record. I presume this is to do with the cursor?

so my question is how do I get back the results in a nice format, one after another, that i can use?

any help is appreciated!

CREATE PROCEDURE sp_Test
as

DECLARE @colA nvarchar(20)
DECLARE @MyCursor CURSOR
DECLARE @level varchar(1)
DECLARE @consultancytypename varchar(20)
DECLARE @daterequired datetime
DECLARE @client numeric(9)

SET @daterequired = '01-01-2006'
SET @level = '2'
SET @consultancytypename = 'Scanning'
SET @client = '3'

SET NOCOUNT OFF
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT DayOfWeekDate
FROM WeekEndsAndHolidays

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA

WHILE @@FETCH_STATUS = 0
BEGIN
---------------BEGIN--------------------
DECLARE @dayname varchar(20)
DECLARE @dateandday varchar(25)
SET @dayname = (SELECT dayname from WeekEndsAndHolidays where DayOfWeekDate = @ColA)
SET @dateandday = LEFT(@ColA,(LEN(@ColA)-8)) + ' (' + @dayname + ')'

SELECT consultantname 'Name Of Consultant(s)', @dateandday
FROM vw_consultant_ability
WHERE (
--consultancy type is the desired one
consultancytypename = @consultancytypename

--the consultants competence is high enough
AND consultancytypelevel >= @level

--check they are not on holiday
AND consultantid NOT IN
(
SELECT consultantid
FROM consultant_availability
WHERE dateunavailable = @ColA
)
)

--------------END----------------------
FETCH NEXT FROM @MyCursor
INTO @ColA
END

CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-09 : 22:08:46

You are returning multiple result set of one record each.

Change this from cursor base to set based and return all records as a single result set. Or if you want to stick to cursor approach, create a temp table and store all the result and select from this temp table a the end of your stored procedure.

----------------------------------
'KH'

indieman
Starting Member

12 Posts

Posted - 2006-03-09 : 23:25:30

thanks for the advice. think i should be able to sort it now:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-10 : 01:24:16

>>SET @dayname = (SELECT dayname from WeekEndsAndHolidays where DayOfWeekDate = @ColA)

You will get error, if subquery returns more than one value
The correct method is

select @dayname = dayname from WeekEndsAndHolidays where DayOfWeekDate = @ColA

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources