I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...
I need removing cursor in my table valued function with alternate code.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
[Code] ....
I'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedFor a new web project I am working on I wrote a couple of Stored Procs in C#. I have deployed them to the database, and can run them fine using SQL Mgmt Studio. The problem is that when I try and run them from my application, I get a 'The Stored Procedure 'ProcName' does not exist' error message. I orignally thought it might be a permissions issue, so I changed my connection string to connect as 'sa' and it still got the same error. I am using the Microsoft.Practices.EnterpriseLibrary to connect to the database. Any ideas on what I need to do to get this to work?
View 1 Replies View RelatedI need to call a DLL from with one of my stored procedures. How do I achieve this?
The DLL is currently on App server (Not on the SQL Server box). Do I have to register this DLL on my SQL server, create an extended stored proc and call it from my stored proc?
Please throw some light on this topic.
Thanks,
Varma
How can I find what is calling a stored proc in a database or on the server?
Currently a job runs daily that calls a couple of DTS packages. Somewhere in this process a stored proc is being called that pumps out several emails. BUT nowhere in the DTS or job can I find any reference to the stored proc.
I would like to know how to call a Stored Proc with in code. Have an SQL code but need data from a table that needs to be updated by a Stored Proc. Would need to call proc in the middle of code to get the correct data. Just not sure of what the statement looks like to call the proc. Thank you for your help. David (DHL)
View 9 Replies View RelatedHi,
In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:
Msg 16916, Level 16, State 1, Procedure SP2, Line 114
A cursor with the name 'C1' does not exist.
Yes, the cursor is of type GLOBAL. I am sure I miss something here ...
Any help is highly appreciated !
Thanks: Peter
Hello.
I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want).
Not I need that ID in another stored procedure and I can't get it out from the stored procedure.
exec @blabla = MyProc -- works well if I use return
exec MyProc @blabla -- works using OUTPUT keyword
But neither of these examples works with a CURSOR as the @blabla.
Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FOR exec MyProc
Thanks for any help!
Daniel Ronnqvist, Stockholm
Can I do something like
CREATE PROCEDURE ProcA
AS
DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT
END
GO
I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.
AB
---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot.
select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else
---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan)
---but Shaggy has sponsored 2 others
select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy
---I also want to get their information ID,fname,lname
---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain:
---Let's assume that we have an OAK Tree that has 4 main branches
---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible
--the way I have it now taking way too long
--because in within so many (do while loop)
TIA
Please pardon me, I could not find better layout to explain this.
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Hi!
How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.
Thanks.
I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.
I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.
Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.
Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.
Any help is very much appreciated.
Code Snippet
ALTER procedure [dbo].[sp_MyProc]
AS
EXEC msdb.dbo.sp_start_job @job_name = 'MyJob'
I'm trying to write a procedure that calls a job. If I execute it (calling it from adp) as a user that is not db_owner (I guess), I get the error: The Execute permission was denied on the object sp_start_job, database 'msdb', schema 'dbo'.
How can I resolve this problem?
Has anyone ever tried to use a cursor as an output variable to a stored proc ?
I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc
@parentId integer,
@outputCursor CURSOR VARYING OUTPUT
AS
BEGIN TRAN T1
DECLARE parent_cursor CURSOR STATIC
FOR
SELECT parentTable.childId, parentTable. parentValue
FROM parentTable
WHERE parentTable.parentId = @parentId
OPEN parent_cursor
SET @outputCursor = parent_cursor
DECLARE @childId int
DECLARE @parentValue varchar(50)
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END
CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
Thanks
Martin
I have a stored Procedure that is looping through multiple cursors.
It is never finding any records in any curosr that is using a local variable in the where clause...Help
Alter Procedure ProjectedIncome
As
SET ROWCOUNT 0
Declare
-- Date types
@startdate smalldatetime
,@enddate smalldatetime
,@ProjectedDate smalldatetime
,@termination smalldatetime
,@effectivedate smalldatetime
-- Integer
,@Nums int
,@nums2 int
,@ClientId int
,@AssetId int
,@ProductID int
,@Policies int
,@product int
,@Per int
,@Projected int
-- String
,@debugtext varchar(150)
,@productid2 varchar(15)
-- float
,@rate float
,@Cap float
--bit
,@Override bit
--Money
,@AnnualPremium Money
,@Value Money
,@Premium Money
,@PaymentAmount Money
--Doubles
,@PremCalc int
,@HoldPrem int
,@HoldCom int
,@CumBal int
,@CumPrem int
,@MonthlyPrem int
,@XBal int
,@CapPrev int
,@PremTier int
,@Incriment int
--Declare cursor for System Variables
DECLARE SystemVar_cur cursor for
SELECT ProjectionStartDate,ProjectionEndDate from SystemVariables
--Declare the Cursor for Asset Definitions
declare AssetDef_cur cursor for
SELECT termination,effectivedate,ClientID,AnnualPremium,A ssetID,ProductID,Policies from AssetDefinitions
--Declare cursor for CommisionDefinitions
declare CommisionDef_cur cursor for
Select a.product,a.per,a.cap,a.rate,a.value from CommisionDefinitions a where a.product = @ProductId2;
--Declare cursor for projections
declare projections_cur cursor for
Select a.override,a.premium,a.paymentamount from projections a where a.date = @ProjectedDate and assetid = @AssetId;
-- Select from the SystemVariables Table
OPEN SystemVar_cur
FETCH SystemVar_cur INTO @startdate,@enddate
CLOSE SystemVar_cur
DEALLOCATE SystemVar_cur
-- Open the AssetDefinition File and loop through
--
INSERT INTO debug_table VALUES('Open the Asset Cursor')
Open AssetDef_cur
Fetch AssetDef_cur INTO
@termination
,@effectivedate
,@clientId
,@AnnualPremium
,@assetId
,@ProductId
,@Policies
While @@fetch_status = 0
Begin-- begin AssetDefinitions Loop
--If Asset is not Terminated
If @termination IS NULL
BEGIN-- begin @termination IS NULL
SET @MonthlyPrem = (@AnnualPremium/12)
SET @debugtext = 'MonthlyPrem = AnnualPrem' + CAST(@AnnualPremium as Char) + '/12'
INSERT INTO debug_table VALUES(@debugtext)
If @effectivedate > @startdate
SET @ProjectedDate = @effectivedate
Else
SET @ProjectedDate = @startdate
-- end if
SET @PremCalc = 0
SET @CumBal = 0
SET @XBal = 0
SET @HoldCom = 0
-- Fetch the Projection Record
open projections_cur
fetch projections_cur INTO
@override,@premium,@paymentamount
If @@fetch_status = 0
BEGIN
IF @override = 1
BEGIN-- begin @override = 1
SET @CumPrem = @premium
SET @CumBal = @paymentamount
SET @HoldPrem = @CumPrem
SET @HoldCom = @CumBal
END-- end @override = 1
Else
SET @HoldPrem = @MonthlyPrem
END
CLOSE projections_cur
While @ProjectedDate <= @enddate
BEGIN-- begin While @ProjectedDate <= @enddate
SET @CapPrev = 0 --reset cap balance
SET @XBal = 0
SET @debugtext = 'Begin Get Commision Record For Product' + CAST(@productID as CHAR)
INSERT INTO debug_table VALUES(@debugtext)
SET @productid2 = @productid
SET @PremTier = @HoldPrem
---NOW Open the CommisionDef table
OPEN CommisionDef_cur
FETCH CommisionDef_cur INTO
@product,@per,@cap,@rate,@value
IF @@fetch_status <> 0
BEGIN
SET @debugtext = 'ERROR? ' + CAST(@@error as Char)
INSERT INTO debug_table VALUES(@debugtext)
END
WHILE @@fetch_status = 0
BEGIN-- begin While CommisionDef Fetch = 0
SET @debugtext = 'Found Commision Record' + CAST(@product as Char)
INSERT INTO debug_table VALUES(@debugtext)
If @Per = 0
BEGIN-- begin If @Per = 0
SET @Incriment = @Cap - @CapPrev
If @PremTier > @Incriment
SET @XBal = @XBal + (@Incriment * @Rate)
Else
BEGIN-- begin @PremTier > @Incriment
If @PremTier >= 0
SET @XBal = @XBal + (@PremTier * @Rate)
END-- end @PremTier > @Incriment
SET @debugtext = 'XBal ' + CAST(@XBal as CHAR(10))
INSERT INTO debug_table VALUES(@debugtext)
SET @CapPrev = @Cap
SET @PremTier = @PremTier - @Incriment
END-- end If @Per = 0
Else
BEGIN-- begin If @Per <> 0
SET @XBal = @value * @Policies / 12
SET @HoldCom = 0
SET @PremCalc = 0
SET @CumBal = @XBal
SET @debugtext = 'CumBal' + CAST(@CumBal as Char)
INSERT INTO debug_table VALUES(@debugtext)
SET @HoldPrem = @Policies
END-- end If @Per <> 0
FETCH CommisionDef_cur INTO
@product,@per,@cap,@rate,@value
END-- end While CommisionDef Fetch = 0
CLOSE commisionDef_cur
-- Fetch the Projection Record
open projections_cur
fetch projections_cur INTO
@override,@premium,@paymentamount
IF @@fetch_status = 0
BEGIN -- begin Projection Fetch = 0
IF @override = 1
SET @HoldCom = @CumBal
ELSE
-- If not overridden, set the fields to Update the projection File
BEGIN-- begin @override <> 1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100
SET @Premium = @HoldPrem - @PremCalc
UPDATE projections SET projected = @projected, premium = @Premium where assetid=@AssetID and date = @ProjectedDate
SET @HoldCom = @XBal
END-- end @override <> 1
END-- end Projection Fetch = 0
ELSE
BEGIN -- Begin Projection Fetch else
IF @@fetch_status = -1
BEGIN-- begin Projection Fetch = -1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100
SET @Premium = @HoldPrem - @PremCalc
SET @debugtext = '((xbal - holdcom)*100 + 0.5)/100 ' + CAST(@Xbal as char) + ' , ' + CAST(@holdcom as CHAR)
INSERT INTO debug_table VALUES(@debugtext)
SET @debugtext = 'Projection Record Not Found so Write it'
INSERT INTO debug_table VALUES(@debugtext)
--Projection record was not found so write it
SET @override = 0
INSERT INTO Projections
(AssetId,Date,Premium,Projected,Override,Payment,P aymentAmount)
VALUES(@AssetId,@ProjectedDate,@Premium,@Projected ,@override,0,0)
SET @HoldCom = @XBal
END-- end Projection Fetch = -1
END -- end Projection Fetch else
CLOSE projections_cur
SET @ProjectedDate = DateAdd("m", 1, @ProjectedDate)
SET @PremCalc = @HoldPrem
-- Fetch the Projection Record
OPEN projections_cur
FETCH projections_cur INTO
@override,@premium,@paymentamount
IF @override = 1
BEGIN-- begin @override = 1
SET @CumBal = @paymentamount
SET @HoldPrem = @HoldPrem + @CumPrem
END -- end @override = 1
ELSE
SET @HoldPrem = @HoldPrem + @MonthlyPrem
CLOSE projections_cur
END-- End the While ProjectedDate <=@enddate
END --End the If Termination is NULL
Fetch AssetDef_cur INTO
@termination
,@effectivedate
,@clientId
,@AnnualPremium
,@assetId
,@ProductId
,@Policies
END
CLOSE AssetDef_cur
DEALLOCATE AssetDef_cur
DEALLOCATE projections_cur
DEALLOCATE CommisionDef_cur
return
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9 mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0; // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();
All,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]
@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
And inside of aaJonTemp, I have the following :
NULL
here2
NULL
NULL
here2
NULL
How is this possible? If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
Thx
jonpfl
I have a problem in calling a procedure.
It has two input parameters and seven(7) output parms.
When I run it this way:
exec usp_List_Relationship_Users @relationship_id = 1851 ,
@status = 'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT
I get this error:
Server: Msg 119, Level 15, State 1, Line 10
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
But when I run it this way :
exec usp_List_Relationship_Users 1851 ,
'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT
I get the expected results. Why should I omit the name of the name of the input parameters? I don't know why I am getting this error if don't run by ommitting the name of the input params.
Any help regarding this matter is greatlt appreciated.
You can assume that all the variables are declared prior to executing the stored procedure.
Regards
Sushruth Nanduri.
I have a couple of questions that I hope someone might be able to provide some direction on.
1. Why can't you call a proc when the proc uses temp tables? I wrote a simple proc (select * from table) and the data subscription works perfect. I then take that same query and put it into a temp table and the data set can't be validated by the subscription, Why?
2. Why can't you pass a simple execute statement in a data subscription? (execute dbo.RptTakeTheLeadFLSDinnerCompetition @FiscalQuarter ='2007-Q3', @StoreRegionID = '9231', @StoreType = 'Full-Line Store') works perfect in SQL but once you try and validate in the data subscription is fails.
3. Another question, why if I dumb down the proc and the number of parameters does it work? Are there limitations on the number of parameters you can pass into a proc from a data driven subscription?
Basically, I want to declare a few variables dynamically and pass them into a execute statement that calls the proc. Which in turn runs the report.
Any ideas on where I seem to be going wrong?
Existing Stored Procedure, has been running well on SQL since 7.0.(but needed some tweaking to migrate to 2000).Now all of a sudden after installing SP4 of SQL 2000,this process slows down, and SQL Spotlight shows the number of locksjust climbing throughout the processing run.According to the MS Knowledge Base Articles on KeyLocks .. this was aproblem that was *fixed* in the service pack ... where as for me it isnow broken.Article ID: 260652PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"May Hold Locks Longer http://support.microsoft.com/kb/260652/Article ID: 828096FIX: Key Locks Are Held Until the End of the Statement for Rows ThatDo Not Pass Filter Criteria http://support.microsoft.com/kb/828096/Anybody else have this issue, or have any "eazy" solutions?The proc cursors thru a list and runs a proc on each item in the "worklist".This is an existing systemwith no plans to turn the process into a set oriented one,as is going away shortly.
View 4 Replies View RelatedIn SQL how can a cursor be opened to iterate the result set returnedfrom a stored proc-Rahul SoodJoin Bytes!
View 1 Replies View RelatedHi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB
View 1 Replies View RelatedAll -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
We had a scenario where we used to run the Process from front end thru application. on the back ground the the process call the SP & from there it calls the SSIS package then again to SP.
after SSIS package ran succesfully it will be updated on a table with sucess then call the SP & deletes the entry from the other table but in one scenario Package was success but the entry was not getting deleted as the process takes almost 2-3 hours loading 60 millions records. but the process was running in SQL 2008 but once we upgraded to SQL 2012 its not working for one application. its not returning any error as timeout also. we tried changing the server level setting for remote query time out also to 0 but no luck .