User-defined Functions and Side-effects

User-defined Functions and Side-effects

Post by Jason Lipma » Wed, 12 Feb 2003 02:00:07



User-defined Functions in MSS2000......

Below is my function code. I am converting a procedure to a function that
takes a One-column, multi-row SQL output, and changes it into a
comma-delimited string. The code below works fine as a procedure, but I need
the function. The problem... the user-defined function does not like the
CREATE #temp table or the Insert INTO.. "side effects".

What alternatives do I have to holding SQL output in a table and looping
through it?

Thanks,

Jason

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
//////////////////////////


RETURNS varchar(8000) AS
---------------------------------------------------------------
-- Func Name: f_rows_to_var

column of output



--
-- Purpose  : This Procedure will take the rows from a one-column SQL
statement
--             and convert the rows into one string.
--
--            Make sure the output is distinct. For example you could get
this
--              for output... 'A','A','B','C','B','B',etc..., or you could
--              get this... 'A','B','C','D'....
-----------------------------------------------------------------
BEGIN


    BEGIN

    END
  ELSE
    BEGIN



        BEGIN




        END
      ELSE
        BEGIN

        END

      CREATE Table #temp (col1 varchar(500))





      FROM #temp

      DROP TABLE #temp

    END


END -- Entire statement
GO

GRANT ALL on f_rows_to_var to Public
GO

 
 
 

User-defined Functions and Side-effects

Post by Joel Aske » Wed, 12 Feb 2003 02:14:46


Jason:

That depends on how the comma-separated string is going to be consumed and
by what application layers.

If it's only going to be consumed by the middleware or client, why not just
let that layer consume the resultset and construct the string?


Quote:> User-defined Functions in MSS2000......

> Below is my function code. I am converting a procedure to a function that
> takes a One-column, multi-row SQL output, and changes it into a
> comma-delimited string. The code below works fine as a procedure, but I
need
> the function. The problem... the user-defined function does not like the
> CREATE #temp table or the Insert INTO.. "side effects".

> What alternatives do I have to holding SQL output in a table and looping
> through it?

> Thanks,

> Jason

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

- Show quoted text -

> //////////////////////////



> RETURNS varchar(8000) AS
> ---------------------------------------------------------------
> -- Func Name: f_rows_to_var

> column of output



> --
> -- Purpose  : This Procedure will take the rows from a one-column SQL
> statement
> --             and convert the rows into one string.
> --
> --            Make sure the output is distinct. For example you could get
> this
> --              for output... 'A','A','B','C','B','B',etc..., or you could
> --              get this... 'A','B','C','D'....
> -----------------------------------------------------------------
> BEGIN


>     BEGIN

>     END
>   ELSE
>     BEGIN



>         BEGIN




>         END
>       ELSE
>         BEGIN

>         END

>       CREATE Table #temp (col1 varchar(500))





>       FROM #temp

>       DROP TABLE #temp

>     END


> END -- Entire statement
> GO

> GRANT ALL on f_rows_to_var to Public
> GO


 
 
 

User-defined Functions and Side-effects

Post by Jason Lipma » Wed, 12 Feb 2003 02:31:34


Yeah, I thought of letting the client-side construct the string. I am trying
to avoid that, and make as much reusable code as possible on the server.  If
another server-side solution is not available to me, I will code the string
from the client side.

So, back to the function... are there any other ways to hold the temporary
'table' without really using a table?  Are there any ways to accomplish
this? :)

Jason


> Jason:

> That depends on how the comma-separated string is going to be consumed and
> by what application layers.

> If it's only going to be consumed by the middleware or client, why not
just
> let that layer consume the resultset and construct the string?



> > User-defined Functions in MSS2000......

> > Below is my function code. I am converting a procedure to a function
that
> > takes a One-column, multi-row SQL output, and changes it into a
> > comma-delimited string. The code below works fine as a procedure, but I
> need
> > the function. The problem... the user-defined function does not like the
> > CREATE #temp table or the Insert INTO.. "side effects".

> > What alternatives do I have to holding SQL output in a table and looping
> > through it?

> > Thanks,

> > Jason

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

- Show quoted text -

> > //////////////////////////


varchar(2),

> > RETURNS varchar(8000) AS
> > ---------------------------------------------------------------
> > -- Func Name: f_rows_to_var

> > column of output



> > --
> > -- Purpose  : This Procedure will take the rows from a one-column SQL
> > statement
> > --             and convert the rows into one string.
> > --
> > --            Make sure the output is distinct. For example you could
get
> > this
> > --              for output... 'A','A','B','C','B','B',etc..., or you
could
> > --              get this... 'A','B','C','D'....
> > -----------------------------------------------------------------
> > BEGIN


> >     BEGIN

> >     END
> >   ELSE
> >     BEGIN



> >         BEGIN




> >         END
> >       ELSE
> >         BEGIN

> >         END

> >       CREATE Table #temp (col1 varchar(500))





> >       FROM #temp

> >       DROP TABLE #temp

> >     END


> > END -- Entire statement
> > GO

> > GRANT ALL on f_rows_to_var to Public
> > GO

 
 
 

User-defined Functions and Side-effects

Post by BP Margoli » Wed, 12 Feb 2003 04:37:12


Jason,

SQL Server 2000 does not allow a UDF to alter the state of the database.
Temp tables are real tables, although they reside in the tempdb.

SQL Server 2000 introduced table variables that can be used within UDF's
very similar to temp tables. There are some differences between the two, but
they are more alike than different. The SQL Server 2000 Books Online has
documentation on table variables.

Note, however, that one can not use dynamic SQL (sp_ExecuteSQL) in UDF's.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> User-defined Functions in MSS2000......

> Below is my function code. I am converting a procedure to a function that
> takes a One-column, multi-row SQL output, and changes it into a
> comma-delimited string. The code below works fine as a procedure, but I
need
> the function. The problem... the user-defined function does not like the
> CREATE #temp table or the Insert INTO.. "side effects".

> What alternatives do I have to holding SQL output in a table and looping
> through it?

> Thanks,

> Jason

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

- Show quoted text -

> //////////////////////////



> RETURNS varchar(8000) AS
> ---------------------------------------------------------------
> -- Func Name: f_rows_to_var

> column of output



> --
> -- Purpose  : This Procedure will take the rows from a one-column SQL
> statement
> --             and convert the rows into one string.
> --
> --            Make sure the output is distinct. For example you could get
> this
> --              for output... 'A','A','B','C','B','B',etc..., or you could
> --              get this... 'A','B','C','D'....
> -----------------------------------------------------------------
> BEGIN


>     BEGIN

>     END
>   ELSE
>     BEGIN



>         BEGIN




>         END
>       ELSE
>         BEGIN

>         END

>       CREATE Table #temp (col1 varchar(500))





>       FROM #temp

>       DROP TABLE #temp

>     END


> END -- Entire statement
> GO

> GRANT ALL on f_rows_to_var to Public
> GO

 
 
 

User-defined Functions and Side-effects

Post by Anith Se » Wed, 12 Feb 2003 04:57:28


Quote:>> SQL Server 2000 does not allow a UDF to alter the state of the

database. <<

BP, that is not entirely true. The BOL reference about this is not
fully accurate with respect to table-valued UDFs. Try the following:

CREATE TABLE tbl(
     IdCol INT NOT NULL PRIMARY KEY,
     col INT)
INSERT tbl VALUES(2,3)
INSERT tbl VALUES(1,5)
GO
CREATE FUNCTION ufn()
RETURNS TABLE
AS
RETURN(SELECT IdCol, col
         FROM tbl)
GO
SELECT * FROM tbl                -- before update
GO
UPDATE ufn()
   SET col = 4
 WHERE IdCol = 1
GO
SELECT *  FROM tbl             -- after update.
GO

--
- Anith
(Please respond only to newsgroups)

 
 
 

User-defined Functions and Side-effects

Post by BP Margoli » Wed, 12 Feb 2003 05:10:48


Anith,

I will accept that my terminology was imprecise ...

I should have posted that SQL Server 2000 does allow a UDF to alter the
state of the database from within the UDF itself ... and, of course, as we
both know even that is not completely accurate ... one can invoke an
extended stored procedure that alters the state of the database from within
the UDF itself   :-)

Thanks for forcing me to be more accurate   :-)

BPM


Quote:> >> SQL Server 2000 does not allow a UDF to alter the state of the
> database. <<

> BP, that is not entirely true. The BOL reference about this is not
> fully accurate with respect to table-valued UDFs. Try the following:

> CREATE TABLE tbl(
>      IdCol INT NOT NULL PRIMARY KEY,
>      col INT)
> INSERT tbl VALUES(2,3)
> INSERT tbl VALUES(1,5)
> GO
> CREATE FUNCTION ufn()
> RETURNS TABLE
> AS
> RETURN(SELECT IdCol, col
>          FROM tbl)
> GO
> SELECT * FROM tbl                -- before update
> GO
> UPDATE ufn()
>    SET col = 4
>  WHERE IdCol = 1
> GO
> SELECT *  FROM tbl             -- after update.
> GO

> --
> - Anith
> (Please respond only to newsgroups)

 
 
 

User-defined Functions and Side-effects

Post by BP Margoli » Wed, 12 Feb 2003 06:13:15


Arrrrgggg ... the missing NOT syndrome strikes again   ;-(

That, fairly obviously, should be:

"I should have posted that SQL Server 2000 does NOT allow a UDF to alter the
state of the database from within the UDF itself ... "

BPM


> Anith,

> I will accept that my terminology was imprecise ...

> I should have posted that SQL Server 2000 does allow a UDF to alter the
> state of the database from within the UDF itself ... and, of course, as we
> both know even that is not completely accurate ... one can invoke an
> extended stored procedure that alters the state of the database from
within
> the UDF itself   :-)

> Thanks for forcing me to be more accurate   :-)

> BPM



> > >> SQL Server 2000 does not allow a UDF to alter the state of the
> > database. <<

> > BP, that is not entirely true. The BOL reference about this is not
> > fully accurate with respect to table-valued UDFs. Try the following:

> > CREATE TABLE tbl(
> >      IdCol INT NOT NULL PRIMARY KEY,
> >      col INT)
> > INSERT tbl VALUES(2,3)
> > INSERT tbl VALUES(1,5)
> > GO
> > CREATE FUNCTION ufn()
> > RETURNS TABLE
> > AS
> > RETURN(SELECT IdCol, col
> >          FROM tbl)
> > GO
> > SELECT * FROM tbl                -- before update
> > GO
> > UPDATE ufn()
> >    SET col = 4
> >  WHERE IdCol = 1
> > GO
> > SELECT *  FROM tbl             -- after update.
> > GO

> > --
> > - Anith
> > (Please respond only to newsgroups)

 
 
 

User-defined Functions and Side-effects

Post by Joel Aske » Wed, 12 Feb 2003 09:53:04


Jason:

I don't know your application architecture, but code re-use is good wherever
the component is located...
I don't know another way to accomplish this from a UDF.


> Yeah, I thought of letting the client-side construct the string. I am
trying
> to avoid that, and make as much reusable code as possible on the server.
If
> another server-side solution is not available to me, I will code the
string
> from the client side.

> So, back to the function... are there any other ways to hold the temporary
> 'table' without really using a table?  Are there any ways to accomplish
> this? :)

> Jason



> > Jason:

> > That depends on how the comma-separated string is going to be consumed
and
> > by what application layers.

> > If it's only going to be consumed by the middleware or client, why not
> just
> > let that layer consume the resultset and construct the string?



> > > User-defined Functions in MSS2000......

> > > Below is my function code. I am converting a procedure to a function
> that
> > > takes a One-column, multi-row SQL output, and changes it into a
> > > comma-delimited string. The code below works fine as a procedure, but
I
> > need
> > > the function. The problem... the user-defined function does not like
the
> > > CREATE #temp table or the Insert INTO.. "side effects".

> > > What alternatives do I have to holding SQL output in a table and
looping
> > > through it?

> > > Thanks,

> > > Jason

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

- Show quoted text -

> > > //////////////////////////


> varchar(2),

> > > RETURNS varchar(8000) AS
> > > ---------------------------------------------------------------
> > > -- Func Name: f_rows_to_var

one
> > > column of output

desired.


> > > --
> > > -- Purpose  : This Procedure will take the rows from a one-column SQL
> > > statement
> > > --             and convert the rows into one string.
> > > --
> > > --            Make sure the output is distinct. For example you could
> get
> > > this
> > > --              for output... 'A','A','B','C','B','B',etc..., or you
> could
> > > --              get this... 'A','B','C','D'....
> > > -----------------------------------------------------------------
> > > BEGIN


> > >     BEGIN

> > >     END
> > >   ELSE
> > >     BEGIN



> > >         BEGIN

END

END

END

END
> > >         END
> > >       ELSE
> > >         BEGIN

> > >         END

> > >       CREATE Table #temp (col1 varchar(500))





> > >       FROM #temp

> > >       DROP TABLE #temp

> > >     END


> > > END -- Entire statement
> > > GO

> > > GRANT ALL on f_rows_to_var to Public
> > > GO