Call Function From Stored Procedure
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
View Complete Forum Thread with Replies
Related Forum Messages:
How To Call A Userdefined Function Within A Stored
Hello All, How do i call a user defined function from within a stored procedure, I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string. That part works. declare @fullname varchar(400) @fullName=getFullName(@firstname,@lastname) As always thanks for all your input
View Replies !
System Stored Procedure Call From Within My Database Stored Procedure
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense. How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how? Thanks in advance
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
How To Call A Stored Procedure Using C#
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help. CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate )
View Replies !
How To Call A Stored Procedure In Asp.net
I have created a stored procedure only with an insert statement in sql server 2005. How can i call this stored procedure through code in ASP.NET page using vb. i want to pass one parameter that comes from a text box in asp.net page. my emailid is: g12garg@yahoo.co.in pls reply. Thank you Gaurav
View Replies !
Call SQL Stored Procedure
Greetings, Even though this may be not right place with this issue I would like to try! I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form. I need some help very badly or maybe a good sample of code that works in this issue is very welcome. Many thanks in Advance
View Replies !
Stored Procedure's Call
Hi there. My problem is: I have two stored procedures. 1. SELECT A FROM B 2. SELECT C FROM D WHERE A = EXEC First procedure The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets? P.S. I already tried to use return instead of select in the first procedure. Same result. Thank you
View Replies !
How To Call Stored Procedure In VC++
Respected Sir/MAm i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table. i want to call this strd procedure in the VC++ main.cpp file.... Could you please help me for the correct syntax code with example. Thank You. Upali
View Replies !
Stored Procedure Call
hi, i created a stored procedure and below is the code segment of it; create Procedure test1 @price as varchar(50) output, @table as varchar(50) = '' AS Declare @SQL_INS VarChar(1000) SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']' Exec (@SQL_INS) Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
View Replies !
Call Stored Procedure In Loop
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString); System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn); commChk.CommandType = System.Data.CommandType.StoredProcedure; commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString()); commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text); commChk.Connection.Open(); commChk.ExecuteNonQuery(); conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
View Replies !
Asynch Call DTS Or Stored Procedure 1.1
Hi, I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables. Those values are not needed immediately. The calculation takes between 20 or 30 minutes. Do you have any idea how to do it ? Thanks
View Replies !
How To Call A Webservice In A Stored Procedure
hi everyone , is it possible to create a storedprocedure that will call a webservice and do some operations which i have called in the webservice or is it possible to create a stored procedure that will call a DLL in which in return the DLL will call the webservice which has the same kind operations if anyone knows abt it pls do send me some links r samples(iam a beginner so pls do tell me somewhat clearly so that i can understand easily) Thanks in advance
View Replies !
Can I Asynchronously Call A Stored Procedure?
I have a stored proc that delivers a report. It takes 45-60 seconds to run (which is too long for a user to wait). What I would like to do is create a report table that contains the results and periodically update that. That way I can just query the results rather than run the long stored proc all the time (many times the data is unchanged since the last time the report was run, so why do it again). I need to update the table by periodically calling the big stored proc and making sure my data is fresh. I though at the end of various other stored procs, I would fire the stored proc to update the report table asynchronously, if possible. Can this be done? Thanks.
View Replies !
How To Call A Stored Procedure In A Scheduled Job
Hi All,I have a stored procedure.I need to create a scheduled job using that stored procedure.I went to Enterprise Manager -->Management--> Jobs-->New JobPropertiesIn the step tab, I can select db and put the codes.Instead of writing the code there, I want to call the stored procedurein the command box.How can I do that?System: MS SQL Server 2000I would highly appreciate your help.Thanks a million in advance.Best regards,mamun
View Replies !
Aborting CALL To Stored Procedure
HelloI am calling a stored procedure in a MSDE/SQLServer DB form within myVisual C++ 6.0 program along the linesCCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);WithDEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))It all works sweet as, but it can take a while and I want to let theuser abort it.Everything I've tried ends in tears.
View Replies !
Using A Variable To Call A Stored Procedure Name
Hello; I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC. I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure. So how do I refer to a procedures name using a variable? Here's my code; Function LieuBen() MyCalendar = CurrTSCalendar Call PopulateTmpFile("sp_DelTmpProctimesheetCalc") Call PopulateTmpFile("sp_PopTmpCalcLieuBen") End Function Function PopulateTmpFile(MySProc As Variant) Dim sp_PopulateTempOTTable As String Const DS = "SOS-1" Const db = "TIMS" Const DP = "SQLOLEDB" Dim objConn As New ADODB.Connection Dim objRs As New ADODB.Recordset Dim objComm As New ADODB.Command ConnectionString = "Provider=" & DP & _ ";Data Source=" & DS & _ ";Initial Catalog=" & db & _ ";Integrated Security=SSPI;" ' Connect to the data source. objConn.Open ConnectionString ' Set a stored procedure objComm.CommandText = MySProc objComm.CommandType = adCmdStoredProc Set objComm.ActiveConnection = objConn objConn.MySProc MyCalendar, objRs objConn.Close Set objRs = Nothing Set objConn = Nothing Set objComm = Nothing End Function
View Replies !
Call COM Object From Stored Procedure?
Is there any wasy to access a COM object from a Stored Procedure? With Microsofts, non-stop "COM, COM, Everywhere" mantra, I would think I should be able to talk to COM from Tansact-SQL, but I can't seem to figure a way yet. Any ideas...?
View Replies !
Error Within Stored Procedure Call
I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter): -- Decalre variables DECLARE @object INT DECLARE @hr INT DECLARE @property VARCHAR(255) DECLARE @return VARCHAR(255) DECLARE @src VARCHAR(255) DECLARE @desc VARCHAR(255) -- Create object EXEC @hr = sp_OACreate 'SQLActiveXTest.SQLActiveXTestClass', @object OUT -- Call method EXEC @hr = sp_OAMethod @object, 'getDesc', @return OUT -- Destroy object EXEC @hr = sp_OADestroy @object
View Replies !
Make A Call To AS/400 Stored Procedure From DTS
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View Replies !
Nested Stored Procedure Call
I would like to know how can I call a child stored procedure from a parent stored procedure. A child stored procedure will return a result set. Like, return Select * from tblA where colA=@value. @value will be passed from the parent SP. And in parent SP. I need to loop through the returned result set to do further processing. The reason I want to have a seperate child SP is that I will need similar functionality in few other SPs. Any other solution will be appreciated. Thanks in advance.
View Replies !
Call Stored Procedure In Table
Hi..... I have problem and I need your help I stored a procedure in the Projects Folder in my computer and I want to return the procedure result in a column inside table thank you
View Replies !
Can't Call Stored Procedure In SqlFunction
There is a SqlFunction in my SQL Server Project: [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)] public static SqlBoolean GetRelation(SqlGuid ID) { using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn); //SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn); /*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.UniqueIdentifier)); cmd.Parameters["@ID"].Value = ID;*/ using (SqlDataReader sdr = cmd.ExecuteReader()) { sdr.Read(); //... sdr.Close(); } conn.Close(); } } When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function." What does the error message mean? How could I correct it? Thanks for answering.
View Replies !
How Can I Use A Stored Procedure Call Inside The WHERE?
Hi folks I nead to call a stored procedure in a where statemene, but MSSQL dont like that. My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code ALTER PROCEDURE dbo.advsp_FilterRecordRights @RequesterGUID Char(20), @EntityGUID Char(20) AS BEGIN if not Exists(Select * from GUIDRightsH where GUIDRightsH.EntityGUID = @EntityGUID and GUIDRightsH.RequesterGUID = @RequesterGUID and GUIDRightsH.RecProp <> 0) Begin Return 1 end if not Exists(Select * from UsergroupMembers where UserGroupMembers.UsergroupGUID = @RequesterGUID and dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1) Begin Return 1 end Return 0 END
View Replies !
How To Call A Stored Procedure In SSIS
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table. Appreciate an early feedback. Qadir Syed
View Replies !
How To Call A C#.net Class From Stored Procedure
Hi, I would like to know how can I call a c#.net class from a stored procedure. I have a class file which will generate a pdf file from sql reporting services and mail that file. Now I want to call this class from a stored procedure. Is it possible, if so please send me the sample code. Thanks in advance Regards Babu
View Replies !
Problems With Stored Procedure Call
I have developed an application that interfaces with SQL 2000 via stored procedure calls. The execution time of a particular stored procedure takes approximately 2 seconds on average, but we see spikes every so often up to 150 ~ 200 seconds. The timing of the spikes are erratic and do not correlate with any blocking or maintenance job runs. We have reviewed the stored procedure and optimized it to the nth degree and still no improvement. Any suggestions on how I can track down what is causing the spikes?
View Replies !
How Do I Call A Stored Procedure In Vb2005
I have a sql2005 express database I am using in a vb2005 application. I have read a bunch and can't seem to find a clear (to me) example of how to call a stored procedure. I have a procedure that runs ok in the query builder, but don't have a clue about how to call it now that it is written. SO. call the database MYdatabase call the Stored procedure MYStoredproc. Any help? Dave PS: it dosen't require and values passed to it, its very simple.
View Replies !
How To Call A Parameterized Stored Procedure Within A Loop In ASP.NET
I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET. Scenario: I have a loop through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables. I can not use the following syntax: cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dbo.storedprocedurename" With cmd .Parameters.Add(New SqlClient.SqlParameter("@param1", paramValue1)) .Parameters.Add(New SqlClient.SqlParameter("@param2", paramValue2)) End With What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET? Thanks, Carlos
View Replies !
Call A Stored Procedure From A SELECT Statement
Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName, LastName, (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table. Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.
View Replies !
How To Call A Stored Procedure In An Insert Command
hi,i had a small doubt , i have a table xxx with two columns (a int,b int) and i have inserted 5 rows my query is to add the two colums using astored procedure and the result has to be displayed in an separatecolumn --this has to be done only stored procedures ---i know how tosolve the problem using computed columns conceptlike thiscreate table yyy(a int ,b int ,total as a+b)insert into yyy values (12,13)select * from yyyi need the answer using stored procedures---is it possiblepls help mesatish
View Replies !
HOW TO: Call A Parameterized Stored Procedure By Using ADO.NET And Visual C# .NET
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET andVisual C# .NETView products that this article applies to.This article was previously published under Q310070For a Microsoft Visual Basic .NET version of this article, see 308049.For a Microsoft Visual C++ .NET version of this article, see 310071.For a Microsoft Visual J# .NET version of this article, see 320627.This article refers to the following Microsoft .NET Framework ClassLibrary namespaces:System.Data.SqlClientSystem.Data.OleDbIN THIS TASKSUMMARYUse DataReader to Return Rows and ParametersUse the ExecuteScalar Method of the Command ObjectUse the ExecuteNonQuery Method of the Command ObjectREFERENCESSUMMARYThere are several ways to use ADO.NET to call a stored procedure andto get back return values and return parameters, including:Use a DataSet object to gather the returned rows and to work withthese rows in addition to the return values and the return parameters.Use a DataReader object to gather the returned rows, to move throughthese rows, and then to gather return values and return parameters.Use the ExecuteScalar method to return the value from the first columnof the results' first row with the return values and the returnparameters. This is most useful with aggregate functions.Use the ExecuteNonQuery method to return only the return parametersand the return values. Any returned rows are discarded. This is mostuseful for executing action queries.This article demonstrates the last three methods and uses both theSqlCommand and the OleDbCommand objects. Make sure that you copy onlythe code for the managed provider that you are using. If you are notsure which managed provider you should use, visit the followingMicrosoft Developer Network Web site:..NET Data Providershttp://msdn.microsoft.com/library/d...etproviders.aspIn each of the samples in this article, the parameters are added tothe Parameters collection of the Command object. When you use theSqlCommand object, you do not have add the parameters in anyparticular order, but the parameters must have the correct name. Whenyou use the OleDbCommand object, you must add the parameters in thecorrect order, and you cannot use the parameters by name.back to the topUse DataReader to Return Rows and ParametersYou can use the DataReader object to return a read-only, forward-onlystream of data. The information that the DataReader contains can comefrom a stored procedure. This example uses the DataReader object torun a stored procedure that has an input and an output parameter andthen moves through the returned records to view the return parameters.Create the following stored procedure on the server that is runningMicrosoft SQL Server:Create Procedure TestProcedure(@au_idIN varchar (11),@numTitlesOUT Integer OUTPUT)ASselect A.au_fname, A.au_lname, T.titlefrom authors as A join titleauthor as TA onA.au_id=TA.au_idjoin titles as Ton T.title_id=TA.title_idwhere A.au_id=@au_idINset @numTitlesOUT = @@Rowcountreturn (5)Create a new Visual C# .NET Windows Application project.Use the using statement on the System and the System.Data namespacesso that you do not have to qualify declarations in those namespaceslater in your code. Add this code to the top of the Form code module.Make sure to copy only the code for the provider that you havechosen.SQL Clientusing System.Data.SqlClient;OLE DB Data Providerusing System.Data.OleDb;Replace the code in the private Form_Load event with the followingcode:SQL ClientSqlConnection PubsConn = new SqlConnection("Data Source=server;integrated " +"Security=sspi;initial catalog=pubs;");SqlCommand testCMD = new SqlCommand("TestProcedure", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;SqlParameter RetVal = testCMD.Parameters.Add("RetVal", SqlDbType.Int);RetVal.Direction = ParameterDirection.ReturnValue;SqlParameter IdIn = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;SqlParameter NumTitles = testCMD.Parameters.Add("@numtitlesout", SqlDbType.VarChar, 11);NumTitles.Direction = ParameterDirection.Output ;IdIn.Value = "213-46-8915";PubsConn.Open();SqlDataReader myReader = testCMD.ExecuteReader();Console.WriteLine ("Book Titles for this Author:");while (myReader.Read()){Console.WriteLine ("{0}", myReader.GetString (2));};myReader.Close() ;Console.WriteLine("Number of Rows: " + NumTitles.Value );Console.WriteLine("Return Value: " + RetVal.Value);OLE DB Data ProviderOleDbConnection PubsConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=server;" +"integrated Security=sspi;initial catalog=pubs;");OleDbCommand testCMD = new OleDbCommand("TestProcedure", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;OleDbParameter RetVal = testCMD.Parameters.Add("RetVal", OleDbType.Integer);RetVal.Direction =ParameterDirection.ReturnValue;OleDbParameter IdIn = testCMD.Parameters.Add("@au_idIN", OleDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;OleDbParameter NumTitles = testCMD.Parameters.Add("@numtitlesout", OleDbType.VarChar, 11);NumTitles.Direction = ParameterDirection.Output;IdIn.Value = "213-46-8915";PubsConn.Open();OleDbDataReader myReader = testCMD.ExecuteReader();Console.WriteLine ("Book Titles for this Author:");while (myReader.Read()){Console.WriteLine ("{0}", myReader.GetString (2));};myReader.Close() ;Console.WriteLine("Number of Rows: " + NumTitles.Value );Console.WriteLine("Return Value: " + RetVal.Value);Modify the connection string for the Connection object to point to thecomputer that is running SQL Server.Run the code. Notice that the DataReader retrieves the records andthen returns the parameter values. You can use the Read method of theDataReader object to move through the returned records.The Output window displays the titles of two books, the return valueof 5, and the output parameter, which contains the number of records(2). Notice that you must close the DataReader in the code to see theparameter values. Additionally, note that you do not have to movethrough all of the records to see the return parameters if theDataReader is closed.back to the topUse the ExecuteScalar Method of the Command ObjectYou can use the ExecuteScalar method of the Command object to retrieveparameter values. Additionally, ExecuteScalar returns the first columnof the first row of the stored procedure. This is most useful foraggregate functions as in the following example.Create the following stored procedure on the server that is runningSQL Server:Create Procedure TestProcedure2(@au_idIN varchar (11))As/* set nocount on */select count (T.title)from authors as A join titleauthor as TA onA.au_id=TA.au_idjoin titles as Ton T.title_id=TA.title_idwhere A.au_id=@au_idINReturn(5)Create a new Visual C# .NET Windows Application project.Use the using statement on the System and the System.Data namespacesso that you do not have to qualify declarations in those namespaceslater in your code. Add this code to the top of the Form code module.Make sure that you copy only the code for the provider that you havechosen.SQL Clientusing System.Data.SqlClient;OLE DB Data Providerusing System.Data.OleDb;Add the following code to the Form_Load event:SQL ClientstringstrCount;SqlConnection PubsConn = new SqlConnection("Data Source=server;integrated " +"Security=sspi;initial catalog=pubs;");SqlCommand testCMD = new SqlCommand("TestProcedure2", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;SqlParameter RetVal = testCMD.Parameters.Add("RetVal", SqlDbType.Int);RetVal.Direction = ParameterDirection.ReturnValue;SqlParameter IdIn = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";PubsConn.Open();strCount =testCMD.ExecuteScalar ().ToString() ;Console.WriteLine("Number of Rows: " + strCount );Console.WriteLine("Return Value: " + RetVal.Value);OLE DB Data Providerstring strCount;OleDbConnection PubsConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=server;" +"integrated Security=sspi;initial catalog=pubs;");OleDbCommand testCMD = new OleDbCommand("TestProcedure2", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;OleDbParameter RetVal = testCMD.Parameters.Add("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;OleDbParameter IdIn = testCMD.Parameters.Add("@au_idIN", OleDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";PubsConn.Open();strCount = testCMD.ExecuteScalar().ToString() ;Console.WriteLine("Number of Rows: " + strCount);Console.WriteLine("Return Value: " + RetVal.Value);Modify the connection string for the Connection object to point to thecomputer that is running SQL Server.Run the code. Notice that the ExecuteScalar method of the Commandobject returns the parameters. ExecuteScalar also returns the value ofcolumn 1, row 1 of the returned rowset. Therefore, the value ofintCount is the result of the count function from the storedprocedure.back to the topUse the ExecuteNonQuery Method of the Command ObjectThis sample uses the ExecuteNonQuery method to run the query and toreturn the parameter values. ExecuteNonQuery also returns the numberof records that are affected after the query runs. However,ExecuteNonQuery does not return any rows or columns from the storedprocedure.The ExecuteNonQuery method is most useful when you use INSERT, UPDATE,or DELETE statements if you only have to know how many rows arechanged. In a stored procedure in which you are using only a SELECTstatement, you receive -1 because no rows are affected by the query.Create the following stored procedure on the computer that is runningSQL Server:Create Procedure TestProcedure3(@au_idIN varchar (11),@au_fnam varchar (30))As/* set nocount on */Update authors set au_fname = @au_fnamwhere au_id = @au_idinreturn (5)Create a new Visual C# .NET Windows Application project.Use the using statement on the System and the System.Data namespacesso that you do not have to qualify declarations in those namespaceslater in your code. Add this code to the top of the Form code module.Make sure that you copy only the code for the provider that you havechosen.SQL Clientusing System.Data.SqlClient;OLE DB Data Providerusing System.Data.OleDb;Replace the code below the private Form1_Load event in the Form1 codemodule with the following code:SQL Clientstring strRowAffect;SqlConnection PubsConn = new SqlConnection("Data Source=server;integrated Security=sspi;" +"initial catalog=pubs;");SqlCommand testCMD = new SqlCommand("TestProcedure3", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;SqlParameter RetVal = testCMD.Parameters.Add("RetVal", SqlDbType.Int);RetVal.Direction = ParameterDirection.ReturnValue;SqlParameter IdIn = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;SqlParameter FnameIn = testCMD.Parameters.Add("@au_fnam", SqlDbType.VarChar, 30);FnameIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";FnameIn.Value = "Marjorie";PubsConn.Open();strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;Console.WriteLine("Number of Rows: " + strRowAffect );Console.WriteLine("Return Value: " + RetVal.Value);OLE DB Data Providerint intRowAffected;OleDbConnection PubsConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=server;" +"integrated Security=sspi;initial catalog=pubs;");OleDbCommand testCMD = new OleDbCommand("TestProcedure3", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;OleDbParameter RetVal = testCMD.Parameters.Add("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;OleDbParameter IdIn = testCMD.Parameters.Add("@au_idIN", OleDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;OleDbParameter FnameIn = testCMD.Parameters.Add("@au_fname", OleDbType.VarChar, 30);FnameIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";FnameIn.Value = "Marjorie";PubsConn.Open();intRowAffected = testCMD.ExecuteNonQuery();Console.WriteLine("Number of Rows affected: " + intRowAffected);Console.WriteLine(RetVal.Value);Modify the connection string for the Connection object to point to thecomputer that is running SQL Server.Run the code. The Output window displays the number of affected rows(intRowAffect) and the value of the return parameter.back to the topREFERENCESFor additional information, visit the following MSDN Web sites:Introduction to the .NET Framework Class Libraryhttp://msdn.microsoft.com/library/d...lasslibrary.aspRetrieving Data Using the DataReaderhttp://msdn.microsoft.com/library/d...tdatareader.aspback to the top
View Replies !
Call Stored Procedure Within SELECT Statement
Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it. USE NORTHWIND GO CREATE TABLE tbA ( Item int NOT NULL, Value int NOT NULL ) ON [PRIMARY] GO INSERT INTO tbA (Item, Value) SELECT 1, 10 UNION ALL SELECT 2, 5 UNION ALL SELECT 3, 2 GO CREATE PROCEDURE usp_SquareIt @iItem int AS declare @iValue int SELECT @iValue = Value FROM tbA SELECT @iValue * @iValue AS Result GO SELECT Item, EXECUTE usp_SquareIt Item AS Squared ---- can this be done FROM tbA GO DROP TABLE tbA GO DROP PROCEDURE usp_SquareIt GO Any thoughts? Mike B
View Replies !
|
TRACKER
