Problem executing stored procedure to update all rows of a tab

Thanks Razvan for your help. As per your advise I have changed the stored
procedure as follows:
CREATE PROCEDURE usp_UpdatedPrices_para1
@.Type char(12),
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
Now I am trying to execute the stored procedure as:
exec usp_UpdatedPrices_para1 @.percent = 10
To the above command I am getting the following error message:
Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was not
supplied.
Do you have any further ideas for resolution. Thanks.
"Razvan Socol" wrote:

> Hello, Jack
> You can call the procedure like this:
> EXEC usp_UpdatedPrices_para DEFAULT, 1
> or:
> EXEC usp_UpdatedPrices_para @.Percent=1
> However, this will not get you the expected result, because your
> condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
> the default for the @.Type parameter (leave it to be NULL) and use a
> condition like this:
> WHERE Type=@.Type OR @.Type IS NULL
> Razvan
>When passing parameters to a stored procedure using the @.variable=value
syntax, you must explicity set a valure for all input parameters that don't
have a default value assigned in the procedure defenition; even if you want
it to be NULL.
Try
EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
Alternatively, you could define the procedure to use a default of NULL for
the variable @.Type
CREATE PROCEDURE usp_UpdatedPrices_para2
@.Type char(12)=NULL,
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
And then execute as
EXEC sp_UpdatedPrices_para2 @.Percent=1
"Jack" wrote:
> Thanks Razvan for your help. As per your advise I have changed the stored
> procedure as follows:
> CREATE PROCEDURE usp_UpdatedPrices_para1
> @.Type char(12),
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> Now I am trying to execute the stored procedure as:
> exec usp_UpdatedPrices_para1 @.percent = 10
> To the above command I am getting the following error message:
> Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was n
ot
> supplied.
> Do you have any further ideas for resolution. Thanks.
> "Razvan Socol" wrote:
>|||Thanks a lot Mark for your generous help. I tried executing both the
procedures and both worked great. Best Regards.
"Mark Williams" wrote:
> When passing parameters to a stored procedure using the @.variable=value
> syntax, you must explicity set a valure for all input parameters that don'
t
> have a default value assigned in the procedure defenition; even if you wan
t
> it to be NULL.
> Try
> EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
> Alternatively, you could define the procedure to use a default of NULL for
> the variable @.Type
> CREATE PROCEDURE usp_UpdatedPrices_para2
> @.Type char(12)=NULL,
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> And then execute as
> EXEC sp_UpdatedPrices_para2 @.Percent=1
>
> "Jack" wrote:
>

Problem debugging SQL stored procedures

Hi
I am having a problem debugging SQL stored procedures from VS.net.
With some connections it works fine but when I try to debug a stored
procedure in a database on my local machine it fails. When I right
click and select Step into stored procedure' VS.NET goes into run
mode but I don't get the yellow arrow and I am unable to step through
the code.
I'm new to SQL and I'm not sure what other information I can supply
that will help. The connections themselves are the same except with
one the server is on my machine and with the other the server is on my
local network. If anyone has any suggestions as to what I can try to
get this to work, or even better, if someone has seen this problem
before, any help will be gratefully appreciated!
Many thanks
Julia.
Julia
Do you mean by click on Server Explorer in .NET Project?
Are you getting aby error when you run the SP?
For me it works fine.
I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
an error trown by SQL Server?
or run this stored procedure in QA and debug it step by step
"Julia" <julia_beresford@.hotmail.com> wrote in message
news:6c293529.0407260531.28ba29cf@.posting.google.c om...
> Hi
> I am having a problem debugging SQL stored procedures from VS.net.
> With some connections it works fine but when I try to debug a stored
> procedure in a database on my local machine it fails. When I right
> click and select 'Step into stored procedure' VS.NET goes into run
> mode but I don't get the yellow arrow and I am unable to step through
> the code.
> I'm new to SQL and I'm not sure what other information I can supply
> that will help. The connections themselves are the same except with
> one the server is on my machine and with the other the server is on my
> local network. If anyone has any suggestions as to what I can try to
> get this to work, or even better, if someone has seen this problem
> before, any help will be gratefully appreciated!
> Many thanks
> Julia.
|||Julia
Hi Uri, in answer to your questions...
Do you mean by click on Server Explorer in .NET Project?[vbcol=seagreen]
Are you getting aby error when you run the SP?[vbcol=seagreen]
debug mode or when I right click on the stored procedure in Server
Explorer and select Step Into Stored Procedure'.
For me it works fine.[vbcol=seagreen]
EXCEPT those on localhost
I'd write TRY CATCH error handler in .NET with SQLExceptions to see
what is an error trown by SQL Server? or run this stored procedure in
QA and debug it step by step[vbcol=seagreen]
suggested but now I get the errors...
"Unable to start SQL debug session. Ensure sqldbreg2.exe and
proxy/stub DLL sqldbg.dll are registered on this machine."
And
"Attaching the T-SQL debugger to process [2104] StoredProcedures.exe'
on machine BWJULIA' failed. Error code 0x80004004."
...when I try to run the stored procedure with a breakpoint on it.
This is different to yesterday and I haven't changed anything (that
I'm aware of). I have registered sqldbreg2.exe and sqldbg.dll. I
also have the correct permissions set on the required folders for user
SQLDebugger.
Has anyone got debugging stored procedures to work successfully? The
behavior on my machine seems temperamental and I'm not sure if I need
to reinstall VS.NET.
Many thanks again for any help anyone can offer.
Regards
Julia Beresford.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<utL0eYxcEHA.3420@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Julia
> Do you mean by click on Server Explorer in .NET Project?
> Are you getting aby error when you run the SP?
> For me it works fine.
> I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
> an error trown by SQL Server?
> or run this stored procedure in QA and debug it step by step
>
> "Julia" <julia_beresford@.hotmail.com> wrote in message
> news:6c293529.0407260531.28ba29cf@.posting.google.c om...

Problem debugging SQL stored procedures

Hi
I am having a problem debugging SQL stored procedures from VS.net.
With some connections it works fine but when I try to debug a stored
procedure in a database on my local machine it fails. When I right
click and select Step into stored procedure' VS.NET goes into run
mode but I don't get the yellow arrow and I am unable to step through
the code.
I'm new to SQL and I'm not sure what other information I can supply
that will help. The connections themselves are the same except with
one the server is on my machine and with the other the server is on my
local network. If anyone has any suggestions as to what I can try to
get this to work, or even better, if someone has seen this problem
before, any help will be gratefully appreciated!
Many thanks
Julia.Julia
Do you mean by click on Server Explorer in .NET Project?
Are you getting aby error when you run the SP?
For me it works fine.
I'd write TRY CATCH error handler in .NET with SQLExceptions to see what is
an error trown by SQL Server?
or run this stored procedure in QA and debug it step by step
"Julia" <julia_beresford@.hotmail.com> wrote in message
news:6c293529.0407260531.28ba29cf@.posting.google.com...
> Hi
> I am having a problem debugging SQL stored procedures from VS.net.
> With some connections it works fine but when I try to debug a stored
> procedure in a database on my local machine it fails. When I right
> click and select 'Step into stored procedure' VS.NET goes into run
> mode but I don't get the yellow arrow and I am unable to step through
> the code.
> I'm new to SQL and I'm not sure what other information I can supply
> that will help. The connections themselves are the same except with
> one the server is on my machine and with the other the server is on my
> local network. If anyone has any suggestions as to what I can try to
> get this to work, or even better, if someone has seen this problem
> before, any help will be gratefully appreciated!
> Many thanks
> Julia.|||Julia
Hi Uri, in answer to your questions...
Do you mean by click on Server Explorer in .NET Project?[vbcol=seagreen]
Are you getting aby error when you run the SP?[vbcol=seagreen]
debug mode or when I right click on the stored procedure in Server
Explorer and select Step Into Stored Procedure'.
For me it works fine.[vbcol=seagreen]
EXCEPT those on localhost
I'd write TRY CATCH error handler in .NET with SQLExceptions to see
what is an error trown by SQL Server? or run this stored procedure in
QA and debug it step by step[vbcol=seagreen]
suggested but now I get the errors...
"Unable to start SQL debug session. Ensure sqldbreg2.exe and
proxy/stub DLL sqldbg.dll are registered on this machine."
And
"Attaching the T-SQL debugger to process [2104] StoredProcedures.exe'
on machine BWJULIA' failed. Error code 0x80004004."
...when I try to run the stored procedure with a breakpoint on it.
This is different to yesterday and I haven't changed anything (that
I'm aware of). I have registered sqldbreg2.exe and sqldbg.dll. I
also have the correct permissions set on the required folders for user
SQLDebugger.
Has anyone got debugging stored procedures to work successfully? The
behavior on my machine seems temperamental and I'm not sure if I need
to reinstall VS.NET.
Many thanks again for any help anyone can offer.
Regards
Julia Beresford.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<utL0eYxcEHA.3420@.TK2MSFTNGP12.phx.gbl
>...[vbcol=seagreen]
> Julia
> Do you mean by click on Server Explorer in .NET Project?
> Are you getting aby error when you run the SP?
> For me it works fine.
> I'd write TRY CATCH error handler in .NET with SQLExceptions to see what i
s
> an error trown by SQL Server?
> or run this stored procedure in QA and debug it step by step
>
> "Julia" <julia_beresford@.hotmail.com> wrote in message
> news:6c293529.0407260531.28ba29cf@.posting.google.com...