How To Run Oracle Stored Procedures, Especially With REF CURSOR
May 16, 2008
Hello,
Does SQL server 2005 provide capability to run Oracle stored procedures. I already have a linked server established for Oracle.
I have several oracle stored procedures that :
a) Accept multiple input parameters and return multiple out parameters.
b) Accept multiple input parameters and return a REF CURSOR as out parameter.
If you have any sample code, can you please post it here along with any suggestions. I researched, but there seems to be no solution, especially for REF CURSOR. Much appreciate it.
Thnx
Sam
View 20 Replies
ADVERTISEMENT
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Feb 26, 2004
Is there a way to call an Oracle stored procedure through a MS SQL stored procedure (via linked server)? If so, can output parameters be used?
View 1 Replies
View Related
Mar 8, 2008
Hi all,
I wrote following SP in Oracle but getting Red color with Into symbol at Procedure Name in List of Procs.
create or replace procedure EXAMPLE(:year in char, TYPE in char) AS
BEGIN
IF :year IS NULL THEN
select s.survey_year,st.survey_type_name,count(s.survey_id) as count from tqdb_survey s,TQDB_SURVEYS_TYPE st where s.survey_type_id=st.SURVEY_TYPE_ID
and st.survey_type_name=TYPE and s.is_active='N' and s.survey_year>0 group by s.SURVEY_YEAR,st.survey_type_name;
ELSE IF TYPE IS NULL THEN
select s.survey_year,st.survey_type_name,count(s.survey_id) as count from tqdb_survey s,TQDB_SURVEYS_TYPE st where s.survey_type_id=st.SURVEY_TYPE_ID
and s.survey_year=:year and s.is_active='N' and s.survey_year>0 group by s.SURVEY_YEAR,
st.survey_type_name;
END IF;
END EXAMPLE
Any Body Help me.
View 3 Replies
View Related
Jun 2, 2000
How do i migrate Stored Procedures from SQL Server (7.0) to ORACLE (8.0)??
Thanks
Anand
View 1 Replies
View Related
Feb 15, 2000
Hello Everyone,
First problem :
I have an application which uses Power Builder and Oracle and my job is to convert it so that it can run on a SQL server database.
Oracle allows empty strings - '' - to mean NULL. If the application inserts an empty string in a column of a table, Oracle takes it to mean NULL.
How can i have this functionality in SQL Server?
Second Problem:
SQL Server does not allow stored procedures like sp_addlogin and sp_droplogin etc., to be executed from within a transaction. So everytime i need to execute these stored procedures thru my application, i disconnect and set autocommit to true and connect again. after executing these stored procedures, i disconnect again , set autocommit to false and then connect again. is this how it should be done or is there some better way of doing it.
All help will be highly appreciated. I really need help on these problems - fast.
Thanks
View 2 Replies
View Related
Aug 10, 2015
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 Replies
View Related
Aug 3, 2006
Good day!
Does SQL Server has aby analog of ORACLE's Ref Cursors? ex: "type [name] is ref cursor"?
Thanks you!
View 3 Replies
View Related
Apr 24, 2007
Hi,
I need to get recordset returned by oracle sp in execute sql task to process futher in For Each Loop container and on same lines i want to use oracle sp for extraction data in Data Flow Task. Could anybody suggest if it how we could do it in SSIS?
All suggestions will be highly appreciated.
Thanks,
Lalit
View 3 Replies
View Related
Jun 29, 2015
The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.
Table A
Col1
Col2
A
Alpha
For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.
Result:
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie
View 6 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Apr 25, 2007
Hi Everyone
Please pardon my inexperience, I am new to SSIS. I am having some difficulty with using Oracle
procedures in SSIS. I have installed and configured the Oracle Client Software for 10g. I have managed to
create a connection to the Oracle database that I will be using. I am currently using the Oracle Provider For
OLE DB. I want to add an OLE DB Source component to the Data Flow which I then want to configure to
use an Oracle procedure to bring back the data that I need.
When I want to execute the Oracle Proc in the same way that I would normally execute a SQL Proc it returns
an error saying:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
I have tried using a ADO.NET connection and then using a Data Reader Source component but I get an
error with my SQL Statement saying "Invalid SQL Command"
Could anyone please provide me with some information on what I am doing wrong? Or possibly point me to
information that will help me? I have been searching the net non-stop without success.
View 15 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Oct 8, 2015
I am trying to generate a report using SSRS ( SQL server 2012 & Visual studio 2013). when I try to execute stored procedures I am getting below error. Below are the details of enviroment.
1. SQl serverr 2012
2.Oracle 12c client
3.Oracle Server 12c
4.Visual studio 2013
[code]....
View 3 Replies
View Related
Feb 16, 2004
Hello,
I deaply need to know how to execute procedures from package in oracle, from sqlserver 2000 using linkserver.
Thank you very much,
Victor
DBA
View 2 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
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
Sep 6, 2000
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
View 2 Replies
View Related
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
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
Feb 19, 2005
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
View 6 Replies
View Related
Sep 10, 2001
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
View 1 Replies
View Related
May 26, 2015
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'
View 8 Replies
View Related
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
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
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
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
