October 14, 2009 at 11:50 am
Here's an example of a query to list parameters from the procedure "testProc".
create procedure dbo.testProc
@parm1 int = null
,@parm2 varchar(30)
,@parm3 numeric (5,2) = 1000
as
select @parm1, @parm2, @parm3
go
testProc 21,'BlackJack',100.25
declare @name nchar(100)
set @name = 'dbo.testProc'
select @name as procName, parameter_id,sp.name as parmName, st.name as dataType, sp.max_length, sp.[precision], sp.scale, is_xml_document
-- , sp.has_default_value, sp.default_value -- these apparently are not to be trusted
from sys.parameters sp
join sys.types st on st.system_type_id = sp.system_type_id
where object_id = object_id(@name)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 12:43 pm
GSquared (10/14/2009)
Iterating through the parameters would definitely be better. Heck, even I managed to do that in an Access app I used to operate, and I'm far from a skilled dev (outside of T-SQL), so it can't even be all that difficult to do.
Of course, building generic CRUD statements dynamically opens that other little problem: the "man in the middle"/injection/etc.... Just how secure is this going to be? What is going to be built in to prevent any of the other ugliness from happening? How do you prevent the values you didn't want changed from actually being changed now?
To be frank - the only way dynamic is "less work" is when the input is NOT being validated, inputs are not being checked, etc... Something we've renamed internally "chaos theory" (chaos is BOUND to ensue at some point with that strategy...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 14, 2009 at 11:55 pm
Sorry but am feeling a bit strong about this
From Bob Hovious signature
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
"Keep Trying"
October 15, 2009 at 4:07 pm
From current experience in my workplace: sometimes developers use third-party libraries like iBatis or NHibernate for Object Model - Relational Database mappings. Maybe these libraries are forcing the developer to have a parameter for each column in the table that corresponds to a property on his object model. It would have been probably nicer if he/she told you the reason (if there is such a reason), but maybe there is a reasoning behind this developer's request.
October 16, 2009 at 7:32 am
Al-279884 (10/15/2009)
From current experience in my workplace: sometimes developers use third-party libraries like iBatis or NHibernate for Object Model - Relational Database mappings. Maybe these libraries are forcing the developer to have a parameter for each column in the table that corresponds to a property on his object model. It would have been probably nicer if he/she told you the reason (if there is such a reason), but maybe there is a reasoning behind this developer's request.
I can almost gurantee this is not the case. Using 3rd party anything is anethema to this company.
(sometimes I wonder why they use SQL server, Visual Studio, etc).
Each application they roll out they build a new reporting system for it where they try to learn from the old one but eventually write it from the ground up. I asked once "why not just use SSRS or Crystal Reports?" After the dirty look I received it was explained to me that they don't use such things because "the companies tend to put out new versions that change stuff, and they don't want to adapt to it."
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply