Stored Procedure Cursor Problem URGENT

Dec 14, 2000

Hi,

I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks

CREATE PROCEDURE [MYSP] AS

Declare @pdesc nvarchar(30)
Declare @ssc int
Declare @myid int
Declare @name nvarchar(30)

Declare new_cur CURSOR DYNAMIC FOR
SELECT ProductDescription, SubSubCatID
FROM C2000HPB
FOR UPDATE

Open new_cur
FETCH FROM new_cur INTO @pdesc, @ssc
While @@FETCH_STATUS = 0

BEGIN
Declare new_cur2 CURSOR DYNAMIC FOR
SELECT SubSubCatID, SubSubCategory FROM SSC
FOR READ ONLY

Open new_cur2
FETCH FROM new_cur2 INTO @myid, @name
While @@FETCH_STATUS = 0

BEGIN
IF PATINDEX ('@name%',@pdesc) = 0
Set @ssc = @myid
UPDATE C2000HPB
SET SubSubCatID = @ssc
FETCH NEXT FROM new_cur2 INTO @myid, @name

END

Close new_cur2
DEALLOCATE new_Cur2
FETCH NEXT FROM new_cur INTO @pdesc,@ssc
END
Close new_cur
DEALLOCATE new_Cur

View 1 Replies


ADVERTISEMENT

How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?

Oct 8, 2007

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 6 Replies View Related

Cursor With Stored Procedure

Aug 11, 2004

I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie:

CREATE PROCEDURE [dbo].[bsa_GetImportIDs]
(@FilterText varchar(1000))
AS

DECLARE @MySQL varchar(1000)

SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText

EXEC (@MySQL)
GO

Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie:

CREATE PROCEDURE [dbo].[bsa_PutLargeCase]
AS

DECLARE @CaseID uniqueidentifier
SET @CaseID = NewID()
Declare @ImportID uniqueidentifier

Declare curClient Cursor FAST_FORWARD for
SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!!

Open curClient
FETCH NEXT FROM curClient INTO @ImportID
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID

FETCH NEXT FROM curClient INTO @ImportID
END

CLOSE curClient
DEALLOCATE curClient

GO

How can I utilize my first stored procedure in the cursor of the second? ... or
Are there any other approaches that may be a better solution to what I am trying to accomplish?

Thanks in advance for any input.

View 2 Replies View Related

Out A Cursor From A Stored Procedure

Aug 17, 2000

hello!

any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor.
please help!

honey

View 1 Replies View Related

Stored Procedure Into A Cursor

Jul 20, 2005

Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance

View 2 Replies View Related

Stored Procedure Using A Declared Cursor

Nov 15, 2007

I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.

The doctors table has the following columns with specialism allowing NULL values

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

Any help would be greatly appreciated.

View 11 Replies View Related

How To Declare Cursor In Stored Procedure?

Jan 23, 2008

I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.

CREATE PROCEDURE STP_EMPSAL
@empno int,
@Employee_Cursor CURSOR VARYING OUTPUT
FOR SELECT empno FROM AdventureworksDW.dbo.emp
AS
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @empno;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE emp set sal= sal+ 2000 where
empno = @empno and comm is null
mgr='Scott';
FETCH NEXT FROM Employee_Cursor into @empno;
COMMIT;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

View 4 Replies View Related

Creating Cursor From Stored Procedure

Jun 20, 2006

Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky

View 12 Replies View Related

Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again

Jun 11, 2008

I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that  line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after
any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit.  (#tblSalesOrders is a temporary table which containsset of  records from orginal table )DECLARE @soNo1 INT
 DECLARE @CursorOrders CURSOR
SET @CursorOrders = CURSOR FAST_FORWARD FOR
select fldSoNo from #tblSalesOrders
declare @newSONO1 int OPEN @CursorOrders
FETCH NEXT FROM @CursorOrders INTO @soNo1
WHILE @@FETCH_STATUS = 0
BEGIN
----for each salesorder insert to salesorderline
insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;
 
 set @newSONO1=SCOPE_IDENTITY;
-------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert --------------------------
SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM
FETCH NEXT FROM @CursorOrders INTO @soNo1
END CLOSE @CursorOrders
DEALLOCATE @CursorOrders

View 2 Replies View Related

Receiving And Sending A Cursor With(in) A Stored Procedure

Feb 23, 2005

Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

Thanks for help on this,

Peter

View 14 Replies View Related

How To Call A Stored Procedure In T-SQL And Pass It To A Cursor

Dec 2, 2007

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 1 Replies View Related

Make A Dynamic Cursor In A Stored Procedure

Jul 9, 2006

I need im my aplication to meke a "Cursor" in a execution of a stored procedure.

For sample:

In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.

How can I do ???

My code:

Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat

Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)

Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux

Not working !!!!

View 1 Replies View Related

Problem When Invoking Stored Procedure With Cursor From Inside .net

Nov 7, 2007

Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,

View 1 Replies View Related

Cursor Works In Query Analyzer But Not In Stored Procedure

Mar 7, 2008



Hi i have a script works in sql query analyzer;


declare @id decimal


declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID
open mycur;

Fetch ABSOLUTE 30 from mycur into @id
close mycur;
deallocate mycur;

select @id
this script turns me a value.

i create a stored procedure from above script and its syntax is ok;
CREATE PROCEDURE SELECT_MyRow
AS
declare @cur cursor
declare @RowID decimal
set @cur = CURSOR SCROLL
for select myRowID from myTable order by myRowID
open @cur
Fetch ABSOLUTE 30 from @cur into @RowID
close @cur
deallocate @cur
select @RowID
GO

my c# code using stored procedure is below;






Code Snippet
try
{

OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection);
cmd.CommandType = CommandType.StoredProcedure;
myconnection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
MessageBox.Show(reader.GetName(0));//here fails
while (reader.Read())
{

MessageBox.Show(reader.GetDecimal(0).ToString());
}
reader.Close();
myconnection.Close();
}
catch(Exception ex)
{

MessageBox.Show(ex.Message);
}


The code above fails because reader reads no values, error message is "No data exists for the row/column"
but i know exists. Can anyone help me, what is the difference between stored procedure and script ?

View 4 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten.  Below is the stored procedure I've created:

ALTER PROCEDURE [dbo].[ap_CalcGrade] 
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,

[Code] ....

And below is the "test query" I'm using: 

--  *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
        @Numeric   int,        -- Numeric Grade
        @Result    int         -- Procedure Status (0 = OK) 
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3, 

[Code] ....

This is resulting in an output of: 

A+ 97
A+ 97
C- 72

but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
 
A+ 97
No Find
C- 72

I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it.  Below is the assignment requirements:

Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:

1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade

View 6 Replies View Related

T-SQL (SS2K8) :: Take Data And Execute Stored Procedure With Parameters - Remove Cursor

Jun 26, 2014

I currently have a process that has a cursor. It takes data and executes a stored procedure with parameters.

declare @tmpmsg varchar(max)
declare @tmpmsgprefix varchar(max)
declare @cms varchar(20)
create table #tmpIntegrity(matternum varchar(10),ClientName varchar(20))
insert into #tmpIntegrity(matternum,ClientName)

[Code] ....

Output from code:

The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4,
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client4 accounts have A1 value and a blank A2 field. Accounts:

Desired output (no trailing comma):

The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client4 accounts have A1 value and a blank A2 field. Accounts:

Next, how do I call the stored procedure without doing it RBAR? Is that possible?

execute usp_IMessage 832,101,@tmpmsgprefix,@tmpmsg,','

View 5 Replies View Related

Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement

Feb 13, 2007

My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.

I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?

I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.

First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?

Second problem: What do I set the value of the parameter to?

The code looks like:

CallableStatement cstmt = myConnection.prepareCall(sQuery);

cstmt.registerOutParameter(1, Types.OTHER); // What is the right type?

cstmt.setNull(1, Types.OTHER); // What is the right type?

if (cstmt.execute()) {

ResultSet rs = cstmt.getResultSet();

}

Execution results in a NullPointerException from the driver.

What am I doing wrong?

Thanks for your assistance.

Jon Weaver

View 3 Replies View Related

URGENT Help --------- Stored Procedure

Oct 21, 2004

Hi !!

We are in a situaltion.... and don't have any answer...

We have 3 stored procedure. We want to write output of all the 3 stored procedure into one text file.. How do we do this?

We thought about DTS but its not solving our problem...

View 7 Replies View Related

Stored Procedure (Urgent)

Jun 14, 2006

I need to display the records from a start record number to an end record number. I need a stored procedure for this. The procedure should accept the query, start record number and the end record number as parameters. The procedure should be optimized such that it would work fast with even 1 lac records or more in the database table. Please help.....

View 1 Replies View Related

Java Code To Retrieve Data From Stored Procedure Which Returns Cursor Varying Output?

May 11, 2015

java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.

View 3 Replies View Related

Passing A Value To Another Stored Procedure...need Urgent Help

Dec 10, 2007

Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne      @typeID smallint=null,      @title nvarchar(MAX)=null,      @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON    INSERT INTO Publication (typeID, title)    VALUES (@typeID, @title)    SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
 

View 8 Replies View Related

How To Use The Return Value Of A Stored Procedure (URGENT)

May 11, 2008

  Hi everyone, I have a stored procedure as the following:1 ALTER PROCEDURE dbo.logincheck2 @ID int,3 @Password varchar(50),4 @Result int output5 AS6 IF EXISTS (SELECT * FROM users WHERE (ID=@ID AND Password=@Password))7 BEGIN8 SET @Result=19 END10 11 ELSE12 BEGIN13 SET @Result=014 END15 16 RETURN @Result What I'm trying to do is using @result in my login.aspx.vb to check if user exists in my database. If he is, the result should be 1 and if not it should be 0. Unfortunately, I don't know how to do it. I must submit my project in 2 days and I'm desperately seeking for help. Here is my code:   1 Dim sqlcon As New SqlClient.SqlConnection2 Dim sqlcmd As New SqlCommand()3 Dim sonuc As Integer = 04 If IsValid Then5 sqlcon.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersShadow ShooterWebSiteMainProjectApp_Datamydb.mdf;Integrated Security=True;User Instance=True"6 sqlcon.Open()7 sqlcmd.Connection = sqlcon8 sqlcmd.CommandType = CommandType.StoredProcedure9 sqlcmd.CommandText = "logincheck"10 sqlcmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")11 sqlcmd.Parameters("@ID").Value = txtUsername.Text12 13 sqlcmd.Parameters.Add("@Password", SqlDbType.VarChar, 50, "Password")14 sqlcmd.Parameters("@Password").Value = txtPassword.Text15 16 sqlcmd.Parameters.Add("@Result", SqlDbType.Int, 4, sonuc)17 sqlcmd.Parameters("@Result").Direction = ParameterDirection.Output18 19 20 sqlcmd.ExecuteNonQuery()21 22 If (sonuc = 1) Then23 24 Response.Write("USER FOUND :)")25 'cookie is set with SESSION ID.26 Response.Cookies("User").Value = Session.SessionID27 Response.Cookies("user").Expires = DateTime.Now.AddMinutes(30)28 'Response.Redirect("Welcome.aspx")29 Else30 Response.Write("USER NOT FOUND :(")31 'Response.Redirect("invalid.aspx")32 33 End If    

View 5 Replies View Related

Sql Stored Procedure Problem(very Urgent)

Jul 6, 2000

Since i am newbie to sqlserver and & stored procedure. I need some help to modify the existing stored procedure.
The values and parameters are passing to stored procedure from Asp page.
My question is in Stored procedure, there is one value is Company name (vchCompany Name) , I need to check the values
passed from the asp page, whether the company name is already exists in the table. If exists display the pop up message window.

Thanks
The code is below:
************************************************** ****
CREATE procedure wbospsiCompany
@iSiteId int,
@iCompanyId int = NULL OUTPUT,
@chLanguageCode char(4),
@vchAssignedId varchar(255),
@vchCompanyName varchar(255),
@vchAddress1 varchar(255),
@vchAddress2 varchar(255),
@vchAddress3 varchar(255),
@vchCity varchar(255),
@chRegionCode char(4),
@chCountryCode char(4),
@vchPostCode varchar(40),
@vchPhoneNumber varchar(40),
@vchEmailAddress varchar(255),
@vchURL varchar(255),
@iCompanyTypeCode int,
@iCompanySubTypeCode int,
@iFamilyId int,
@iParentId int,
@iPrimaryContactId int,
@vchContactFirstName varchar(255),
@vchContactLastName varchar(255),
@iDivisionCode int,
@iSICCode int,
@iMarketSector int,
@vchTaxId varchar(255),
@vchDunnsNumber varchar(255),
@iPhoneTypeId int,
@iAddressTypeId int,
@iSourceId int,
@iStatusId int,
@bValidAddress tinyint,
@iAccessCode int,
@bPrivate tinyint,
@vchUser1 varchar(255) = NULL,
@vchUser2 varchar(255) = NULL,
@vchUser3 varchar(255) = NULL,
@vchUser4 varchar(255) = NULL,
@vchUser5 varchar(255) = NULL,
@vchUser6 varchar(255) = NULL,
@vchUser7 varchar(255) = NULL,
@vchUser8 varchar(255) = NULL,
@vchUser9 varchar(255) = NULL,
@vchUser10 varchar(255) = NULL,
@chInsertBy char(10) = NULL,
@dtInsertDate datetime = NULL,
@tiLockRecord tinyint = 0,
@tiRecordStatus tinyint = 1,
@tiReturnType tinyint = 1
AS
/*
** ObjectName: wbospsiCompany
**
** Project: Apollo
** SubProject:
** FileName: Insert.sql
** Type: Production
**
** Description: Inserts a record into the Company table.
**
** Valid Values for @tiLockRecord Valid Values for @tiReturnType
** ------------------------------ ------------------------------
** 1 = Lock Record 1 = Result Set
** 0 = Don't Lock Record 0 = Output Only
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
** 1/26/99 RobertA Created
** 10/11/99 GregP Dion schema compatibility changes
**
*/
BEGIN
/*
** Declare & initialize Local Variables
*/
declare @iReturnCode int,
@iWBOCPExists int,
@iWBOCPReturn int
select @iReturnCode = 0,
@iWBOCPExists = 0,
@iWBOCPReturn = 0
/*
** Declare & initialize Local Constants
*/
declare @PRE_OPTION int,
@CORE_OPTION int,
@POST_OPTION int,
@INSERT_CODE char(1),
@chUpdateBy char(4),
@dtUpdateDate datetime,
@dtModifiedDate datetime
select @PRE_OPTION = 0,
@CORE_OPTION = 0,
@POST_OPTION = @tiLockRecord * 2, /* Lock if @tiLockRecord = 1 */
@INSERT_CODE = "I",
@chUpdateBy = NULL,
@dtUpdateDate = NULL,
@dtModifiedDate = NULL
exec @iReturnCode = ospsiCompanyPre @iSiteId OUTPUT,
@iCompanyId OUTPUT,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@PRE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
/*
** Determine if @vchAssignedId and @iIndividualId should be set by configs
*/
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsuCustomerIds @iSiteId,
"Company",
@chCountryCode,
@iCompanyId OUTPUT,
@vchAssignedId OUTPUT
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Determine if WBOCP configurable procedure exists
*/
exec @iWBOCPExists = ospObjectExists "wbocpscCompany", "p"
if @iReturnCode <= 0
begin
begin transaction
if @iWBOCPExists = 1
begin
exec @iWBOCPReturn = wbocpscCompany @INSERT_CODE,
@iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT
if @iWBOCPReturn <> 0
begin
exec @iReturnCode = ospCheckError "p", @iWBOCPReturn
end
if @iReturnCode <= 0
begin
exec @iReturnCode = espcpCheckCustomerId @iSiteId, @iCompanyId
end
end

if @iReturnCode <= 0
begin
exec @iReturnCode = ospsiCompanyCore @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@CORE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Transaction Management
*/
if @iReturnCode <=0
begin
commit transaction
end
else
begin
rollback transaction
end

if @iReturnCode <= 0
begin
/*
** Call post procedure
*/
exec @iReturnCode = ospsiCompanyPost @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@POST_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
end
/*
** Manage return: return ID of record based upon @ReturnType param
*/
exec @iReturnCOde = ospManageReturn @tiReturnType,
@iReturnCode,
@iSiteID,
@iCompanyId,
"Company",
"I"
if @iReturnCode = 0
begin
select @iReturnCode = @iWBOCPReturn
end
return @iReturnCode
END

************************************************** ****

View 1 Replies View Related

Urgent! Asp And Sql Stored Procedure Parameters

Apr 12, 2005

Hi, I have a problem with input parameter which has Decimal DataType. Stored procedure works but it rounds all
values, i.e 5.555 input becomes 6 and 1.3 input becomes 1.
In table QTY has data type decimal(5) - precision(8) scale(3).
Please, suggest what's wrong with this:

newqty = Request.Form("quantity")
..........
cmd.Parameters.Append(cmd.CreateParameter("qty", adDecimal, adParamInput, 5, newqty))
cmd.Parameters("qty").Precision = 8
cmd.Parameters("qty").NumericScale = 3

Please, help!
Thanks in advance.

View 4 Replies View Related

How To Pass XML File To Stored Procedure (Urgent)

Dec 18, 2007

i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance..... 

View 7 Replies View Related

Insertion Data Via Stored Procedure [URGENT]!!

Oct 15, 2004

Hi all;

Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??

Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??


Example:
------------
I have two tables "Customer" and "CustomerDetails"..

SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).

SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.


FYI:
----
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)


Thanks in advanced!

View 5 Replies View Related

Stored Procedure In SELECT Statement?? Urgent

Sep 26, 2001

I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2

I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.

SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2

Can I use stored proc in this way in SQL-server?

View 2 Replies View Related

Stored Procedure To Calculate Month Salary(urgent)

Aug 30, 2005

i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.

View 2 Replies View Related

URGENT: Stored Procedure Throws Windows Error 203

May 15, 2007

I am trying to execute a stored procedure on Server1 which creates an excel report on a share of Server2:

The following error message is thrown:
Saving of scheduled report(s) to Excel file failed : 203 SaveReportToExcel() in TlRptToFile.RptExcel failed in sproc_SaveReportAsFile -
TLRptXL::SaveReportToExcel - Connection to Database failed for Analytics DataBase
Server : TKTALSQL3, Application Server: and DataBase : tlAnalytics : Windows
Error - The system could not find the environment option that was entered.

A DCOM component on Server1 runs the stored procedure that creates the excel report. The account under which DCOM component runs is a member of 'Administrators' group on both Server1 and Server2. Checked permissions on the shares. Account is a local Admin on both Server1 and Server2. Account under which SQLServer and ServerAgent runs is also an Admin on these shares (implicitly as part of 'Administrators' group).

I am manually able to create an excel/text file on the Server2 share while accessing it from Server1, though.

Appreciate your help in resolving the issue.

View 4 Replies View Related

Urgent: Whichj One Is Faster : SSIS Or Stored Procedure

Dec 17, 2007



Hi
My PM and me are on a discussion. He wants SSIS to simply upload the source excel files in staging and do rest of the ETL in SQl server stored procedure which I feel is primitive.
I have suggested him to use SSIS for whole ETL and not Stored procedure( method which indistry has discarded)
He sayd Stored procedure are more efficient than SSIS.

Please guide with some facts.

View 12 Replies View Related

Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded

Aug 3, 2005

Hi all,

I have writen a Function which call's the same function it self. I'm getting the error as below.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.

CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

View 4 Replies View Related

Help Cursor Based Stored Procedure Is Getting Slower And Slower!

Jul 20, 2005

I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO

View 15 Replies View Related

Urgent. Output Columns Are Not Appearing When I Use OLEDB Data Source With An Oracle Stored Procedure In Dataflow Task

Nov 12, 2007

I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?

When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved