Query programmable

Hi,
I need to create a stored procedures that can pass table parameters in
varchar format.
for example:
CREATE FUNCTION MyFunc (@.table varchar(200)
AS
UPDATE @.table + 'aaa' -> How can I do this?
SET field1=tbl2.field2
FROM tbl2
WHERE tbl2.field3>10Use Dynamic SQL
Madhivanan|||You can try using dynamic SQLs
like this ..
declare @.t nvarchar(1000)
set @.t = 'UPDATE ' +@.table + 'aaa ' +
'SET field1=tbl2.field2 FROM tbl2 WHERE tbl2.field3>10 '
exec sp_executeSQL @.t|||UPDATE statements are not allowed in functions. Dynamic SQL is also
disallowed in functions. Perhaps a stored procedure will address your
needs.
Check out http://www.sommarskog.se/dynamic_sql.html for dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dario Concilio" <DarioConcilio@.discussions.microsoft.com> wrote in message
news:F6C4572F-6306-47CB-9087-5C3E2204C824@.microsoft.com...
> Hi,
> I need to create a stored procedures that can pass table parameters in
> varchar format.
> for example:
> CREATE FUNCTION MyFunc (@.table varchar(200)
> AS
> UPDATE @.table + 'aaa' -> How can I do this?
> SET field1=tbl2.field2
> FROM tbl2
> WHERE tbl2.field3>10|||I can't see why that would make sense as a requirement unless your
tables duplicated lots of non-key columns. In that case it's really a
design problem rather than something to solve with a parameterized
UPDATE.
Dynamic SQL is one way you can do this but in a well-designed database
it should rarely be necessary.
David Portas
SQL Server MVP
--|||>> I need to create a stored procedures that can pass table parameters
in varchar format. <<
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.
The right answer is 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. 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 having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your applications a disaster of design.
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. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
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. Look up the term
attribute splitting.
4) You might have 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.
Yes, you can write a program with dynamic SQL to kludge something like
this. It will last about a year in production and then your data
integrity is shot.sql