To insert 50 parameters data into a SQL table, which way is better (fast insertion)?

Question

To insert 50 parameters data into a SQL table, which way is better (fast insertion)?

adonetok

SSCrazy Eights

Points: 8395
More actions
February 15, 2017 at 5:56 am

#340644

From a asp.net page, I'll get values for all parameters from text boxes, combo boxes.
Question: To insert all 50 parameters data into a SQL table, which way is better (fast insertion)?
1) In application,
2) Pass all 50 parameters to a store procedure and the make inserting from there.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

If you're talking about a single row insert, then adding the sproc just adds unnecessary overhead, unless you have an environment that requires the sprocs to do it because they're the only objects with the necessary permission to insert.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQLDave2015 SSC Enthusiast Points: 167 More actions · Answer 2

Store proc parameter can help with SQL injection and such. So it is a good practice to use store proc to insert instead of a simple SQL insert

_____________________________________________________________________________________________________
IT Jobs
KSL classifieds
Clasificados Los angeles
Busco Trabajo

sgmunson SSC Guru Points: 110640 More actions · Answer 3

SQLDave2015 - Thursday, February 16, 2017 12:27 AM
Store proc parameter can help with SQL injection and such. So it is a good practice to use store proc to insert instead of a simple SQL insert
_____________________________________________________________________________________________________

While using a sproc CAN help protect against SQL injection, it has to actually be coded to do so. If there's an nvarchar(max) parameter and nothing is done inside the sproc but append it to dynamic SQL, for example, then the mere use of a sproc hasn't done much for you in that regard. Been there, seen it happen. Fortunately, I wasn't the one that wrote the sproc in that case.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

sgmunson - Thursday, February 16, 2017 8:00 AM
SQLDave2015 - Thursday, February 16, 2017 12:27 AM
Store proc parameter can help with SQL injection and such. So it is a good practice to use store proc to insert instead of a simple SQL insert
_____________________________________________________________________________________________________
While using a sproc CAN help protect against SQL injection, it has to actually be coded to do so. If there's an nvarchar(max) parameter and nothing is done inside the sproc but append it to dynamic SQL, for example, then the mere use of a sproc hasn't done much for you in that regard. Been there, seen it happen. Fortunately, I wasn't the one that wrote the sproc in that case.

Well the same is true of just letting an application write it's own SQL 🙂

Bill Talada SSChampion Points: 11954 More actions · Answer 5

Maybe you want to do something similar to this script. I use it to log all parameters passed into a procedure for a short time to see how customers are calling certain reports.

-- ProcedureArgsLogger
-- create the table before doing logging
CREATE TABLE ProcedureArgsLogger
(
ProcedureName varchar(128) NOT NULL,
RunDT datetime NOT NULL,
Args xml NOT NULL
);
---- add this to proc to log
-- customize the SELECT for the args you want to capture
DECLARE @args xml;
SET @args =
(
SELECT
    @CaseCoordinatorKey AS CaseCoordinatorKey,
    @TestKey AS TestKey,
    @ProcedureKey AS ProcedureKey,
    @LabKey AS LabKey,
    @AccessionColorCodeTypeKey AS AccessionColorCodeTypeKey,
    @MaxRecordCount AS MaxRecordCount,
    @UserKey AS UserKey,
    @IncludeUndistributedCases AS IncludeUndistributedCases
FOR XML RAW
)
INSERT INTO dbo.ProcedureArgsLogger
SELECT OBJECT_NAME(@@procid), GETDATE(), @args
-----

sgmunson SSC Guru Points: 110640 More actions · Answer 6

ZZartin - Thursday, February 16, 2017 8:32 AM
sgmunson - Thursday, February 16, 2017 8:00 AM
SQLDave2015 - Thursday, February 16, 2017 12:27 AM
Store proc parameter can help with SQL injection and such. So it is a good practice to use store proc to insert instead of a simple SQL insert
_____________________________________________________________________________________________________
While using a sproc CAN help protect against SQL injection, it has to actually be coded to do so. If there's an nvarchar(max) parameter and nothing is done inside the sproc but append it to dynamic SQL, for example, then the mere use of a sproc hasn't done much for you in that regard. Been there, seen it happen. Fortunately, I wasn't the one that wrote the sproc in that case.
Well the same is true of just letting an application write it's own SQL 🙂

Yep.... EXACTLY !!!

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

To insert 50 parameters data into a SQL table, which way is better (fast insertion)?

From a asp.net page, I'll get values for all parameters from text boxes, combo boxes.