I had created stored procedure at SQL Server 2000 and how do I call it via
ASP.Net using VB Language?
CREATE PROCEDURE STK As
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[STOCK_RESULT]
GO
CREATE TABLE [dbo].[STOCK_RESULT] (
[StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [decimal](18, 4) NULL ,
[weight] [decimal](18, 4) NULL ,
) ON [PRIMARY]
GO
INSERT INTO STOCK_RESULT (StockNum,qty,weight)
select StockNum,sum(qty) as qty,sum(weight) as weight
from INVENTORYLEDGER
where Julian_Date <= 101001
group by StockNum
order by StockNum
go
Will stored procedure faster than SQL statement via ADO.Net?
Please advise.
Many thanks. 6 2297
Hi Sam
Writing SQL stored procedure is faster than writing SQl statements in
ADO.NET since stored procedure will be compiled and optimized executed plan
will be create in sqlserver for sp(stored proc) and more over maintenance
will be less comparing to writing sql in ado.net
to call sp from ado.net u need to created sql command object with command
type as stored procedure
sqlcommand sqlcmd = new sqlcommand("sp_name",slqconnection)
"Sam" wrote: I had created stored procedure at SQL Server 2000 and how do I call it via ASP.Net using VB Language?
CREATE PROCEDURE STK As
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[STOCK_RESULT] GO
CREATE TABLE [dbo].[STOCK_RESULT] ( [StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [qty] [decimal](18, 4) NULL , [weight] [decimal](18, 4) NULL , ) ON [PRIMARY] GO
INSERT INTO STOCK_RESULT (StockNum,qty,weight) select StockNum,sum(qty) as qty,sum(weight) as weight from INVENTORYLEDGER where Julian_Date <= 101001 group by StockNum order by StockNum go
Will stored procedure faster than SQL statement via ADO.Net?
Please advise.
Many thanks.
You'll also want to specify the CommandType of the SqlCommand as
CommandType.StoredProcedure.
sqlcmd.CommandType = CommandType.StoredProcedure
HTH
----------------
Dave Fancher http://davefancher.blogspot.com
"HARI PRASD BARU" <HA***********@discussions.microsoft.com> wrote in message
news:D0**********************************@microsof t.com... Hi Sam
Writing SQL stored procedure is faster than writing SQl statements in ADO.NET since stored procedure will be compiled and optimized executed plan will be create in sqlserver for sp(stored proc) and more over maintenance will be less comparing to writing sql in ado.net to call sp from ado.net u need to created sql command object with command type as stored procedure sqlcommand sqlcmd = new sqlcommand("sp_name",slqconnection)
"Sam" wrote:
I had created stored procedure at SQL Server 2000 and how do I call it via ASP.Net using VB Language?
CREATE PROCEDURE STK As
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[STOCK_RESULT] GO
CREATE TABLE [dbo].[STOCK_RESULT] ( [StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [qty] [decimal](18, 4) NULL , [weight] [decimal](18, 4) NULL , ) ON [PRIMARY] GO
INSERT INTO STOCK_RESULT (StockNum,qty,weight) select StockNum,sum(qty) as qty,sum(weight) as weight from INVENTORYLEDGER where Julian_Date <= 101001 group by StockNum order by StockNum go
Will stored procedure faster than SQL statement via ADO.Net?
Please advise.
Many thanks.
And if using DataSet DataAdpater u can do this:-
Dim da As SqlDataAdapter = New SqlDataAdapter("Stored_Procedure",
connection)
*** Sent via Developersdex http://www.developersdex.com ***
Yes, sprocs are faster than ad hoc code. I have a problem with your sproc,
however, as I see no reason to drop and create a table on a regular basis. It
is preferable to delete the records or even truncate the table to dropping
and creating objects. You are actually incurring a bit of a penalty with the
drop, as the sproc cannot be fully optimized, as the object it is calling
cannot be compiled into the code.
I would also consider setting up a DTS job on SQL Server to empty and
populate the table on a regular basis, rather than using your ASP.NET app to
do it. I see no reason to write a lot of code for something that can be done
declarative and automated.
---
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Sam" wrote: I had created stored procedure at SQL Server 2000 and how do I call it via ASP.Net using VB Language?
CREATE PROCEDURE STK As
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[STOCK_RESULT] GO
CREATE TABLE [dbo].[STOCK_RESULT] ( [StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [qty] [decimal](18, 4) NULL , [weight] [decimal](18, 4) NULL , ) ON [PRIMARY] GO
INSERT INTO STOCK_RESULT (StockNum,qty,weight) select StockNum,sum(qty) as qty,sum(weight) as weight from INVENTORYLEDGER where Julian_Date <= 101001 group by StockNum order by StockNum go
Will stored procedure faster than SQL statement via ADO.Net?
Please advise.
Many thanks.
Sam -
The VB language would go something like this:
Try
Dim cnn as New SqlClient.SqlConnection("Your connection info in quotes")
Dim cmd as New SqlCommand("STK", cnn)
cmd.CommandType = CommandType.StoredProcedure
cnn.Open
cmd.ExecuteNonQuery
Catch ex as Exception
Label1.Text = "There was an error. " & ex.Source & " " & ex.Message
Finally
cnn.Close
cnn.Dispose
End Try
Use of stored procedures is also strongly advised for security reasons.
Hope this helps!
Sandy
"Sam" wrote: I had created stored procedure at SQL Server 2000 and how do I call it via ASP.Net using VB Language?
CREATE PROCEDURE STK As
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[STOCK_RESULT] GO
CREATE TABLE [dbo].[STOCK_RESULT] ( [StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [qty] [decimal](18, 4) NULL , [weight] [decimal](18, 4) NULL , ) ON [PRIMARY] GO
INSERT INTO STOCK_RESULT (StockNum,qty,weight) select StockNum,sum(qty) as qty,sum(weight) as weight from INVENTORYLEDGER where Julian_Date <= 101001 group by StockNum order by StockNum go
Will stored procedure faster than SQL statement via ADO.Net?
Please advise.
Many thanks. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Luis |
last post by:
I'm using code similar to the following in an asp page to call a
stored procedure that does a select on a SQLServer 2000 table:
dim rs, SelectSql
SelectSql = "EXEC spSelectStuff @param = '" &...
|
by: Nashat Wanly |
last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft...
|
by: Brad H McCollum |
last post by:
I've looked through many suggestions and partial examples all over
this newsgroup and still am not coming up with anything that does
specifically what I'm wanting to accomplish.
I'm writing a VB...
|
by: Chris |
last post by:
Hello everyone
I want to call a DB2 7.2 stored procedure via ADO in VC++ 7.1
It has 4 parameters, the 1st and the 4th are OUTPUT and the others are INPUT
params.
My code looks like this:...
|
by: wdwedw |
last post by:
I have included all the source codes in the attached MyTest.zip
(http://www.codeguru.com/forum/attachment.php?attachmentid=11218)
There are three projects:
VBTestCOM project is a apartment...
|
by: Mo |
last post by:
Hi,
I have a webform which has vb.net code behind it and I would like it to
submit the entries in the fields into a sql server db using a stored
procedure. I have a central .vb file in my...
|
by: Amaryllis |
last post by:
I'm trying to call a CL which is located on our AS400 from a Windows
application. I've tried to code it in different ways, but I seem to
get the same error every time. Does anyone have any clue...
|
by: kd |
last post by:
Hi All,
How to debug a stored procedure?
Thanks,
kd
|
by: barmatt80 |
last post by:
I don't know if this is the right part of the forum. But.... I have been working all night trying to create a web service to call a stored procedure in sql server 2008. The stored procedure calls...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |