How To Call A Stored Procedure In T-SQL And Pass It To A Cursor
Hi,
I have a kind of problem. In SQL Server I have a stored procedure ressembling this:
Code Block
ALTER PROCEDURE procedure1
(
@param int
)
SELECT * FROM table WHERE param = @param
Now I want to call this procedure and pass it to a cursor. We all know you can do this:
Code Block
DELCARE cursor1 CURSOR for
SELECT * FROM table WHERE param = @param
.. , but I want something like this:
Code Block
DECLARE cursor1 CURSOR for
EXEC procedure1 @param
Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant.
I have also tried something like this:
Code Block
ALTER PROCEDURE procedure1
(
@param int
)
SELECT @test = id FROM table WHERE param = @param
RETURN @test
ALTER PROCEDURE procedure2
(
@param int
)
DECLARE @var varchar(100)
EXEC @var = procedure1 @param
But then it returns always 0.
So is there a way to pass a procedure's select to a cursor?
Thanks in advance
Stevevil0
View Complete Forum Thread with Replies
Related Forum Messages:
Call SSIS Package From Stored Procedure And Pass Parameter
I am new to the SSIS. For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is: dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test' Does anyone know, how do I do the similar thing from SSIS environment. 1) How to call a SSIS package from Stored Procedure? 2) How do I pass parameter to the SSIS package? Thanks everyone.
View Replies !
How To Call A Procedure While Declaring A Cursor
HI, WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :- DECLARE CUR_NAME CURSOR FOR SELECT * FROM CLEANCUSTOMER BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :- CREATE PROCEDURE MY_PROC AS SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CLEANCUSTOMER A INNER JOIN TRCUSTOMERPREFERENCE03JULY B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID ORDER BY B.INTPREFERENCEID WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :- EXEC MY_PROC BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :- I AM USING :- DECLARE CHK_CUR CURSOR FOR SELECT * FROM MY_PROC WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'." AND IF I USE :- DECLARE CHK_CUR CURSOR FOR EXEC MY_PROC WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'". AND IF I USE :- DECLARE CHK_CUR CURSOR FOR CALL MY_PROC WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. " IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE? HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR PLS HELP. I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW THANKS
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 !
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source? Something like this: CREATE PROCEDURE TestHardDisk AS BEGIN DECLARE CURSOR HardDisk_Cursor FOR Exec xp_FixedDrives -- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source OPEN CURSOR HardDisk_Cursor FETCH NEXT FROM HardDisk_Cursor INTO @Drive, @Space WHILE @@FETCH_STATUS = 0 BEGIN ... END END
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 !
How To Pass A Variable To The Stored Procedure?
Hi, i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3) My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens: comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details Maybe is my stored procedure wrong? Thanks T. Here is it:---------- ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN SET @iLoopNumber = 1 SET @amountr While (@iLoopNumber <= @amount) BEGIN INSERT INTO table(field1,...) VALUES (....)) SET @iLoopNumber = @iLoopNumber +1 End COMMIT TRAN
View Replies !
How To Pass Xml File To A Stored Procedure
hi,i am passing a xml file name to the stored procedure. the SP parses the file. but it is giving the error' INVALID AT THE TOP LEVEL OF THE DOCUMENT 'I expect this because of + and - in the xml file bafore the parent tags.how can i do the parser to eliminate these.
View Replies !
Can't Pass 0 In Stored Procedure Parameter
Hi I have an if clause in my code to add the final parameter value to send to the database. If Page.User.IsInRole("MICMS") Then cmdCheckUser.Parameters.Add("@C_ID", 0) Else cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID")) End If If the user is in the role, the error is triggered saying that @C_ID is expected by the stored procedure. If i then change the value from 0 to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.
View Replies !
How Do I Use Pass Data To/from Stored Procedure
Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob
View Replies !
Pass A Csv List To Stored Procedure
I would like to pass a list of ids to my stored proc for use in a statment like: SELECT pr.name where pr.id IN ('23,25,27') FROM profiles pr so that the list following the IN statement is a replaceable parameter. How do I declare the parameter in my stored procedure?
View Replies !
How Array Will Pass To Stored Procedure
I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.
View Replies !
How To Pass DateTime To A Stored Procedure
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName". Code Snippet public static string GetWithDate(string date) { string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); XmlDocument xmlDoc = new XmlDocument(); SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++); cmd.Parameters.Add(new SqlParameter("@date", date)); try { cmd.ExecuteReader(); } catch (Exception ex) { Console.WriteLine(ex.Message); } SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs if (conn != null) conn.Close(); return xmlDoc.InnerXml; } I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View Replies !
VB.NET Stored Procedure, Can't Pass Param
Hello, I have a VB.NET stored procedure as below: Code Snippet Partial Public Class StoredProcedures Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer) 'here I perform update statement using "paramInOut" passed form calling code ....... 'then I return value to the calling code paramInOut = 555 End Sub End Class Calling code specifies a parameter like this: Code Snippet Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int) param.Direction = ParameterDirection.InputOutput param.Value = 999 cmd.Parameters.Add(param) When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ? Any help would be appreciated. Thanks a lot,Fly.
View Replies !
Pass XML To Stored Procedure In SQL Server2000
Hi, I want to pass an xml file to stored procedure in SQL Server 2000 as a parameter. Can we pass the xml file path as a parameter? if not then how can it be done. I want to serialize data to XML and then pass it to a procedure to insert data. Kindly help ASAP. Thanks
View Replies !
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 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 !
|
TRACKER
