Table-valued Functions

[Motlatjo]

Hi All

I have this Table:

Name: One
[pre]
ID Name Status Reason StartTime
1 Chris Busy Personal 2013-02-22 06:20:23.100
2 Chris Busy Case Related 2013-02-22 06:21:44.100
3 Chris Busy Lunch 2013-02-22 06:22:56.100
4 Chris Busy Available 2013-02-22 07:22:10.100
5 Mantsha Busy Personal 2013-02-22 06:20:56.100
6 Mantsha Busy Case Related 2013-02-22 06:21:22.100
7 Mantsha Busy Lunch 2013-02-22 06:26:16.100
8 Mantsha Busy Available 2013-02-22 07:18:16.100
9 Mantsha Busy Lunch 2013-02-22 07:18:16.100
[/pre]

Then i used this Function to manipulated the table so that it can be easly accessed by the SSRS Report

Create Function Agents(@NewReason varchar(20), @NewName varchar(20))
Returns @AgentsInfo Table (
	ID int NULL,
	Name varchar(20) NULL,
	Status varchar(50) NULL,
	Reason varchar(50) NULL,
	StartTime datetime NULL
)
AS
BEGIN
DECLARE 
	@ID int,
	@Name varchar(20),
	@Status varchar(50),
	@Reason varchar(50),
	@StartTime datetime;
		
SELECT @ID = ID,
       @Name = NAME,
       @Status = STATUS,
       @Reason = REASON,
       @StartTime = Starttime
      From One
      Where Reason = @NewReason
      And Name = @NewName;
      
      IF @ID IS NOT NULL 
    BEGIN
        INSERT @AgentsInfo
        SELECT @ID, @Name, @Status, @Reason, @StartTime;
    END;
      
   RETURN;   
END;

Then i ran this query

Select * From Agents('Personal', 'Mantsha')

Then i got this results

[pre]
ID Name Status Reason StartTime
9 Mantsha Busy Lunch 2013-02-22 07:18:16.100
[/pre]

While i was expecting this results

[pre]
ID Name Status Reason StartTime
7 Mantsha Busy Lunch 2013-02-22 06:26:16.100
9 Mantsha Busy Lunch 2013-02-22 07:18:16.100
[/pre]

Then I decided to use a cursor thinking i will get the right results
This is the Cursor:

DECLARE 
							@ID int,
							@Name varchar(20),
							@Status varchar(50),
							@Reason varchar(50),
							@StartTime datetime;

Declare AgentManip Cursor 
For
Select * From Agents('Lunch', 'Mantsha')
Open AgentManip
FETCH NEXT FROM AgentManip INTO @ID, @Name, @Status, @Reason, @StartTime
Select @ID ID, @Name NAME, @Status Status, @Reason Reason, @StartTime Starttime
WHILE @@FETCH_STATUS = 0
BEGIN
	Select @ID ID, @Name NAME, @Status Status, @Reason Reason, @StartTime Starttime
	FETCH NEXT FROM AgentManip INTO @ID, @Name, @Status, @Reason, @StartTime   
	
	
	END;
	
	
	CLOSE AgentManip
	DEALLOCATE AgentManip

Then i got this results:

[pre]
ID NAME Status Reason Starttime
9 Mantsha Busy Lunch 2013-02-22 07:18:16.100

ID NAME Status Reason Starttime
9 Mantsha Busy Lunch 2013-02-22 07:18:16.100
[/pre]

Please help me get the right results using this Function

 

[TheBugSlayer]

Your query

Select * From Agents('Personal', 'Mantsha')

should return

5 Mantsha Busy Personal 2013-02-22 06:20:56.100

To get

   7 Mantsha Busy Lunch 2013-02-22 06:26:16.100 
   9 Mantsha Busy Lunch 2013-02-22 07:18:16.100

You should call your function like this:

Select * From Agents('Lunch', 'Mantsha')

Not sure where what the problem is. In any case, FUNCTION, CURSOR...doesn't seem like a good approach.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[philhege]

You really need a stored procedure here.

I'm going to go out on a limb and suppose that the parameters are available in the SSRS report.

To return the dataset, create an SP:

CREATE PROCEDURE GetAgents
@reason varchar(32)
,@name varchar(32)

AS

SELECT

[ID]
,[Name]
,[Status]
,[Reason]
,StartTime

FROM One

WHERE name = @name AND reason = @reason

This is just a start. You'll have to adjust the procedure for NULL or blank parameters for one thing.

Also, how are the parameters presented in the report? I'd suggest basing them on DISTINCT queries of the fields in question.

-----------
With business clients like mine, you'd be better off herding cats.

 

[Motlatjo]

Hi

@TheBugSlayer thank you for taking you time to help me, my issue was to use the Multi-Statement Table Valued User defined Function, the parameter i wanted to throw to the function was

Select * From Agents('Lunch', 'Mantsha')

So the function was not returning the two rows, but i got a solution by using this query

Create Function Agents(@NewReason varchar(20), @NewName varchar(20))
Returns @AgentsInfo Table (
ID int NULL,
Name varchar(20) NULL,
Status varchar(50) NULL,
Reason varchar(50) NULL,
StartTime datetime NULL
)
AS
BEGIN
Insert @AgentsInfo (ID, Name, Status, Reason, StartTime)

SELECT ID,
NAME,
STATUS,
REASON,
Starttime
From One
Where Reason = @NewReason
And Name = @NewName;

RETURN;
END;

If you are interested on the reason y it was not working initially, i can explain to you

@philhege

i agree with what you are suggesting, but only want to call a function in the SSRS report, hope you understand me

Thank you all