Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
Thanks
D Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>
|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.
> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.
> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.
> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting chang
e
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on parameters to stored procedure

Hi All,
I had posted this question in the vb.net news group and don't seem to be
getting anywhere. This question may be more apt for this group, I guess. I
have pasted the post below.
****************************************
********************
I am trying to pass parameters to a stored procedure from vb.net code and
fails with the error that the variable is not a parameter to the stored
procedure
Here is the vb.net code
----
--
command = New SqlCommand("sp_updateProducts")
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.Transaction = trans
command.Parameters.Add(New SqlParameter("@.pMacId",
SqlDbType.Char))
command.Parameters.Add(New SqlParameter("@.pProdDt",
SqlDbType.DateTime))
command.Parameters.Add(New SqlParameter("@.pProdInfo",
SqlDbType.VarChar))
command.Parameters(0).Direction = ParameterDirection.Input
command.Parameters(1).Direction = ParameterDirection.Input
command.Parameters(2).Direction = ParameterDirection.Input
command.Parameters(0).Value = machineID
command.Parameters(1).Value = updateDate
command.Parameters(2).Value = joinStr
command.ExecuteNonQuery()
----
--
Here is the stored procedure code:
----
--
CREATE PROCEDURE dbo.sp_updateProducts
(
@.pMachineId AS CHAR(6),
@.pProdDt AS DATETIME,
@.pProdinfo VARCHAR(4000)
)
AS
BEGIN
.....
.....
.....
END
GO
----
--
The error message occurs on ExecuteNonQuery() and says that @.pMacId is not a
prameter to the stored procedure sp_updateProducts
I may be missing something very naive! Could anybody suggest the cause of
the error?
Thanks
kd@.pMacId is not a parameter. Ther parameter is called @.pMachineId.
Do NOT use the "sp_" prefix for stored procs (unless you want to create
system procs in Master - something that I wouldn't recommend on a production
system).
"sp_" denotes a system proc and if you create procs with this name outside
Master they may not execute and their performance will suffer from recompile
s.
David Portas
SQL Server MVP
--|||Hi Kd -
The string you specify in the VB.Net call for the name of the parameter
should match the name of the parameter as specified in the stored
procedure.
In the VB.Net code you create a paramter called @.pMacId, but in the
procedure it's named @.pMachineId. Make sure they are given the same name.
BTW - considering changing your procedure name to something like
usp_UpdateProducts. With a prefix of sp_, SQL Server will look first to
the master database for the procedure - slowing your system down a bit.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
kd wrote:
> Hi All,
> I had posted this question in the vb.net news group and don't seem to be
> getting anywhere. This question may be more apt for this group, I guess. I
> have pasted the post below.
> ****************************************
********************
> I am trying to pass parameters to a stored procedure from vb.net code and
> fails with the error that the variable is not a parameter to the stored
> procedure
> Here is the vb.net code
> ----
--
> command = New SqlCommand("sp_updateProducts")
> command.Connection = connection
> command.CommandType = CommandType.StoredProcedure
> command.Transaction = trans
> command.Parameters.Add(New SqlParameter("@.pMacId",
> SqlDbType.Char))
> command.Parameters.Add(New SqlParameter("@.pProdDt",
> SqlDbType.DateTime))
> command.Parameters.Add(New SqlParameter("@.pProdInfo",
> SqlDbType.VarChar))
> command.Parameters(0).Direction = ParameterDirection.Input
> command.Parameters(1).Direction = ParameterDirection.Input
> command.Parameters(2).Direction = ParameterDirection.Input
> command.Parameters(0).Value = machineID
> command.Parameters(1).Value = updateDate
> command.Parameters(2).Value = joinStr
> command.ExecuteNonQuery()
> ----
--
> Here is the stored procedure code:
> ----
--
> CREATE PROCEDURE dbo.sp_updateProducts
> (
> @.pMachineId AS CHAR(6),
> @.pProdDt AS DATETIME,
> @.pProdinfo VARCHAR(4000)
> )
> AS
> BEGIN
> .....
> .....
> .....
> END
> GO
> ----
--
> The error message occurs on ExecuteNonQuery() and says that @.pMacId is not
a
> prameter to the stored procedure sp_updateProducts
> I may be missing something very naive! Could anybody suggest the cause of
> the error?
> Thanks
> kd
>|||kd
I think the problem is you are refering to @.pMacId as a parameter of the SP
but actually a name of parameter is @.pMachineId (see CREATE PROC ...)
Am I right?
"kd" <kd@.discussions.microsoft.com> wrote in message
news:C2C6C755-604B-4FED-B113-13D2F7D345C9@.microsoft.com...
> Hi All,
> I had posted this question in the vb.net news group and don't seem to be
> getting anywhere. This question may be more apt for this group, I guess. I
> have pasted the post below.
> ****************************************
********************
> I am trying to pass parameters to a stored procedure from vb.net code and
> fails with the error that the variable is not a parameter to the stored
> procedure
> Here is the vb.net code
> ----
--
> command = New SqlCommand("sp_updateProducts")
> command.Connection = connection
> command.CommandType = CommandType.StoredProcedure
> command.Transaction = trans
> command.Parameters.Add(New SqlParameter("@.pMacId",
> SqlDbType.Char))
> command.Parameters.Add(New SqlParameter("@.pProdDt",
> SqlDbType.DateTime))
> command.Parameters.Add(New SqlParameter("@.pProdInfo",
> SqlDbType.VarChar))
> command.Parameters(0).Direction = ParameterDirection.Input
> command.Parameters(1).Direction = ParameterDirection.Input
> command.Parameters(2).Direction = ParameterDirection.Input
> command.Parameters(0).Value = machineID
> command.Parameters(1).Value = updateDate
> command.Parameters(2).Value = joinStr
> command.ExecuteNonQuery()
> ----
--
> Here is the stored procedure code:
> ----
--
> CREATE PROCEDURE dbo.sp_updateProducts
> (
> @.pMachineId AS CHAR(6),
> @.pProdDt AS DATETIME,
> @.pProdinfo VARCHAR(4000)
> )
> AS
> BEGIN
> .....
> .....
> .....
> END
> GO
> ----
--
> The error message occurs on ExecuteNonQuery() and says that @.pMacId is not
a
> prameter to the stored procedure sp_updateProducts
> I may be missing something very naive! Could anybody suggest the cause of
> the error?
> Thanks
> kd
>|||Hi,
But, I thought @.pMacId is a value name, which could differ, in the call and
the definition, just like how it is with vb.net procedures and functions!
And thanks for the advice on the usage of "sp_"
kd
"David Portas" wrote:

> @.pMacId is not a parameter. Ther parameter is called @.pMachineId.
> Do NOT use the "sp_" prefix for stored procs (unless you want to create
> system procs in Master - something that I wouldn't recommend on a producti
on
> system).
> "sp_" denotes a system proc and if you create procs with this name outside
> Master they may not execute and their performance will suffer from recompi
les.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi David,
Changing the parameter name to @.pMachineId fixed the error.
Thanks
kd
"David Portas" wrote:

> @.pMacId is not a parameter. Ther parameter is called @.pMachineId.
> Do NOT use the "sp_" prefix for stored procs (unless you want to create
> system procs in Master - something that I wouldn't recommend on a producti
on
> system).
> "sp_" denotes a system proc and if you create procs with this name outside
> Master they may not execute and their performance will suffer from recompi
les.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Joe,
Thanks for the solution
kd
"Joe Webb" wrote:

> Hi Kd -
> The string you specify in the VB.Net call for the name of the parameter
> should match the name of the parameter as specified in the stored
> procedure.
> In the VB.Net code you create a paramter called @.pMacId, but in the
> procedure it's named @.pMachineId. Make sure they are given the same name.
> BTW - considering changing your procedure name to something like
> usp_UpdateProducts. With a prefix of sp_, SQL Server will look first to
> the master database for the procedure - slowing your system down a bit.
> HTH...
> Joe Webb
> SQL Server MVP
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
>
> kd wrote:
>|||Hi Uri,
Thanks for the solution
kd
"Uri Dimant" wrote:

> kd
> I think the problem is you are refering to @.pMacId as a parameter of the
SP
> but actually a name of parameter is @.pMachineId (see CREATE PROC ...)
> Am I right?
>
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:C2C6C755-604B-4FED-B113-13D2F7D345C9@.microsoft.com...
> --
> --
> --
> --
> a
>
>

Newbie Question

Hi,
I have a procedure that returns 5 different values. I want to run the procedure in another procedure and use those values in that procdure.

How? How? How?

CREATE Procedure get_Averages_EY @.Quality dec OUTPUT, @.Commitment dec OUTPUT,@.Change dec OUTPUT, @.Strategy dec OUTPUT, @.Leadership dec OUTPUT, @.Environment dec OUTPUT, @.id int

I want to use all of the output values in another procedure.

Thanks!!!Try this one.

create procedure proc1(@.id int,@.id2 int output,@.id3 int output)
as
set @.id2=@.id*2
set @.id3=@.id*10
go
create procedure proc2
as
declare @.id2 int,@.id3 int,@.id int
set @.id=3
exec proc1 @.id,@.id2 output,@.id3 output
select @.id,@.id2,@.id3
go
exec proc2

Newbie procedure problem

I have the following table (Student):

Student_id First_name Last_name Sex Title

and I have a procedure problem:

Write a procedure to enter a student's title for a particular record when the procedure is called.

Here is my attempt but it doesn't add anything to the title column:
CREATE OR REPLACE PROCEDURE TITLE(FNAME VARCHAR2, LNAME VARCHAR2, PERTITLE VARCHAR2) AS
BEGIN
UPDATE STUDENT
SET TITLE = PERTITLE
WHERE FIRST_NAME = FNAME AND LAST_NAME = LNAME;
END;

From my understanding of the question, I believe the title gets passed along with the first and second name of the person you want to add the title to. So, if anybody has any suggestions for my problem it would be great.
Jameswell in sql server it would be something like this, oracle or another database might have a slight different syntax.

Your FNAME and LNAME variables where set to varchar(2), not sure but that is a very short last name and first name. They will never get passed correctly if you do not allow enough spaces, these should be set to the length of the field in the tables.

CREATE PROCEDURE p_new_user @.FNAME VARCHAR(20), @.LNAME VARCHAR(25), @.PERTITLE VARCHAR(4) AS

UPDATE STUDENT
SET TITLE = @.PERTITLE
WHERE FIRST_NAME = @.FNAME AND LAST_NAME = @.LNAME
GO|||I forgot to say I am using Oracle 9i.|||I believe I have corrected an error I was making. When I was executing the procedure, I was typing the names in upper case when in the table they are stored with the first letter being uppercase and the rest lower case. Sorry for wasting space on the forum!

Newbie Procedure Problem

Hi there, I'm new to PL/SQL and have been given the following procedure:

create or replace procedure otime (hoursworked number) as
overtimehours number;
normalhours number;
message varchar2(30);
begin
normalhours := 35;
if hoursworked > normalhours then
overtimehours := hoursworked - normalhours;
message := 'Overtime Hours worked = ';
dbms_output.put_line(message);
dbms_output.put_line(overtimehours);
else
message := 'No Overtime!';
dbms_output.put_line(message);
end if;
end;

it compiles OK, but when I enter the command 'EXECUTE otime', I get he following error:

BEGIN otime; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OTIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using SQL *Plus on Oracle 9i on Windows XP.

I haven't created tables to use with this procedure as I wasn't sure if it
was necessary.

Also, how would I get the data from a column called hours in a table called
EMP, when using a procedure?

Many thanks,
James//1
I think execute works when you call a procedure from another procedure.

You may try this, save your procedure in file say for eg
procedure_otime.sql and then run at the sql prompt as

>start procedure_otime.sql;

or

>@.procedure_otime.sql;

to display errors use show errors" at sql prompt

//2

To get the data from a column called hours in a table called
EMP, when using a procedure

v_hours EMP.hours%TYPE; /* assuming table is created */
BEGIN
SELECT hours INTO v_hours FROM EMP;
DBMS_OUTPUT.PUT_LINE(v_hours);
END;|||Try 'exec otime 42', you must pass one parameter to your proc.

Originally posted by donnie_darko
Hi there, I'm new to PL/SQL and have been given the following procedure:

create or replace procedure otime (hoursworked number) as
overtimehours number;
normalhours number;
message varchar2(30);
begin
normalhours := 35;
if hoursworked > normalhours then
overtimehours := hoursworked - normalhours;
message := 'Overtime Hours worked = ';
dbms_output.put_line(message);
dbms_output.put_line(overtimehours);
else
message := 'No Overtime!';
dbms_output.put_line(message);
end if;
end;

it compiles OK, but when I enter the command 'EXECUTE otime', I get he following error:

BEGIN otime; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OTIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using SQL *Plus on Oracle 9i on Windows XP.

I haven't created tables to use with this procedure as I wasn't sure if it
was necessary.

Also, how would I get the data from a column called hours in a table called
EMP, when using a procedure?

Many thanks,
James|||Originally posted by ndu35
Try 'exec otime 42', you must pass one parameter to your proc.

I tried this and I received the following error:

BEGIN otime 42; END;

*
ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00103: Encountered the symbol "42" when expecting one of the following:
:= . ( @. % ;
The symbol ":=" was substituted for "42" to continue.

Then I entered 'execute otime(45) and received the following:

"PL/SQL procedure successfully completed."

Which isn't correct as it should produce a message.|||ok I seem to have sorted this problem now. I hadn't input the command 'SET SERVEROUTPUT ON' and it worked when I enter the command 'EXECUTE otime(45)'. Thanks for the help.

Newbie in SQL Server admin

Can you give me the step-by-step procedure to setup some alerts in SQL
2000 to get pager or email notification once the :

1- SQL Server (service) is down
2- Trans log is full"Frank" <soal6570@.yahoo.com> wrote in message
news:42601b2.0404061558.62fda9af@.posting.google.co m...
> Can you give me the step-by-step procedure to setup some alerts in SQL
> 2000 to get pager or email notification once the :
> 1- SQL Server (service) is down
> 2- Trans log is full

For #1, look at the Windows Service manager.

This has a "Recovery" tab, and the ability to run a custom program on
service failure.

...

Steven|||soal6570@.yahoo.com (Frank) wrote in message news:<42601b2.0404061558.62fda9af@.posting.google.com>...
> Can you give me the step-by-step procedure to setup some alerts in SQL
> 2000 to get pager or email notification once the :
> 1- SQL Server (service) is down
> 2- Trans log is full

1. SQL Server Agent can send an alert if the MSSQL process reports a
fatal error, but if it's down as well, it won't be able to alert you.
So you may want to look at it as a general Windows issue - if you
already have a Windows event monitoring system in place, then you can
use that to do something when the MSSQL services stop and start (by
default, they are set to auto restart). You can also use the Control
Panel Services applet to do something (run a script etc.) when a
service fails to start.

2. Email/pager notifications require SQL Mail:

http://support.microsoft.com/defaul...kb;EN-US;263556

After doing that, then you can use Enterprise Manager to create
whatever alerts you need. There is a demo alert already created for a
full log condition, so you can use it as a template.

Simon

Newbie help with dynamic SQL

I am creating a stored procedure that will perform a search against a
table. I am passing search parameters to the SP. If the user did not
select a value on the front end, then I am passing NULL into the SP for
that field. So my question is, what is the best practice to only search
on a field if a value is passed for the given field?

This is what I was thinking, but obviously this doesn't work:

@.vchrFieldOne VARCHAR (200) = NULL

SELECT ...
FROM ...
WHERE
0=0

AND
CASE @.vchrFieldOne
WHEN NULL THEN (0 = 0)
ELSE (vchrFieldOne = @.vchrFieldOne)
ENDMine would look something like this:

CREATE PROC pub_info2 @.vchrFieldOne varchar(200) = NULL
AS
If (@.vchrFieldOne is null)
BEGIN
-- select statement where value is not given
END
ELSE
BEGIN
-- select statement where value is given
END

Hope it helps,
Tony Sebion

"Erich93063" <erich93063@.gmail.com> wrote in message
news:1123175211.631764.144360@.z14g2000cwz.googlegr oups.com:

> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END|||Erich93063 (erich93063@.gmail.com) writes:
> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END

It doesn't work for several reasons.

1) WHEN NULL - is the same as "WHEN @.charFieldOne = NULL", but in SQL
NULL is never equal to anything, not even another NULL. NULL is a
unknown value, and any comparison with NULL yields the value UNKNOWN.
Correct is WHEN @.vcharFieldOne IS NULL.
2) The return value of a CASE expresssion is always an SQL Server data
type, and there is no boolean data type in T-SQL. Thus you cannot
have "THEN (0 = 9".

The normal way to write this is

AND (vchrFieldOne = @.vchrFieldOne OR @.vchrFieldOne IS NULL)

However, while this works as far as giving the correct result, the
performance can be unbearable. I have an article on my web site that
discusses a number of alternatives for dynamic searches,
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply. I tried it (At least what I think you meant) but
I get an error (Incorrect syntax near the keyword 'AND' and Incorrect
syntax near the keyword 'END'. Here is what I have:

SELECT ...
FROM...
WHERE 0=0

IF (@.vchrFieldOne IS NULL)

BEGIN
AND 0=0
END
ELSE
BEGIN
AND vchrFieldOne = @.vchrFieldOne
END|||Nice article. Thanks for sharing it.|||YES, I read your article and used your example for dynamic SQL and it
works PERFECTLY. THANKS!!!!!!!!!!!!!

newbie - Stored procedure

I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...One proc should do it. You can have parameters with default values, if you
so choose:
create proc MyProc
(
@.id int
, @.x char (5) = 'ALFKI'
)
as
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:415B2831-6626-457A-98D4-42ED64F2C7F7@.microsoft.com...
I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...

Newbie - Can I have a Timer for a SP?

I'd like to have a stored procedure run every n seconds. Not sure how to
accomplish. Any ideas are appreciated.
Regards,
MikeYou can create a job to execute the stored procedure. To create job open
Enterprise Manager. Expand your Servername. Then click on "Management" then
click "JOB". Right click and click "New Job" and then you can set the SQL
statement to execute that stored procedure and you can set the schedule (the
minimum is every 1 minute).
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:uMJK3xalDHA.1808@.TK2MSFTNGP09.phx.gbl...
> I'd like to have a stored procedure run every n seconds. Not sure how to
> accomplish. Any ideas are appreciated.
> Regards,
> Mike
>

newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.