create function - for SQL Server

[kingz2000]

Hi ,

I have quite a simple SQL

SELECT EAR_LOB, Controller
FROM Ultimate_Claim_Segmentation
GROUP BY EAR_LOB, Controller
HAVING Ultimate_Claim_Segmentation.Controller=[Control_Segment];

, which I am trying to execute as a function so that I can insert [control_segment] as a parameter.

In Access, it would automatically as for the parameter value, but I want to build a function which works in the same way..SO i tried:

create function dbo.Control_Segment(@conseg varchar(10))
returns varchar(10)
as begin
SELECT EAR_LOB
, Controller
FROM Ultimate_Claim_Segmentation
GROUP BY EAR_LOB
, Controller
HAVING Controller=@conseg
end

Unfortunately it doesn't work. the error is :
"Select statements included within a function cannot return data to a client."
what am I doing wrong???

 

[HarleyQuinn]

I'd try this in Forum183

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[lespaul]

additionally, the HAVING clause is used to filter on an AGGREGATE. So, you could move your HAVING clause to a WHERE clause.

FYI, HAVING is used when you want to run a sales report for all salesman who live in california and sell more than $100000 a year:

SELECT SalesmanID, SUM(TotalSales) 
FROM ORDERS
INNER JOIN SALESMAN ON ORDERS.SalesmanID = SALESMAN.ID
WHERE SALESMAN.STATE = "CA" and Year(ORDERS.OrderDate) = Year(Now())
GROUP BY SalesmanID
HAVING SUM(TotalSales) >= 100000

So I have an Orders table that stores the totalsales and the salesmanId. I have a Salesman table that stores the state the salesman work in. I only want to see those that sold more than 100000 - this is when you use a HAVING clause. All the other criteria should be in the WHERE clause...only the aggregate (SUM, COUNT, AVG, MAX, MIN) should be in the HAVING clause.

HTH


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[JoeAtWork]

A function has to return the specific type of it's return type. Your SELECT statement returns two fields, and possibly multiple rows, instead of the single int return value it's supposed to.

I think you need to do something like declaring a variable of varchar(10), then set it equal to a SELECT statement that returns exactly one record with one field that is a VarChar(10). Then you return that variable.

I've forgotten the exact syntax, so I won't try to guess at it and give you potentially incorrect code.