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 about parameters

Hi there,

i'm using HTTP to get XML from SQLserver 2000. I need to query the database with parameters. most of them are arrays.

i am however unable to get any data from my database using

WHERE name IN (@.param )

i then tried

exec (' ...

WHERE name IN (' + @.param + ')
')

and still nothing.

Does anybody know what i'm doing wrong?

Thank alot

Wim Horemans :confused:
p.s. in @.param there should be something like "jef, jan, gert, dunno"Your local variable should look like "'jef', 'jan', 'gert', 'dunno'"

and your where caluse should evaluate to

exec (' ...

WHERE name IN ('jef', 'jan', 'gert', 'dunno')
')

Newbie question - Syntax for passing parameters to a sub report

Hey, I've looked pretty much everywhere I could, but I am unable to
figure out the correct syntax for my problem.
I have a main report that I am running with a stored procedure.
I also have a sub report that I am running with the same stored
procedure.
The stored procedure has 3 parameters. (a session identifier, an
operator, and a language code)
Here are the steps I have taken so far...
- added the subreport to my main report
- right click on the subreport (in the layout view) and click
Properties; then select the parameters tab.
- not sure what to do here.
** NOTE reportName is clients and the subreport is has the reportName
subClients
I thought the syntax would be... (main report)
Parameter Name: SubClients!@.SessionID
Parameter Value: =Parameters!SessionID.Value
(subreport)
Parameter Name: @.SessionID
Parameter Value: =Parameters!SessionID.Value
This doesn't work. I get this following error message...
A parameter in the subreport ?SubClients' has the name
?SubClients!SessionID.Value'. Parameter names must be CLS-compliant
identifiers.
This error and I also got a few others - not sure what to do.
Please help me, someone, anyone.
Thank you in advance.
Ciao
RobClick on expression when you are mapping the parameters of the subreport.
That wil bring you to the expression builder.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rob" <buju8@.yahoo.com> wrote in message
news:3cff8d2d.0501131505.5862fa69@.posting.google.com...
> Hey, I've looked pretty much everywhere I could, but I am unable to
> figure out the correct syntax for my problem.
> I have a main report that I am running with a stored procedure.
> I also have a sub report that I am running with the same stored
> procedure.
> The stored procedure has 3 parameters. (a session identifier, an
> operator, and a language code)
> Here are the steps I have taken so far...
> - added the subreport to my main report
> - right click on the subreport (in the layout view) and click
> Properties; then select the parameters tab.
> - not sure what to do here.
> ** NOTE reportName is clients and the subreport is has the reportName
> subClients
> I thought the syntax would be... (main report)
> Parameter Name: SubClients!@.SessionID
> Parameter Value: =Parameters!SessionID.Value
> (subreport)
> Parameter Name: @.SessionID
> Parameter Value: =Parameters!SessionID.Value
> This doesn't work. I get this following error message...
> A parameter in the subreport 'SubClients' has the name
> 'SubClients!SessionID.Value'. Parameter names must be CLS-compliant
> identifiers.
>
> This error and I also got a few others - not sure what to do.
> Please help me, someone, anyone.
> Thank you in advance.
> Ciao
> Rob

Newbie Question

I want to load a dropdown list based on another dropdown selection.Iam not sure how to do this.

Both are report parameters.Based on one parameter i need to load another parameter.

Can someone help me.

Thanks in advance

mahalaks.

hello,

I believe in the properties for the drop down, you can define a constraint (drop down), which is where you set that up.

Brian

|||

Both dropdown boxes are report parameters.

Based on the report parameters dropdown boxes are coming.

No properties can be set for this.

Newbie question

Hi all,
I need an autonumber field in a table.
I set the following parameters for the field:
data type - int,
identity - yes (not for replication)
identity seed - 1
identity increment - 1
Is this the right thing to do?
TIA
CSharpHi,
You are right.
Thanks
Hri
MCDBA
"CSharp" <smitha@.asianetindia.com> wrote in message
news:OMXoyYuFEHA.3180@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I need an autonumber field in a table.
> I set the following parameters for the field:
> data type - int,
> identity - yes (not for replication)
> identity seed - 1
> identity increment - 1
> Is this the right thing to do?
> TIA
> CSharp
>

Newbie Parameter Problem

Hi, I have 3 parameters on my form. StartDate (datetime), EndDate (datetime) and CompanyName(string). The default values are: StartDate (Non-queried) 1-1-2005, EndDate (Non-queried) 1-1-2008, CompanyName (From query) DataSetBelow, Value field (AccountFamily):

SELECT DISTINCT AccountFamily
FROM CallDataRecords

The table on the form contains the following DataSet:

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @.CompanyName
AND ModifiedOn
BETWEEN @.StartDate AND @.EndDate

The error I get is: "Query execution failed for data set (one directly above)".

"Must declare the scalar variable "@.CompanyName".

Can anybody shed light please?

Thanks, Dan

I believe you have to declare the variable first and then use it the query..

DECLARE @.CompanyName nvarchar(25)

--Initilize the declared variable

SELECT DISTINCT @.CompanyName = AccountFamily
FROM CallDataRecords

-- use it

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @.CompanyName
AND ModifiedOn
BETWEEN @.StartDate AND @.EndDate

Hope this helps.....

|||

I tried that, but I got the following error:

"The report parameter 'CompanyName' uses the field 'AccountFamily' in a data set reference, but the data set 'DistinctComanyName' does not contain that field".

I also tried editing the Dataset and adding in the parameters tab of the Dataset. However that doesn;t help either (?).

|||

Sorry, please ignore my last post. I fixed it by adding the parameters to the second dataset. (They were not defined).

Thanks!

|||

cool ... all the best

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 has questions...

SQL Server 7.00.623

1. Am I correct in assuming that the initialization parameters that Oracle places in a init.ora file are handled in SQL Server using registry keys? BOL describes how to create a user-defined configuration file based on the setup.iss file, but is this only for unattended installs?

2. With the version of SQL Server that I'm running, is there any reason for me to use isql at all, as opposed to osql? Is isql bundled with 7 simply to be backward compatable with 6.5, or am I missing something here? BOL mentions that isql does not support some of 7's features, otherwise are they the same utility?

3. Generally speaking, should SQL Server be left alone to dynamically manage memory, or should the db be initialized with pre-allocated memory? From what I've been able to gather on the Web (I've only been at this SQL Server stuff for 3 days now...), it seems it does a pretty good job of this itself. Any thoughts?

4. Also, with respect to the physical implementation of SQL Server, if anyone could briefly explain the similarities/differences between SQL Server's Transaction Log and Oracle's redo logs, that would be great. (for example, in SQL Server, if there is only one log, how does the db engine manage the completion/continuation of transactions during a chkpt...am I correct in assuming that there are multiple 'logs' within the one transaction log?). Any thoughts would be helpful...

Cheers,
Chris1. Probably - also called statrtup parameters and can be set in enterprise manager.

2. Yes - isql no longer used.

3. Leave it to manage memory itself unless it causes problems.

4. Every time an update is made entries are put in the log. It is just a circular table. The entries are marked as inactive when the transaction is committed and checkpointed. Several spids can be writing entries at the same time and these will be interleaved.

Get a copy of inside sql server 2000.
Even if you use v7 it's probably worth getting this as the database engine is mostly the same - just watch out for new features.|||more info..

1. Yes, you are correct. The start up parms can be set in EM and are stored in registry. You can start the server via the command line and override the startup parms in the registry.

2. The diffrence between isql and oslq are how they connect to the db. isql uses an older method DB-LIB while osql uses ODBC (OLE DB). You will find that your connection defaults are diffrent between the two, Books Online covers this rather well. In my shop We hve old code the uses DB-LIB to connect as well as OLE DB. When I change stored procedures I run unit tests through both types of connections.

3. I agree %110

4. can't add anything more.|||Thanks, fellas...

With respect to the Transaction Log, I'm beginning to think of it as a kind of hybrid of Oracle's Redo Logs and Rollback Segment. It seems to handle both logging and transactional activity. I'm sure it will all become clear either sooner or later...

Anyways, I was going to add in my first post, 'Please no reply that I RTFM...'. Sad, but true, there are no books available on this island that I've been able to find...

Cheers...|||IMHO, One of the first places you should look for answers is Microsoft's Books Online shipped with SQL Server aka TFM in RTFM. This is one of the best documents I have seen if you have some knowledge of SQL Server or RDBs. The second place to look is www.Google.com. Between the two I am rarely stumped for an answer.|||Paul -

I read you loud and clear, and agree with you completely. BOL is excellent, and of course Google is my very basic resource. I've been thrown into both Informix and SQL Server recently, and between the two I've been forced to forget that Utopia called Metalink. With respect to SQL Server, the forgetting shouldn't be too painful...

Newbie Datetime Parameter problem

Hi, I have setup start date and end date parameters, however when my select gets the results from between the dates, if the dates are both set to today, no results are produced. I think its because I need to add a day to the end date parameter. How can I do this?

Thanks, Dan

Dan,

What you'll need to do is add one to your end date, like you said. You can do that by updating your SQL coding to something like this:

where ....
and DateField between @.StartDate and dateadd(day, 1, @.EndDate)
...

Hope this helps.

Jarret

|||

Thats perfect.

Thank you.