Stored Procedures

[si_owen]

Hi,

I have never used stored procedures before and I am experiencing
problems when I am trying to call my sp in my vb code.

My project is a web app in visual studio 2005.

Here is my stored procedure:

CREATE PROCEDURE sp_dataCredit
@tokenString int
@currentDateStr ing nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT TOP 1 PFWTRAN.dbo.Mai n.Variance, PFWTRAN.dbo.Mai n.AccVariance,
PFWTRAN.dbo.Mai n.TargetHours,
PFWTRAN.dbo.Tra nsactions.Date FROM PFWTRAN.dbo.Tra nsactions
INNER JOIN PFWTRAN.dbo.Mai n ON PFWTRAN.dbo.Tra nsactions.Date =
PFWTRAN.dbo.Mai n.Date AND
PFWTRAN.dbo.Tra nsactions.Token Number = PFWTRAN.dbo.Mai n.TokenNumber
WHERE (PFWTRAN.dbo.Tr ansactions.Toke nNumber = @tokenString) AND
(PFWTRAN.dbo.Tr ansactions.Date =
@currentDateStr ing)
END

GO


The code I am attempting to call the sp initially is the following:

Dim creditSqlConnec tion As New
SqlConnection(C onfigurationMan ager.AppSetting s("Plantime.Con nectionString2" ))
Dim creditSqlComman d As New SqlCommand("sp_ Datacredit",
creditSqlConnec tion)

.....

The code directly above does connect to the sp, but ask's for values
for the two parameters, which is as I expected. However after a few
attempts to add the two parameter values into the creditsqlcomman d
string, I have had plenty of error messages and no joy in solving
them.

Can anyone point me in the right direction on how to add the parameter
values to the call.

Thanks in advance,

Simon

[Andrew Morton]

Re: Stored Procedures

Note that you must supply the length of strings to agree with what's defined
in the database.

Dim sqlConn As New SqlConnection(s qlConnectionStr ing)
Dim sqlCmd As New SqlCommand(sqlC ommandString, sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure

Dim sqlParam As New SqlParameter("@ UserID", SqlDbType.Char, 36)
sqlParam.Value = myUserID
sqlCmd.Paramete rs.Add(sqlParam )

sqlParam = New SqlParameter("@ thingName", SqlDbType.NVarC har, 24)
sqlParam.Value = currentThingNam e
sqlCmd.Paramete rs.Add(sqlParam )

[Your bit of code stopped short of showing the part we need to see.]

Andrew

[si_owen]

Re: Stored Procedures

The next bit of code was just:

Dim creditSqlDataRe ader As SqlDataReader

creditSqlConnec tion.Open()
creditSqlDataRe ader = creditSqlComman d.ExecuteReader

While (creditSqlDataR eader.Read())
variance = creditSqlDataRe ader(0)
balance = creditSqlDataRe ader(1)
target = creditSqlDataRe ader(2)
End While

....this code already works, and i'm just updating my code so that the
sql query's are not directly in the code but are instead pre-compliled
in a stored procedure.


Thanks,

Simon

[Cor Ligthert[MVP]]

Re: Stored Procedures

si_owen,

You have to add the Sqlparameters to the command.

Something as

creditSqlComman d.parameters.ad d(New Sqlparameter("@ tokenString",1)
creditSqlComman d.parameters.ad d(New
Sqlparameter("@ currentDateStri ng","Hello")

Be aware however, that stored procedures are *not* compiled with exception
from a DB2 database.

Cor

[rowe_newsgroups]

Re: Stored Procedures

Note that you must supply the length of strings to agree with what's defined

in the database.

Nope.

This is perfectly valid:

cmd.Parameters. Add("@Param1", SqlDbType.VarCh ar).Value = "hello,
world"

I generally use it in my code since changing a db field length will
not require a recompile / rerelease of the application. The only thing
I have to change is the table schema and the stored procedure.

Thanks,

Seth Rowe [MVP]

[Spam Catcher]

Re: Stored Procedures

"Michel Posseth [MCP]" <MSDN@posseth.c omwrote in
news:#jMQvs9dIH A.5348@TK2MSFTN GP03.phx.gbl:

Or how would you define a extended stored procedure written in C++ (
2000 ) or .Net ( 2005 ) ( my experiences by the way regarding the
lather is that they are much slower as there "uncompiled " transact SQL
scounterparts )

From my experience, the benefits of SPs vs. inline SQL is minimal. I don't
see any speed difference? However, inline SQL is much easier to deploy and
maintain.
--
spamhoneypot@ro gers.com (Do not e-mail)

[rowe_newsgroups]

Re: Stored Procedures

However, inline SQL is much easier to deploy and

maintain.

That largely depends on environment. Especially with websites, having
to recompile and release your site just because you need to change a
procedure can be a real pain. Changing a stored procedure is not a big
deal and doesn't force the site to recompile, which can be a big bonus
when talking about a very frequented site (that 5 sec. delay could
mean 50 annoyed users). Also by having the SQL logic completely
encapsulated from the application you don't have to worry about
accidentally pushing out untested or incomplete changes.


Thanks,

Seth Rowe [MVP]