473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Optimize Store Procedure routine

Dear Group,
I have a store procedure with 12 input parameter and 2 output
parameters I'm wondering what's the best way to simplify the
following routine. I'd like to eliminate extra line of codes, do I
need to define input direction for each input parameter? I'm using
VS2005. I'd appreciate any useful feedback.

Thank You
Ian Nads
-------------------------------------------
Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@CustID", SqlDbType.Int))
Sqlparam.Direction = ParameterDirection.Input
Sqlparam.Value = CustomerID

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@inAddr1", SqlDbType.VarChar))
Sqlparam.Direction = ParameterDirection.Input
Sqlparam.Value = Address1

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@inAddr2", SqlDbType.VarChar))
Sqlparam.Direction = ParameterDirection.Input
Sqlparam.Value = Address2

...

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@ID", SqlDbType.int))
Sqlparam.Direction = ParameterDirection.Output

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@ID2", SqlDbType.int))
Sqlparam.Direction = ParameterDirection.Output

' excecute store procedure
SqlComm.ExecuteNonQuery()
ID = SqlComm.Parameters(13).Value
ID2 = SqlComm.Parameters(14).Value

-------------------------------------------
Jun 27 '08 #1
1 923

<in*****@hotmail.comwrote in message
news:62**********************************@s50g2000 hsb.googlegroups.com...
Dear Group,
I have a store procedure with 12 input parameter and 2 output
parameters I'm wondering what's the best way to simplify the
following routine. I'd like to eliminate extra line of codes, do I
need to define input direction for each input parameter? I'm using
VS2005. I'd appreciate any useful feedback.

Thank You
Ian Nads
-------------------------------------------
Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@CustID", SqlDbType.Int))
Sqlparam.Direction = ParameterDirection.Input
Sqlparam.Value = CustomerID

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@inAddr1", SqlDbType.VarChar))
Sqlparam.Direction = ParameterDirection.Input
Sqlparam.Value = Address1

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@inAddr2", SqlDbType.VarChar))
Sqlparam.Direction = ParameterDirection.Input
Sqlparam.Value = Address2

...

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@ID", SqlDbType.int))
Sqlparam.Direction = ParameterDirection.Output

Sqlparam = SqlComm.Parameters.Add(New
SqlClient.SqlParameter("@ID2", SqlDbType.int))
Sqlparam.Direction = ParameterDirection.Output

' excecute store procedure
SqlComm.ExecuteNonQuery()
ID = SqlComm.Parameters(13).Value
ID2 = SqlComm.Parameters(14).Value

-------------------------------------------
If you check in the Object explorer you will see Input is the default for a
SqlParameter so you will not need that line unless you are dealing with a
value other than Input

LS

Jun 27 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: mjuricek | last post by:
I'm having some problems to optimize my stored procedure (select statement with joins) What I'm trying to do is calculate total work. My situation: I have 3 tables I'm using -Input (char...
2
by: Tobias | last post by:
Hello, I am having problems creating stored procedures in DB2 on HP UX. I have set the DB2_SQLROUTINE_COMPILER_PATH and installed a C Compiler. I am receiving an SQL7032N error message, but as...
7
by: George | last post by:
Hi , I am getting the below error while i am invoking a Java stored procedure from another java stored procedure. SQL0751N Routine "" (specific name "") attempted to execute a statement...
1
by: DB_2 | last post by:
Hello, I created the following stored procedure. (Please ignore the logic in it; at this point it is not complete and I am trying to get the basic structure working first.) CREATE PROCEDURE...
1
by: Private Pyle | last post by:
DB2 version 8, fixpack 5. Aix 5.1.0.0 Using C for AIX compiler. I'm having a problem where I can create stored procedures but I'm getting -444 when I call them. It's a new environment for...
1
by: sshankar | last post by:
Hi, New to Stored procedure. Basically just installed DB2 v8.1.0.36 Was trying to build a stored procedure.. It is giving following error.. Looks like some error related to configuration...
1
by: arunkv444 | last post by:
give me some tips to optimize the store procedure so that it take less time to execute
2
by: Ramchandra | last post by:
Hi, i am getting error while executing the store procedure db2 "CALL CRS.UPDATE_DEAD_RECORD2(')" SQL0444N Routine "*_RECORD2" (specific name "SQL080221165103852") is implemented with code in...
1
by: rajpar | last post by:
Environment: Solaris (client + server) db2 version 7.2 latest fixpak (DB2 v7.1.0.111", "s050516" and "U803330") Compiler: gcc Here is my SP code executed on the client: CREATE PROCEDURE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.