SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);
param2.Value = 6;
SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);
param1.Value = 5;
SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);
param3.Direction = ParameterDirection.Output;
m_cmd.CommandType = CommandType.StoredProcedure;
m_cmd.Parameters.Add(param1);
m_cmd.Parameters.Add(param2);
m_cmd.Parameters.Add(param3);
m_cmd.ExecuteNonQuery();
This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.
What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?
Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:SqlCommand c = new SqlCommand("AddAuthor", myConnection);c.CommandType = CommandType.StoredProcedure; c.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 100));c.Parameters["@LastName"].Value = "Last"; c.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 100));c.Parameters["@FirstName"].Value = "First"; In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction. But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN @@IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code? Thanks for the help in advance. Happy 4th to all! : ) -- Curt
Hi, I was wondering if someone can tell me why my sub for calling stored procedures doesn't work.The string strFinFileId is not null. I keep getting the following error System.IndexOutOfRangeException: Total Here's my sub. '//get GrandTotal Protected Sub GetGrandTotal(ByVal finfileid As String) Dim myConnection As SqlConnection Dim connString As String Dim strFinFileId As String Dim strGrandTotal As Decimal strFinFileId = finfileid connString = ConfigurationManager.ConnectionStrings("PMOConnectionString1").ConnectionString myConnection = New SqlConnection(connString) 'you need to provide password for sql server myConnection.Open() Dim sql1 As String sql1 = "GetGrandTotal " & Convert.ToInt32(strFinFileId) Dim myCommand As SqlCommand 'Response.Write(sql1) 'Response.End() myCommand = New SqlCommand(sql1, myConnection) Dim reader As SqlDataReader = myCommand.ExecuteReader() While reader.Read() strGrandTotal = reader.Item("Total") LabelGrandTotal.Text = strGrandTotal 'Response.Write(strFinFileId) 'Response.End() End While reader.Close() myConnection.Close() End Sub Cheers Mark :)
I have a stored procedure written to update a table: The stored procedure has the following parameters: @Original_TypeID int, @Type_Desc varchar(35), @Contact_Name varchar(20), @Contact_Ad1 varchar(25), @Contact_Ad2 varchar(25), @Contact_City varchar(10), @Contact_Phone varchar(12), @Contact_Fax varchar(12), @Contact_Email varchar(35) And I want to call this procedure when the "update" button is clicked, my code is:Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)Dim myCommand As SqlCommandDim UpdteParam As New SqlParameter() myConnection.Open()myCommand = New SqlCommand("[dbo].[sp_Update_Types]", myConnection) myCommand.CommandType = CommandType.StoredProcedure ????????????????????????myCommand.Parameters.Add(UpdateParam)
???????????myCommand.ExecuteNonQuery() myConnection.Close() End Sub
Can you please help me with the ????? areas? I don't know how to pass the parameters into the procedure, and also the second set of ??? s, I'm not sure if I am executing the command correctly. Thank you.
I'm relatively new to SQL Server and I was wondering if there is any way to call a DLL from a regular stored procedure? I believe I've read that the extended stored procedure is a DLL and therefore should be able to call another DLL. Can this also be verified? We're looking to speed up some sections of Visual Basic code which does some heavy I/O work. I've also asked this once before but didn't get an absolute answer - can you write the extended stored procedures in Visual Basic or is C/C++ the only language supported right now?
Hi AllI was wondering if there is a way to call a stored procedure from insideanother stored procedure. So for example my first procedure will call asecond stored procedure which when executed will return one record and iwant to use this data in the calling stored procedure. Is this possible ?Thanks in advance
Is it possible to have a stored procedure in database A while callingit from database B and have it manipulate the tables in database B(whatever the calling database happens to be)?We have a large-scale app that uses many complex stored procedures,and as of now, we're copying the SPs to every new database that iscreated, and it will soon become a nightmare for propagating updatesand fixes. We'd like to keep a master set of the SPs in one DB and"use" them from other DBs so that they only query data and manipulatetables in the calling DB. I hope someone has some suggestions. Thanks.
How can we call Stored Procedure inside any SQL Statement For Example. If we have procedure name sprocCurrentPriority select * from tablename where colmunname = exec sprocCurrentPriority
I'm using reporting services to build a report and I plan on using a stored procedure to gather my data for the report. My question is:
I know it's possible to call a stored procedure from a stored procedure, both within the same dB and in different dB's, but is it possible to call a SP that's on a different server? My gut says "definitely not. at least not easily." But I wanted to see if anyone had any thoughts on this before I pursue a different course of action.
I am currently developing web pages based on data pulled from SQL Server with ASP. I've mastered the art of SELECTing, INSERTing, etc, but want to know if it is possible to run STored Procedures from a page by clicking on a button on a particualr web page.
We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.
Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:
In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
I have created a cursor using the following type of syntax:
DECLARE MyCursor CURSOR FOR SELECT ID FROM tblEmployees OPEN MyCursor BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor
Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
I writte a stored procedure for username , password .... How can i call that stored procedure to verify the username & password then if both match in the database redirected to next page..? in asp with c#.net back end programming
Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application. I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP). On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value: ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem. ThanksPhil
Hi, all I'm using Sql server 2000 I want to make select statement dynamically and return table using function. in sp, I've done this but, in function I don't know how to do so. (I have to create as function since our existing API..)
Following is my tials... 1. alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec spTest @from, @to GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: " ..... * EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec master..xp_msver GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
Setup: I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states. There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem: I have not yet figured out how to call the dll from the stored procedure. Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help.
The linked server was not created When I executed the query. However, I was able to create the linked server when I executed this query(batch 2) alone. Is there any limitation of SP which I am not aware of?
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager] -- Add the parameters for the stored procedure here @StartTicks bigint, @EndTicks bigint, @TimeDiff decimal(9,2), @NumRows int OUTPUT
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
rst.MoveFirst Do While Not rst.EOF lstEntity.AddItem (rst.Fields(0)), 0 'lstEntity.AddItem (rst.Fields(1)), 1 rst.MoveNext Loop
The Stored Proc is as follow:
Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[sp_DefaultEntityData] AS BEGIN
SET NOCOUNT ON;
SELECT tblEntities.[Name], tblEntities.PrimaryKey FROM tblEntities ORDER BY tblEntities.PrimaryKey;
END
The table contains 24 entries
As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data.
I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work.
Here are my Q's: 1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment? 2) How do I read the values into the listbox, without displaying the primary key in die box?
Thank you in advance! Any help is very much appreciated.
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example.....
Everytime after I restore any database on the sql server 6.5, I have to recompile all the stored procedures in that database.Is there any method to overcome this problem?
Trying to run a SQL Server 6.5 stored procedure via ADO 2.0 in Visual Basic 6. The stored procedure moves roughly 10000 records from one table to another within the same database. The procedure works just fine when run from a SQL editor such as I/SQL, but only transfers a few hundred records when it is executed from a VB application using ADO 2.0. The guilty code can be seen below. Any thoughts?
Public cnSQL As New ADODB.Connection Public qry As New ADODB.Command
With cnSQL .ConnectionString = ConnectStringSQL .ConnectionTimeout = 20 .Open End With
Set qry.ActiveConnection = cnSQL qry.CommandType = adCmdStoredProc qry.CommandTimeout = 120
I have a stored procedure on SQL/Server 2000 that inserts into a #temptable. When I execute it from Query Analyzer it works fine. When I execute it from an ASP using ADO it gives me an Open Error message. When I comment out the #temptable the ASP works fine. Can someone shed some light on this? Do I have to do an ADO opent on the #temptable?