create functions - 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???

 

[Gixonita]

Hello,

your select will return a table with an x number or rows for EAR_LOB and Controller, you are trying to create a function that returns a single varchar(10) value. Please explain a little more of what you are trying to do.

 

[kingz2000]

ok,

I want to be able to add a parameter to a select! Thats basically it. To elaborate, In my 1st SQL, if that is doen in Ms Access, it will automatically prompt you to enter a value for [Control_Segment].
I am trying to create this under SQL-Server whereby I would merely call up the function with the parameter Control_Segment], in order to receve the same results.

Thanks in advance,
Kingz

 

[gmmastros]

The simplest way to do this is to open up query analyzer and type...

[tt]Select dbo.Control_Segment('[!]Some Value[/!]') [/tt]

The important thing to realize though... SQL Server is a database without a front end component. Sure, there are gui apps distributed with SQL Server (Enterprise Manager and Query Analyzer with SQL 2000 and SQL Server Management Studio with SQL 2005). You should not allow your end users access to these management applications because they could inadvertently cause lots of problems with your database. If you want to allow your end users to run procedures and functions in your database, you should create an application in another language. For example, you could write an app in C#, C++, VB, ASP, ASP.net, php, coldfusion, etc... In that application, you would write a simple data entry screen to allow the user to enter the parameter value, then pass the name of the function and it's parameter value to the database engine, and then display the results on the front end.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

You write a stored proc with input parameters. Your calling application then fills in those input parameters into the exec sp call based on what the user put on the form.



"NOTHING is more important in a database than integrity." ESquared