Dynamic SQL with variables in user-defined Functions

Dynamic SQL with variables in user-defined Functions

Post by Bruc » Sat, 23 Aug 2003 01:44:26



I need to create a dynamic SQL statement in a user-defined function, but the
statement needs to return values back into local variables:


to do this, I need to use sp_executesql:


but SQL Server does not allow a function to call a stored procedure. I also
cannot use temporary tables because SQL Server also does not allow temporary
tables in functions.

Any way to accomplish this?

Bruce

 
 
 

Dynamic SQL with variables in user-defined Functions

Post by Dan Guzma » Sat, 23 Aug 2003 02:03:56


You don't need to use dynamic SQL to assign values to local variables.
Is there some reason you can't simply include the following SELECT
statement in your function?


--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


> I need to create a dynamic SQL statement in a user-defined function,
but the
> statement needs to return values back into local variables:


> to do this, I need to use sp_executesql:


> but SQL Server does not allow a function to call a stored procedure. I
also
> cannot use temporary tables because SQL Server also does not allow
temporary
> tables in functions.

> Any way to accomplish this?

> Bruce


 
 
 

Dynamic SQL with variables in user-defined Functions

Post by Bruc » Sat, 23 Aug 2003 17:18:17


I neglected to mention that another part of the sql statement (a table name)
is added inside a cursor loop. Like:


Bruce

------------------------------

> You don't need to use dynamic SQL to assign values to local variables.
> Is there some reason you can't simply include the following SELECT
> statement in your function?


> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP

 
 
 

Dynamic SQL with variables in user-defined Functions

Post by Dan Guzma » Sat, 23 Aug 2003 17:32:10


Variable object names are problematic because these require dynamic SQL
and limit your options.  Check out Erland's article on dynamic SQL
considerations:

    http://www.algonet.se/~sommar/dynamic_sql.html

--
Hope this helps.

Dan Guzman
SQL Server MVP


> I neglected to mention that another part of the sql statement (a table
name)
> is added inside a cursor loop. Like:


> Bruce

> ------------------------------


> > You don't need to use dynamic SQL to assign values to local
variables.
> > Is there some reason you can't simply include the following SELECT
> > statement in your function?


> > --
> > Hope this helps.

> > Dan Guzman
> > SQL Server MVP

 
 
 

Dynamic SQL with variables in user-defined Functions

Post by Joe Celk » Sat, 23 Aug 2003 20:59:50


Quote:>>  I neglected to mention that another part of the sql statement (a

table name) is added inside a cursor loop. <<

The short answer is use slow, proprietrary dynamic SQL to kludge a query
together on the fly with your table name in the FROM clause.  But you
are asking about how to do two bits of awful programming.  

1) Cursors are bad things that can avoided 99.99% of the time.  Are you
doing an NP Complete problem for which you can accept the first
near-optimal answer?  That is the only use I have found for cursors.  

Cursors are usually used by people who don't know SQL or non-procedural
coding to fake the old sequential access files they had in Cobol,
Fortran, etc.

2) Never pass a table name as a parameter.  You need to understand the
basic idea of a data model and what a table means in implementing a data
model.  

1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.  

2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future.  Remember the
basics of Software Engineering?  Modules need weak coupling and strong
cohesion, etc.

3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal.  Look up what
Chris Date has to say about this design flaw.  

Go back to basics.  What is a table?  A model of a set of entities or
relationships.  EACH TABLE SHOULD BE A DIFFERENT KIND OF ENTITY.  What a
generic procedure that works equally on automobiles, octopi or Britney
Spear's discology is saying that your applications a disaster of design
because you have:

1) failed to put all items of the same kind into one table.  Chris date
calls this orthogonal design, and I call it attribute splitting.  Common
example, 12 identical tables, one for each month, with the same
information them instead of a single table with a temp*attribute.  

2) failed to tell the difference between data and meta-data.  The SQL
engine has routines for that stuff and applications do not work at that
level, if you want to have any data integrity.  

Try to get some help from a more expereicned SQL programmer in your
shop.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.veryComputer.com/ ***
Don't just participate in USENET...get rewarded for it!