I don't want user dynamic sql

Dear all,
I have a stored procedure which might do INSERT, UPDATE over a specified
table.
Issue is that, at the outset, that table is totally unknown:
' into aux_PLAZO_PRIMER_IMPAGO ' +
' from ' + @.TABLA_COB
EXEC
..
' end DISC043 ' +
' into aux_PLAZO_PRIMER_IMPAGO ' +
' from ' + @.TABLA_COB
EXEC...
BLA,BLA,
I'm looking for a best version of that, dangerous dynamic sql is not well
welcomed here so that...
Declaring a variable as table also to solve the problem.
Thanks in advance for any input,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Enric
The best solution is know a table name that you operate on.
http://www.sommarskog.se/dynamic_sql.html
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:72F7C1B3-7C25-414A-8957-D9B0472476D6@.microsoft.com...
> Dear all,
> I have a stored procedure which might do INSERT, UPDATE over a specified
> table.
> Issue is that, at the outset, that table is totally unknown:
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC
> ..
> ' end DISC043 ' +
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC...
> BLA,BLA,
> I'm looking for a best version of that, dangerous dynamic sql is not well
> welcomed here so that...
> Declaring a variable as table also to solve the problem.
> Thanks in advance for any input,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||not sure if this is what you're looking for, but sp_executesql is the
first step to prevent SQL injection attacks. after that you're next
best step would be to validate the table against the sysobjects table
to ensure that it is a real table.|||I'm not sure I agree with what he says on that link. IMO he uses exec
way too much. His reasons for not using sp_executesql do not make
sense, and while he does suggest using quotename(), stored procedures
are a better way to ensure that your parameters are unable to be
anything other than the datatype suggested.|||Create as many INSERT and UPDATE procedures as there are tables, that's the
best advice any one can (and should) give you.
If you don't care about data, however, you could just as well store it all
in a single table. (NOT RECOMMENDED!)
I see no reason for dynamic SQL in such elementary processes as inserting,
updating or deleteing a row in a table - building the query string might eve
n
take longer than the execution (i.e. if you decide to do it right - clean up
the parameters to prevent SQL injection, check whether objects exist,
validating the parameters that contain actual values, etc.).
ML
http://milambda.blogspot.com/|||I don't know if this is the situation, but creating a generic insert /
update procedure for tables that store different types of objects is a bad
idea. Even if it's not the case now, as time goes on, there will be a need
to implment additional table specific parameters, data validation
programming, etc. and the procedure will become a big pile of unmanageable
spaghetti. Business rules for things like data validation and referential
integrity are best embedded at the table level in the form of constraints
and triggers.
Generic data access programming is best implmented on the application side
in the form of a data access class.
Designing Data Tier Components and Passing Data Through Tiers:
http://msdn.microsoft.com/library/d...Gag
.asp
On the other hand, if these tables store the same object (vertical
paritioning), then you can implement a partitioned view and perform inserts
/ updates into that:
Modifying Data in Partitioned Views:
http://msdn2.microsoft.com/en-us/library/ms187067.aspx
Strategies for Partitioning Relational Data Warehouses in Microsoft SQL
Server:
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
That said; if dynamic SQL can't be avoided, here my links on the topic of
SQL injection:
http://www.sqlservercentral.com/col...qlinjection.asp
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.nextgenss.com/papers/adv...l_injection.pdf
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:72F7C1B3-7C25-414A-8957-D9B0472476D6@.microsoft.com...
> Dear all,
> I have a stored procedure which might do INSERT, UPDATE over a specified
> table.
> Issue is that, at the outset, that table is totally unknown:
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC
> ..
> ' end DISC043 ' +
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC...
> BLA,BLA,
> I'm looking for a best version of that, dangerous dynamic sql is not well
> welcomed here so that...
> Declaring a variable as table also to solve the problem.
> Thanks in advance for any input,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||Will (william_pegg@.yahoo.co.uk) writes:
> I'm not sure I agree with what he says on that link. IMO he uses exec
> way too much. His reasons for not using sp_executesql do not make
> sense, and while he does suggest using quotename(), stored procedures
> are a better way to ensure that your parameters are unable to be
> anything other than the datatype suggested.
What I try to say about this particular case, is that that you should
not do this at all. That is, you should have one procedure per table.
I can't recall that I suggest that EXEC() should be used over sp_executesql,
but you are right that the text could be stronger on using sp_executesql,
and most of all using parameters. In fact, I'm already working with
reworking the article with a lot more emphasis on sp_executesql.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx