Create View based on User-Defined Function

I would like to create a View in SQL Server 2000, based on a user-defined
function. However when I try it, the "Functions" tab is empty. There are n
o
functions to choose from. I can select tables and other views.Josh
Well , have you already created UDF?
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>I would like to create a View in SQL Server 2000, based on a user-defined
> function. However when I try it, the "Functions" tab is empty. There are
> no
> functions to choose from. I can select tables and other views.|||Yes. I have 8 of them.
"Uri Dimant" wrote:

> Josh
> Well , have you already created UDF?
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>
>|||The problem is with your tool of choice. Use QA and just type the
appropriate create/alter view query yourself.|||Josh
I have no problems. Perhpas if you are using EM , you need to refresh
thisn tool im order to be able to see the functions
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:433B1DEC-B7AB-4C90-A9B1-932CCDE4EE3F@.microsoft.com...[vbcol=seagreen]
> Yes. I have 8 of them.
> "Uri Dimant" wrote:
>|||Yeah, I've tried that but the function I'm trying to use contains variables
and the QA throws errors when I try to do it that way.
My ultimate goal is to use the function in MS Access. I was thinking that
I'd need to convert it to a view before I could import or link to it. If
there's another way I'd love to hear about it (I've posted this portion of
the question in one of the Access newsgroups).
Josh
"Scott Morris" wrote:

> The problem is with your tool of choice. Use QA and just type the
> appropriate create/alter view query yourself.
>
>|||> Yeah, I've tried that but the function I'm trying to use contains
> variables
> and the QA throws errors when I try to do it that way.
It helps to tell us exactly what you are doing. It is also critical to tell
us exactly what "throws errors" means. When you encounter an error, post
the exact text of the error. And get in the practice of specifying which
version (and service pack level) of sql server you are using.

> My ultimate goal is to use the function in MS Access. I was thinking that
> I'd need to convert it to a view before I could import or link to it. If
> there's another way I'd love to hear about it (I've posted this portion of
> the question in one of the Access newsgroups).
> Josh
A view is effectively a pre-cannned select query. You cannot define a view
that will accept arguments and pass those arguments to the function.|||OK. Hope this will make sense to people unfamiliar with our specific
database. We have a table with a "Comments" field that contains several
pieces of information delimited by slashes (/). I didn't design it this way
and would not have; I'd have put each piece of data in its own field. But
what's done is done and I have to work with it. The DBA created functions t
o
extract the individual portions of this field. One of them has the syntax
below:
CREATE FUNCTION dbo.GetFirstSlash
( @.Comments varchar(300) )
RETURNS INT
AS
BEGIN
DECLARE @.Result int
SET @.Result = 0
WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
BEGIN
IF SUBSTRING(@.Comments, @.Result, 1) = '/'
BEGIN
BREAK
END
SET @.Result = @.Result + 1
END
RETURN (@.Result)
END
When I try to create a view based on this function in QA by changing "Create
Function" to "Create View," I get the following errors:
Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
Line 2: Incorrect syntax near '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
Must declare the variable '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
Must declare the variable '@.Comments'.
Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
A RETURN statement with a return value cannot be used in this context.
"Scott Morris" wrote:

> It helps to tell us exactly what you are doing. It is also critical to te
ll
> us exactly what "throws errors" means. When you encounter an error, post
> the exact text of the error. And get in the practice of specifying which
> version (and service pack level) of sql server you are using.
>
> A view is effectively a pre-cannned select query. You cannot define a vie
w
> that will accept arguments and pass those arguments to the function.
>
>|||A view is a different thing compared to a function. A view cannot take param
eters, not can it
contain code, except for one SELECT statement. In addition, your function is
n't even a table values
function, it is a scalar function. A view exposes a set (a table) where a sc
alar function returns a
scalar value when executed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:C2DF362C-89AA-4F3D-A5CD-BDEE6F1A866B@.microsoft.com...[vbcol=seagreen]
> OK. Hope this will make sense to people unfamiliar with our specific
> database. We have a table with a "Comments" field that contains several
> pieces of information delimited by slashes (/). I didn't design it this w
ay
> and would not have; I'd have put each piece of data in its own field. But
> what's done is done and I have to work with it. The DBA created functions
to
> extract the individual portions of this field. One of them has the syntax
> below:
> CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.Result int
> SET @.Result = 0
> WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
> RETURN (@.Result)
> END
> When I try to create a view based on this function in QA by changing "Crea
te
> Function" to "Create View," I get the following errors:
> Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
> Line 2: Incorrect syntax near '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
> Must declare the variable '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
> Must declare the variable '@.Comments'.
> Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
> A RETURN statement with a return value cannot be used in this context.
> "Scott Morris" wrote:
>|||Once again - a view does not have arguments. You cannot simply replace
"create function" with "create view" in this case. Personally, I recommend
a different approach. First, write a script that contains a select query
that does what you want. Then, try to convert that to a view.
Seems to me that you probably want a view that is based on the table
containing the comments column and that uses some function (or logic) that
parses the comment column into its individual components.