Stored Procedures

  • Thread starter Thread starter si_owen
  • Start date Start date
S

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
@currentDateString 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.Main.Variance, PFWTRAN.dbo.Main.AccVariance,
PFWTRAN.dbo.Main.TargetHours,
PFWTRAN.dbo.Transactions.Date FROM PFWTRAN.dbo.Transactions
INNER JOIN PFWTRAN.dbo.Main ON PFWTRAN.dbo.Transactions.Date =
PFWTRAN.dbo.Main.Date AND
PFWTRAN.dbo.Transactions.TokenNumber = PFWTRAN.dbo.Main.TokenNumber
WHERE (PFWTRAN.dbo.Transactions.TokenNumber = @tokenString) AND
(PFWTRAN.dbo.Transactions.Date =
@currentDateString)
END

GO


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

Dim creditSqlConnection As New
SqlConnection(ConfigurationManager.AppSettings("Plantime.ConnectionString2"))
Dim creditSqlCommand As New SqlCommand("sp_Datacredit",
creditSqlConnection)

.....

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 creditsqlcommand
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
 
Note that you must supply the length of strings to agree with what's defined
in the database.

Dim sqlConn As New SqlConnection(sqlConnectionString)
Dim sqlCmd As New SqlCommand(sqlCommandString, sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

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

sqlParam = New SqlParameter("@thingName", SqlDbType.NVarChar, 24)
sqlParam.Value = currentThingName
sqlCmd.Parameters.Add(sqlParam)

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

Andrew
 
The next bit of code was just:

Dim creditSqlDataReader As SqlDataReader

creditSqlConnection.Open()
creditSqlDataReader = creditSqlCommand.ExecuteReader

While (creditSqlDataReader.Read())
variance = creditSqlDataReader(0)
balance = creditSqlDataReader(1)
target = creditSqlDataReader(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
 
si_owen,

You have to add the Sqlparameters to the command.

Something as

creditSqlCommand.parameters.add(New Sqlparameter("@tokenString",1)
creditSqlCommand.parameters.add(New
Sqlparameter("@currentDateString","Hello")

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

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

Nope.

This is perfectly valid:

cmd.Parameters.Add("@Param1", SqlDbType.VarChar).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]
 
Be aware however, that stored procedures are *not* compiled with exception
from a DB2 database.
Click to expand...

Well ,,, this depends on your definition i guess

And yes i have read the inside SQL server 2000 and 2005 , :-)

definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."

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 )

The point i try to make is ,, how would someone define "compiled" especially
when dealing with .Net and Java assemblys can we call those compiled ?


Just my thoughts

Michel
 
Michel-

AFAIK is there no difference in SQL by a stored sproc and a dynamic sproc.
Although a stored sproc is always the same and seems to work something
smoother in the SQL serve3r cache that is used.
(There is also a slight difference in processing time, as I have seen even
on a Jet database).

Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."
Click to expand...

-Cor
 
rowe_newsgroups said:
Nope.

This is perfectly valid:

cmd.Parameters.Add("@Param1", SqlDbType.VarChar).Value = "hello,
world"
Click to expand...

Is that a change since the 1.1 framework? Or does assigning the Value like
that automatically set the length? Or is there something else wrong with my
programming such that it works if I supply the length and assumes a length
of (IIRC) 1 if I don't?

Andrew
 
Is that a change since the 1.1 framework?

Could be, I don't really remember.
Or does assigning the Value like that automatically set the length?
Click to expand...

I don't think assigning the value has anything to do with it. Some of
my coworkers two-line the parameter adds, adding the parameter and
then setting the value, and it works fine for them.
Or is there something else wrong with my programming such that
it works if I supply the length and assumes a length of (IIRC) 1 if
I don't?
Click to expand...

Don't know, I can't say I ever noticed it assigning the wrong length
when you leave it off, but I'm definitely sure it doesn't trim the
field to 1 char, or else I'd have a funny looking web site :-)

Thanks,

Seth Rowe [MVP]
 
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 )
Click to expand...

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.
 
Is that a change since the 1.1 framework? Or does assigning the Value
like that automatically set the length? Or is there something else
wrong with my programming such that it works if I supply the length
and assumes a length of (IIRC) 1 if I don't?
Click to expand...

You can use cmd.Parameters.AddWithValue("@Param1", "asfdlkjasdlkfjas").

If the string is longer than the field, you'll get a truncation error.
 
However, inline SQL is much easier to deploy and
maintain.
Click to expand...

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]
 
Hello Spam Catcher


With this i meant extended stored procedures the one written in a prefered
..Net language or in SQL 2000 in C++ this are actuall assembly`s ( dll`s )
that are accessible as stored procedures , functions or triggers through
SQL server

when i refered the "uncompiled" transact SQL i actually meant a "normall"
stored procedure where AFAIK the "compiling" stands for the execution plan
beeing generated
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.
Click to expand...

Well actually a SP is when first called a lot slower as inline SQL , when
you call a SP multiple times it gets faster and faster ( when well written
and used apropriate ofcourse ) .

It is always good practice to test your data logic against several options
what works for me might not work for you , but fo me in general once a good
execution plan is made the stored procedures are much faster as inline sql


Regards

Michel
 
Michel-

I saw this on this page

"A stored procedure is compiled at execution time, like any other
Transact-SQL statement"

Maybe would I have better written it in another way.

"A stored procedure is not saved in its compiled version outside the cache,
the same as any other Transact-SQL statement"

As it is in your book in another way, you can give it in my idea to your
boss to use as fuel.

-Cor
 
Cor ,

First of all Cor maybe my initiall comment was understood different by you
as i intended it to be

"A stored procedure is not saved in its compiled version outside the
cache, the same as any other Transact-SQL statement"
Click to expand...

It is the execution plan that is eventually "compiled" and yes i know
this is also happening for inline T-SQL , however with a SP this can in the
case of a SQL batch and with optimizations much more efficient beside the
advantage of encapsulation security etc etc ... for wich alone i would
favor a SP

However my response to you was triggered by my personal question regarding
this comment of you
Be aware however, that stored procedures are *not* compiled with exception
from a DB2 database.
Click to expand...

I know you are right seen from the common perspective ( don`t know about
that DB2 thingy )

However when is something called then "Compiled" as our .Net / Java
assembly`s are JITTED we just create the SQL counterpart of an execution
plan
in my opinion,,,, i was just wondering how you thought about that

Also my remark about the Inside SQL server books was not intended in a
offensive way , i am really a fan of the series especially of Kalen Dalenay
as she writes in such a great style and gives somuch insight in how SQL
server works behind the scenes .


Regards

Michel Posseth












Cor Ligthert said:
Michel-

I saw this on this page

"A stored procedure is compiled at execution time, like any other
Transact-SQL statement"

Maybe would I have better written it in another way.

"A stored procedure is not saved in its compiled version outside the
cache, the same as any other Transact-SQL statement"

As it is in your book in another way, you can give it in my idea to your
boss to use as fuel.

-Cor


Michel Posseth said:
Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."
Click to expand...

I will show you were it is written

http://msdn2.microsoft.com/en-us/library/aa174792.aspx

I would say Cor buy yourself the Books inside SQL server





Cor Ligthert said:
Michel-

AFAIK is there no difference in SQL by a stored sproc and a dynamic
sproc.
Although a stored sproc is always the same and seems to work something
smoother in the SQL serve3r cache that is used.
(There is also a slight difference in processing time, as I have seen
even on a Jet database).

Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."

-Cor

"Michel Posseth [MCP]" <[email protected]> schreef in bericht
Be aware however, that stored procedures are *not* compiled with
exception from a DB2 database.


Well ,,, this depends on your definition i guess

And yes i have read the inside SQL server 2000 and 2005 , :-)

definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."

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 )

The point i try to make is ,, how would someone define "compiled"
especially when dealing with .Net and Java assemblys can we call those
compiled ?


Just my thoughts

Michel






"Cor Ligthert[MVP]" <[email protected]> schreef in bericht
si_owen,

You have to add the Sqlparameters to the command.

Something as

creditSqlCommand.parameters.add(New Sqlparameter("@tokenString",1)
creditSqlCommand.parameters.add(New
Sqlparameter("@currentDateString","Hello")

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

Cor
Click to expand...
Click to expand...
Click to expand...
 
Michel-

I have the idea that you are now at a company which has a lot of (in past
called) batch processing which is probably still a main purpose for large
computers on banks and I think you are now at a likewise company.

In your former job there was probably much more real time processing with
many short processes at the database server.

I think that this is also a reason how you should do your executions on your
database server.

I hope that I did understand you well.

-Cor

Michel Posseth said:
Cor ,

First of all Cor maybe my initiall comment was understood different by you
as i intended it to be

"A stored procedure is not saved in its compiled version outside the
cache, the same as any other Transact-SQL statement"
Click to expand...

It is the execution plan that is eventually "compiled" and yes i know
this is also happening for inline T-SQL , however with a SP this can in
the case of a SQL batch and with optimizations much more efficient
beside the advantage of encapsulation security etc etc ... for wich alone
i would favor a SP

However my response to you was triggered by my personal question regarding
this comment of you
Be aware however, that stored procedures are *not* compiled with exception
from a DB2 database.
Click to expand...

I know you are right seen from the common perspective ( don`t know
about that DB2 thingy )

However when is something called then "Compiled" as our .Net / Java
assembly`s are JITTED we just create the SQL counterpart of an execution
plan
in my opinion,,,, i was just wondering how you thought about that

Also my remark about the Inside SQL server books was not intended in a
offensive way , i am really a fan of the series especially of Kalen
Dalenay as she writes in such a great style and gives somuch insight in
how SQL server works behind the scenes .


Regards

Michel Posseth












Cor Ligthert said:
Michel-

I saw this on this page

"A stored procedure is compiled at execution time, like any other
Transact-SQL statement"

Maybe would I have better written it in another way.

"A stored procedure is not saved in its compiled version outside the
cache, the same as any other Transact-SQL statement"

As it is in your book in another way, you can give it in my idea to your
boss to use as fuel.

-Cor


Michel Posseth said:
Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."


I will show you were it is written

http://msdn2.microsoft.com/en-us/library/aa174792.aspx

I would say Cor buy yourself the Books inside SQL server





"Cor Ligthert[MVP]" <[email protected]> schreef in bericht
Michel-

AFAIK is there no difference in SQL by a stored sproc and a dynamic
sproc.
Although a stored sproc is always the same and seems to work something
smoother in the SQL serve3r cache that is used.
(There is also a slight difference in processing time, as I have seen
even on a Jet database).

Will you show me where it is written this?
definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."

-Cor

"Michel Posseth [MCP]" <[email protected]> schreef in bericht
Be aware however, that stored procedures are *not* compiled with
exception from a DB2 database.


Well ,,, this depends on your definition i guess

And yes i have read the inside SQL server 2000 and 2005 , :-)

definition of a SP "A stored procedure is a group of Transact-SQL
statements compiled into a single execution plan."

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 )

The point i try to make is ,, how would someone define "compiled"
especially when dealing with .Net and Java assemblys can we call those
compiled ?


Just my thoughts

Michel






"Cor Ligthert[MVP]" <[email protected]> schreef in bericht
si_owen,

You have to add the Sqlparameters to the command.

Something as

creditSqlCommand.parameters.add(New Sqlparameter("@tokenString",1)
creditSqlCommand.parameters.add(New
Sqlparameter("@currentDateString","Hello")

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

Cor
Click to expand...
Click to expand...
Click to expand...
 
With this i meant extended stored procedures the one written in a
prefered .Net language or in SQL 2000 in C++ this are actuall
assembly`s ( dll`s ) that are accessible as stored procedures ,
functions or triggers through SQL server
Click to expand...

I heard that CLR procedures are 10x slower than "regular" SPs. I was at a
SQL conference and they said to use CLR procedures only as a last resort?
 
Well i never benchmarked them , but it doesn`t suprise me :-)

Some of my co workers use CLR procedures , but i guess that they use them
because they feel more at home in VB as in T-SQL i haver rewritten a few of
there procedures in T-SQL procedures and they were significantly faster .

Another thingy i noticed today is that CLR Triggers always seem to work
asynchronous , while with Transact SQL you have the choice

Michel
 
Back
Top