Can I Fill A Cursor From A Strored Procedure?
Nov 13, 2003
Basically, I have a complex stored procedure that combines two tables and fills a cursor.
I would like to fill another cursor in another stored procedure from the results of this first stored proc, rather than have to type it all in again.
The reason being that I am doing a one time import of some data from two tables into one new table based on some complex linking/querying.
Can I fill a cursor from the output of another stored procedure rather than an inline SELECT statement?
Does the sp I am using have to have cursor as an out parameter?
View 4 Replies
ADVERTISEMENT
Oct 23, 2007
Hi All
I am using SQL server 2005.
I have wriiten the Stored Procedure.
Once I modified anything in the Stored Procedure I use ALTER command and save it.
But I am unable to save the comments with this ALTER command which I write in the beginnign of the Stored Procedure.
I have given the example below.
Also please tell me without using ALTER is there any way to save the stored procedure.
For Ex:
/*************************************************************************************************************************************************************************************
* Procedure : pr_Generate_PaymentApproval_List
* * Called By : PPComponent.PPPayment.GetPaymentApprovalList
* Description : This proc is use to retrieve payment approval info
*/
ALTER PROCEDURE dbo.pr_Generate_PaymentApproval_List (
@ins_id BIGINT, @disti_id BIGINT,
@geo_cd NCHAR(4) )
--
--
Regards
Abdul
View 2 Replies
View Related
Apr 26, 2006
I am having an issue when trying to pull data from Oracle into a sqlSERVER TABLE. I'm running into an issue because I am using to much temporary space in Oracle because the return set is so large. New to sqlserver, but in Oracle the solution would be to commmit the insert after so many records have been retrieved.
Here's the procedure I wrote I would think I need to add a cursor and a counter which be used to do a commit after so many rows have been retrieved. DOes so one have a procedure I could model mine after?
ALTER PROCEDURE [dbo].[SP_Load_BO]
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 0
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO MERCHANT_BACKOFFICE_SQLSERVER
(MONTHYEAR,ALLIANCEID, merchantid,rptservid,scid,tabletype,tablenum,VOLUME, COST, COSTPLUS,DATELOAD)
SELECT convert(DATETIME,PERIOD) as PERIOD,
ALLIANCEID,
merchantid,
rpTSERVID,
scid,
tabletype,
tablenum,
Convert(numeric(10),Count) AS VOLUME,
Convert(numeric(24,6),COST) as COST,
Convert(numeric(24,6),COSTPLUS) as COSTPLUS,
convert(datetime,DATE_LOAD) as DATE_LOAD
from
OPENQUERY(INV,'
SELECT MER.MONTHYEAR AS PERIOD,
MER.ALLIANCEID,
MER.merchantid,
MER.RPTSERVID,
MER.SCID,
MER.TABLETYPE,
MER.TABLENUM,
SUM(MER.VOLUME) AS Count,
SUM(MER.COST) as Cost,
SUM(MER.COSTPLUS) as Costplus,
to_char((last_day(add_months(sysdate,-1))+1),''MM/DD/YYYY'') as DATE_LOAD
FROM
MERCHANT_BACKOFFICE_SERVICE MER
WHERE MER.allianceid <>516
group by MER.MONTHYEAR,
MER.ALLIANCEID,
MER.merchantid,
MER.RPTSERVID,
MER.SCID,
MER.TABLETYPE,
MER.TABLENUM')
END
View 5 Replies
View Related
Dec 20, 2007
Hi...
Am working with asp.net with vb for a MusicProject..
In this I have playlist for particular User..ie User Selects Some Songs and clicks on AddToMyPlayList Button....
Here He can insert those songs into a new playlist or update the earlier..
the StroedProcedure is as follws: 'N' means NewPlayList and 'E' means Existing
***************************************************************************************
CREATE PROCEDURE MUSIC_ADD_PLAYLIST ( @PLAYLIST_NAME VARCHAR(255), @USER_ID VARCHAR(255), @ItemList NVARCHAR(4000), @delimiter CHAR(1), @FOLDERNAME VARCHAR(255) ,@PLAYLISTTYPE CHAR(1)) AS SET NOCOUNT ON
DECLARE @IDENT INT
IF @PLAYLISTTYPE = 'N'
BEGIN INSERT INTO MUSIC_PLAYLIST ( MUSIC_PLAYLIST_NAME, MUSIC_PLAYLIST_USER ) VALUES ( @PLAYLIST_NAME, @USER_ID )
SELECT @IDENT=@@IDENTITY FROM MUSIC_PLAYLIST END IF @PLAYLISTTYPE='E'
BEGIN
SELECT @IDENT=MUSIC_PLAYLIST_ID FROM MUSIC_PLAYLIST WHERE MUSIC_PLAYLIST_USER=@USER_ID AND MUSIC_PLAYLIST_NAME=@PLAYLIST_NAME
END
DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) -- INSERT INTO @IDTable(Item) VALUES(@Item)
INSERT INTO MUSIC_SONGSLIST (Music_PlayList_Id,Music_SongName,Music_Song_Location) VALUES (@IDENT,@ITEM,@FOLDERNAME+''+@ITEM) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END
GO
***************************************************************************************
Here the problem is am not getting the exact result means if i add 6 songs to a playlist it is adding only 4 songs to that particular playlist..
Please help me out
Thanks in Advance,
Madhavi
View 1 Replies
View Related
Jul 20, 2005
Hi,I have stored procedure (MS SQL Server 2000) which operateson around 600 000 rows (SELECT, UPDATE, INSERT)and executes in 5 minutes,when I put it in SQL transaction it slows down to more than 5 hours (!!)I have to admit that it is not problem with data locks (beside thatprocedurenothing else is executed on db),It is not also problem with that exact procedure, other proceduresalso slow down heavily when wrapped by SQL transactionvery very seldom stored procedure within transaction executescomparably long that its copy without transactionI guess it could be MS SQL Server 2000 configuration/tuning problem.Any ideas ?Chris
View 1 Replies
View Related
Jan 3, 2008
Pls tell me the main differences b/w user defined function and stored procedure .
View 2 Replies
View Related
Jul 21, 2005
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms
@email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT
SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read.....
Dim errCode As Integer
Dim conn = dbconn.GetConnection()
Dim sqlAdpt As New SqlDataAdapter
Dim DS As New DataSet
Dim command As SqlCommand = New SqlCommand("Comms", conn)
command.Parameters.Add("@email", Trim(sEmail))
command.CommandType = CommandType.StoredProcedure
command.Connection = conn
sqlAdpt.SelectCommand = command
Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int)
pNum.Direction = ParameterDirection.Output
Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar)
pUserEmail.Size = 256
pUserEmail.Direction = ParameterDirection.Output
sqlAdpt.Fill(DS)
Return DS
Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way....
Dim DScomm As New DataSet
DScomm = getPts.getBabComm(sEmail)
dgBabComm.DataSource = DScomm.Tables(0)
And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
View 7 Replies
View Related
Oct 4, 2005
I have a table with information about a mobile account in one table, a table for mobile plans, and a table for planfeatures. Each mobile account is associated with a planid, and may be associated with any combination of the features associated with that plan. The plan features are stored in a bridgetable which contains 'invoicedate' (date stamped on the invoice in question), 'subaccountnumber' (the cell phone number), 'planid', and 'featureid'. I've tested the UpdateMobileFeature sp directly from the SQL Profiler--it works fine on it's own. I use a stored procedure to fill the mobile table (called from aspx page), and since I use three of the four columns written above for both the mobilesub and the mobilefeature tables, I tried just adding the parameter which holds the featureid's to the sp to update the mobilesub table. Then I call the UpdateMobileFeature sp from the updatemobile sp. (The code in mobilesub that is not calling UpdateMobileDetail works well). All seems to work fine--nothing crashes or anything, but nothing is being added to the mobilefeature table. Here is the code:CREATE procedure usp_updatemobile( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanId int, @FeatureList varchar(500) --added for UpdateMobileFeatures. In the form of a comma-seperated list, to imitate an array. --***irrelevant parameters removed***--)
as
exec dbo.UpdateMobileFeature '@InvoiceDate','@SubaccountNumber','@PlanId','@FeatureList' --the apparently nonfunctional call
if exists ( //select for the mobile row in question ) Begin //update row
End
Else Begin //insert new row End
GOCREATE PROC dbo.UpdateMobileFeatures( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanID int, @FeatureList varchar(500))ASBEGIN SET NOCOUNT ON
CREATE TABLE #TempList ( InvoiceDate smalldatetime, SubaccountNumber varchar(50), PlanID int, FeatureID int )
DECLARE @FeatureID varchar(10), @Pos int SET @FeatureList = LTRIM(RTRIM(@FeatureList))+ ',' SET @Pos = CHARINDEX(',', @FeatureList, 1)
IF REPLACE(@FeatureList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @FeatureID = LTRIM(RTRIM(LEFT(@FeatureList, @Pos - 1))) IF @FeatureID <> '' BEGIN INSERT INTO #TempList (InvoiceDate, SubaccountNumber, PlanID, FeatureID) VALUES (@InvoiceDate, @SubaccountNumber, @PlanID, CAST(@FeatureID AS int)) --Use Appropriate conversion END SET @FeatureList = RIGHT(@FeatureList, LEN(@FeatureList) - @Pos) SET @Pos = CHARINDEX(',', @FeatureList, 1)
END END
--SELECT o.FeatureID, CustomerID, EmployeeID, FeatureDate --FROM dbo.Features AS o -- JOIN -- #TempList t -- ON o.FeatureID = t.FeatureID
Insert Into MobileFeatures Select InvoiceDate, SubaccountNumber, PlanID, FeatureID From #TempList ENDGOHere is the method I used to call the first sp: (also with irrelevant stuff removed)public void saveCurrentMobile() {
calculateTotals();
InvoiceDataSet dataset = InvoiceDataSet.GetInstance();
SqlConnection conn = (SqlConnection)Session["connection"];
SqlCommand cmdUpdateMobile;
cmdUpdateMobile = new SqlCommand("usp_updatemobile", conn); cmdUpdateMobile.CommandType = CommandType.StoredProcedure;
SqlParameter invoicedate = cmdUpdateMobile.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime); invoicedate.Value = DateTime.Parse(Request.Params["InvoiceDate"].ToString());
SqlParameter cellnumber = cmdUpdateMobile.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50); cellnumber.Value = txtCellNumber.Text.Trim();
SqlParameter plan = cmdUpdateMobile.Parameters.Add("@PlanId", SqlDbType.Int); plan.Value = planid;
SqlParameter featurelist = cmdUpdateMobile.Parameters.Add("@FeatureList", SqlDbType.VarChar, 500); featurelist.Value = planform.FeatureList;
//Response.Write(isthirdparty.ToString()); Response.Write(thirdpartycompany);
SqlParameter cycle = cmdUpdateMobile.Parameters.Add("@Cycle", SqlDbType.Int); cycle.Value = Convert.ToInt32(Request.Params["Cycle"]);
int returnvalue = runStoredProcedure(cmdUpdateMobile); //the sp is called within this method.
if (returnvalue != 0) { dataset.fillMobileTable(); Response.Write("Mobile Subaccount Saved!"); } }
View 3 Replies
View Related
Oct 27, 1998
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
View 2 Replies
View Related
Dec 26, 2007
i explain
what i mean is if i have stored procedure with 7 conditions
for evry day in the week one condition
like this i have 7 code blocks for Sunday,Monday , .....Saturday
Code Block
CREATE PROC YourProc
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied
SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today
WHILE @StartDate <= @EndDate
BEGIN
SELECT CASE
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =51 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Monday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =11 )
THEN 2
............
WHEN DATENAME( dw,@StartDate)='Saturday' AND
...
THEN ..
END
SET @StartDate=DATEADD(d,1,@StartDate)
END
GO
like this until the last day of the month
TNX
View 2 Replies
View Related
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
Jan 28, 2008
I have a stored Proc, which takes 30 min for execution. If I execute it twice it runs properly but when i do the same the third time, it gets stuck.. any reasoning for this..
I am using Insert into statements a lot here...
View 3 Replies
View Related
Dec 31, 2004
I've got the folloing stored procedure
ALTER PROCEDURE AddUserData
(@login varchar (8),
@password varchar (8),
@fullName varchar (50),
@RoleID Int,
@statusID Int)
AS
if exists
(SELECT login, @errorReturn
FROM tbEmployee
WHERE login = @login)
return 55555
Else
INSERT INTO tbEmployee (login, password, fullname, RoleID, statusId)
VALUES (@login, @password, @fullName, @RoleID, @statusID)
RETURN @@error
the function that calls this stored procedure is this
Public Shared Function InsertUserData(ByVal login As String, ByVal password As String, ByVal fullName As String, ByVal StatusID As Integer, ByVal RoleID As Integer)
' Create the connection object
Dim connection As New SqlConnection(connectionString)
' Create and initilise the command object
Dim command As New SqlCommand("AddUserData", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@password", SqlDbType.VarChar)
command.Parameters("@password").Value = password
command.Parameters.Add("@login", SqlDbType.VarChar)
command.Parameters("@login").Value = login
command.Parameters.Add("@fullName", SqlDbType.VarChar)
command.Parameters("@fullName").Value = fullName
command.Parameters.Add("@roleID", SqlDbType.Int)
command.Parameters("@roleID").Value = RoleID
command.Parameters.Add("@statusID", SqlDbType.Int)
command.Parameters("@statusID").Value = StatusID
' Open the connection
Try
connection.Open()
command.ExecuteNonQuery()
Finally
connection.Close()
End Try
End Function
now I'm trying to get ahold of the return value from the stored procedure so I can output a message to the user in a label control.
e.g the soted procedure checks to see if a user already exists if it does it returns 55555. how can I get ahold of this 55555 and place it in a variable in the function so I can then send it to another error function to display the approperate lable message ??
thanks all
View 3 Replies
View Related
Jul 12, 2004
Does anyone know of an internal procedure to return the timestamp of the last time a storedprocedure or table was modified?
Currently the object only lists the creation timestamp.
View 2 Replies
View Related
Jul 18, 2007
Hi,
Can anyone tell me how to export a batch of stored procedures in MS sql server Management studio?
Thanks.
View 1 Replies
View Related
May 18, 2008
Hello:
I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to segment with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it?
Your help is highly appreciated.
s
View 2 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
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
Nov 8, 2006
Hi,everyone.
Why need to open CURSOR in the part in which a procedure is created?
The following codes are listed in the Microsoft SQL Server 2005 BOOKS ONLINE.
(1)
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.currency_cursor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.currency_cursor;
GO
CREATE PROCEDURE dbo.currency_cursor
@currency_cursor CURSOR VARYING OUTPUT
AS
SET @currency_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.Currency;
OPEN @currency_cursor;
GO
(2)
USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
Please help me. Thank you in advance!
View 5 Replies
View Related
Mar 30, 2008
can someone tell me how to use static cursor to read the rows applying it in procedure.
i have input and output tables
i will have to use input table to read data and then in procedure update/insert into output table ) insert values from input and dditioanl calulate charges.
ok, there is my problem:
An Internet service provider has three different subscription
packages for its customers:
Package A: For $15 per month with 50 hours of access provided.
Additional hours are $2.00 per hour over 50 hours.
Assume usage is recorded in one-hour increments,
i.e., a 25-minute session is recorded as one hour.
Package B: For $20 per month with 100 hours of access provided.
Additional hours are $1.50 per hour over 100 hours.
Package C: For $25 per month with 150 hours access is provided.
Additional hours are $1.00 per hour over 150 hours
Assume a 30-day billing cycle.
1) Create a table to hold customer input billing data.
2) Populate input table with follwing records:
CustomerID Pkg Hours
---------- --- ------
1000 A 49
1010 A 50
1020 a 90
1030 a 130
1090 B 40
1100 B 99
1110 b 100
1120 b 145
1140 C 45
1150 c 85
1160 c 149
1170 c 150
1180 c 200
3) Create a table to hold customer data used to generate
the statement to be sent to the customer. It should
include CustomerID, Package, HoursUsed, and Charges.
Write an SQL script that reads customer billing data,
calculates a customer’s monthly charges, and populates
the customer statement table.
Use Cursor to process records and Stored Procedures for
ProcessBill and calcCharges.
CREATE TABLE custinput(
cust_id int NULL,
pkg char(1) NULL,
hrs smallint NULL
)
CREATE TABLE custoutput(
cust_id int NULL,
pkg char(1) NULL,
hrsused smallint NULL,
charges money null
)
insert into custinput values (1000,'A',49);
insert into custinput values (1010,'A',50);
insert into custinput values (1020,'a',90);
insert into custinput values (1030,'a',130);
insert into custinput values (1090,'B',40);
insert into custinput values (1100,'B',99);
insert into custinput values (1110,'b',100);
insert into custinput values (1120,'b',145);
insert into custinput values (1140,'C',45);
insert into custinput values (1150,'c',85);
insert into custinput values (1160,'c',149);
insert into custinput values (1170,'c',150);
insert into custinput values (1180,'c',200);
then there is conditions:
if upper (@pkg)= 'A'
begin
if @hrs<= 50 set @charges =15
else set @charges =15 + (@hrs-50)*2
end;
else if upper(@pkg)= 'B'
begin
if @hrs <= 100 set @charges = 20
else set @charges = 20 + (@hrs - 100)*1.5
end;
else
if @hrs <=150 set @charges = 25
else set @charges =25+(@hrs-150)
insert into custoutput values(@cust_id,@pkg,@hrs,@charges)
View 3 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
May 18, 2008
Hello:I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to the segments with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it?
Your help is highly appreciated.
S
View 6 Replies
View Related
Feb 25, 2004
What is error here when i declare cursor ?
declare curQueryVehicleHave cursor for
exec sp_executesql @strQueryVehicleHave
@strQueryVehicleHave this string contain a query
View 2 Replies
View Related
Apr 30, 2004
HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-
DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER
BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-
CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID
WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-
EXEC MY_PROC
BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-
I AM USING :-
DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC
WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."
AND IF I USE :-
DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".
AND IF I USE :-
DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "
IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.
I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW
THANKS
View 14 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
Feb 29, 2008
i m new in sql...and i have this procedure..which have cursor inside..
1. i want to get all distinct date into #tempt table.
2. In the loop for each distinct date fetch all the date into another #temp
3. get max date from that #temp and use that date to get the data from original table
but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...
create procedure procdate1
(@name varchar(50))
as
begin
SET NOCOUNT ON
DECLARE @MaxDate datetime
DECLARE @Date datetime
select id, title, dated,
CONVERT(CHAR(10), dated,101) as date,
CONVERT(CHAR(8), dated,114) as time from general
where name = @name AND dated = @MaxDate
DECLARE CUR1 CURSOR FOR
SELECT @DATE FROM #tempt
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @Date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date
SELECT @MaxDate = MAX(dated) FROM #date1
--DROP TABLE #Date1
FETCH NEXT FROM CUR1 INTO @Date
continue
CLOSE CUR1
DEALLOCATE CUR1
end
-- DROP TABLE #tempt
end
View 3 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 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
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
View Related
Jun 21, 2004
The tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.
View 1 Replies
View Related
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
