Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?

> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.

> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?

> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.

> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.

> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.sql