Opening Cursor On Result Set From Stored Proc
Jul 20, 2005In SQL how can a cursor be opened to iterate the result set returned
from a stored proc
-Rahul Sood
Join Bytes!
ADVERTISEMENT
Get A Cursor From A Stored Proc.
Sep 6, 2000Hello.
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
Using A Cursor Output From Stored Proc
Feb 19, 2005Has 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
Stored Proc Problem With Int In Cursor?
Sep 10, 2001I 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
Better Way To Rewrite Stored Proc And Not Use Cursor At All
May 26, 2015I 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'
Result Set From Stored Proc In Php Ctp
Oct 25, 2007Is there an example of how to execute a stored proc using the php ctp driver? In the following code, I get an error in the sqlsrv_stmt_fetch_array method. The error is [Microsoft][SQL Native Client]Invalid cursor state.
$proc = "EXECUTE dbo.spGetId $table";
$stmt = sqlsrv_conn_execute($connection, $proc);
if ( !$stmt )
{
$error = sqlsrv_errors();
catchError(3, $error[0]['message'], __FILE__, __LINE__);
}
$row = sqlsrv_stmt_fetch_array($stmt);
if ( !$row )
{
$error = sqlsrv_errors();
catchError(3, $error[0]['message'], __FILE__, __LINE__);
}
When I execute the stored proc in query analyzer, I get 1 record with 1 column.
I'm looking forward to using this driver. This is the first issue I've come across. Similarly, is there a way to get output parameter values from a stored proc?
Thanks,
Greg
UNION ALL, The Use Of GO In Stored Proc. And The Result Is?
Aug 29, 2006I hope I can express what I need to know.Here it goes. I am planning on writing a single stored procedure to include two select statements and followed by a third like so:Select * etcUNION ALLSelect * etcGOSelect something elseMy question is this: does this return one record set (returning all records from the three Select statements) or two, the first being the result from the UNION and the second from the select following the GO. I will need to access the data returned from the resultant UNION and from the select after the Go, so I wonder if I'll be dealing with a single dataset or two. I hope this made sense.
View 3 Replies View RelatedSQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR
Jul 10, 2014I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
Storing A Stored-proc's Result Into A Temp Table
Jul 20, 2005I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?
View 3 Replies View RelatedSQL 2012 :: Opening Files Result In Connect To Database Engine Prompt?
Feb 24, 2014When opening .sql files, I get a connect to database engine prompt every single time. how to stop this from prompting vs. just using my current active connection?
View 4 Replies View RelatedCan You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Is It Possible To Put The Result From A Sp Into A Cursor
Dec 5, 2000I am tring to find out wheather a SQL Agent job is running.
I was wanting to put the result from sp_help_job into a cursor and check whether @@fetch_status was successful.
if it fails then job is not running
the below code fails because when declaring a cursor expects a select statement.
is there anyway to get result from a sp into a cursor thanks
declare @sql varchar(100)
set @sql = 'sp_help_job null, ''event_167'', @job_aspect = ''job'', @execution_status = 1'
declare Job_Cursor cursor for
sp_help_job @job_name = 'some_job_name', @job_aspect = 'job', @execution_status = 1
open Job_Cursor
fetch next from Job_Cursor
if @@fetch_status <> 0
begin
print 'Job is not runnning'
end
if @@fetch_status = 0
begin
print 'Job is runnning'
end
close Job_Cursor
deallocate Job_Cursor
Using The Result Of A Cursor
May 5, 2008I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have a database with two tables:
eg
1) myGameRecency which contains columns : GameId , Date, Zero, One, Two, Three
2) myGameFrequency which contains a column : weeksSinceLastDrawn
Using the myGameRecency table ---
I wish to Fetch the GameId (which is just a counter) which corresponds to the last time a 0 was entered into that column....known in the programme as @lastTimeDrawn
I also wish to Fetch the GameId which corresponds to the current date....known in the programme as @currentTimeDrawn
I then wish to subtract @lastTimeDrawn from @currentTimeDrawn and place that result in the myGameFrequency table.
I have tried a variety of code and last tried the following:
Can anyone please assist?
Thank you.
lpbcorp
if (Ball1 == true)
{
sqlCmd.CommandText = "DECLARE @currentTimeDrawn int " +
"DECLARE @lastTimeDrawn CURSOR " +
"DECLARE lastrow_cursor CURSOR FOR " +
"(SELECT GameId FROM " + DBGameName.ToString() + "Recency WHERE One = 0) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_cursor " +
"SET @lastTimeDrawn = lastrow_cursor " +
"SET @currentTimeDrawn = (SELECT GameId FROM " + DBGameName.ToString()
+ DBGameName.ToString() + "Recency WHERE Date = '" + Date + "') " +
"UPDATE " + DBGameName.ToString() + "Frequency SET WeeksSinceDrawnMainDrawBall = @currentTimeDrawn - @lastTimeDrawn WHERE Ball = 1 " +
"CLOSE lastrow_cursor " +
"DEALLOCATE lastrow_cursor";
sqlCmd.ExecuteScalar();
}
break;
Can You Return A Cursor From A Proc?
Apr 25, 2002Can I do something like
CREATE PROCEDURE ProcA
AS
DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT
END
GO
I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.
AB
Help On Store Proc CURSOR
Aug 28, 2007---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot.
select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else
---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan)
---but Shaggy has sponsored 2 others
select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy
---I also want to get their information ID,fname,lname
---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain:
---Let's assume that we have an OAK Tree that has 4 main branches
---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible
--the way I have it now taking way too long
--because in within so many (do while loop)
TIA
Please pardon me, I could not find better layout to explain this.
Storing The Result Into A Variable W/o Cursor
Feb 1, 2007Hello All,
A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.
Thank you in advance
SQL 2012 :: Calling Proc For Each Row In A Table Without Using Cursor
Apr 14, 2015I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
After SP4, Proc W. Cursor Doesn't Release Keylocks
Aug 15, 2006Existing Stored Procedure, has been running well on SQL since 7.0.(but needed some tweaking to migrate to 2000).Now all of a sudden after installing SP4 of SQL 2000,this process slows down, and SQL Spotlight shows the number of locksjust climbing throughout the processing run.According to the MS Knowledge Base Articles on KeyLocks .. this was aproblem that was *fixed* in the service pack ... where as for me it isnow broken.Article ID: 260652PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"May Hold Locks Longer http://support.microsoft.com/kb/260652/Article ID: 828096FIX: Key Locks Are Held Until the End of the Statement for Rows ThatDo Not Pass Filter Criteria http://support.microsoft.com/kb/828096/Anybody else have this issue, or have any "eazy" solutions?The proc cursors thru a list and runs a proc on each item in the "worklist".This is an existing systemwith no plans to turn the process into a set oriented one,as is going away shortly.
View 4 Replies View RelatedStored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedSELECT DISTINCT Will Always Result In A Static Cursor
Jul 31, 2007an example for the pb
1)First i have created a dynamic cursor :
DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor
2)The result for this cursor is for expamle 'USA'.
3) If now i do an update on that location with a new value 'USA1'
update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7
4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).
If i remove DISTINCT from the cursor declaration , the process works fine .
SELECT DISTINCT Will Always Result In A Static Cursor
Jul 31, 2007An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste CURSOR DYNAMIC
FOR Select DISTINCT name from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .
any idea how to make a select distinct result in a dynamic Cursor?
ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedDynamic Name Of Result Column In S-proc?
Oct 12, 2004How can I make this stored procedure concept work:
-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
Bjorn
SQL Server 2012 :: SELECT Query - Cursor To Display Result In Single Transaction
May 25, 2015Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR
[Code] ....
SQL Server 2012 :: Asynchronous Cursor Population Slow For Large Result Sets
Jul 2, 2015so async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.
Example:
--enable async cursor
exec dbo.sp_configure 'cursor threshold', 0; reconfigure;
declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int;
--example of giant result set
set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';
[code]...
Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.
How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Apr 7, 2008Dear All
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
Here is my query sample for yours to understand.
SET NOCOUNT ON
DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)
Set @qstring = 'select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm'
PRINT '-------- ITEM Products Report --------'
DECLARE ITEM_cursor CURSOR FOR
execute (@qstring)
OPEN ITEM_cursor
FETCH NEXT FROM ITEM_cursor
INTO @ITEMCODE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From ITEM: ' +
@ITEMNAME
PRINT @message
-- Get the next ITEM.
FETCH NEXT FROM ITEM_cursor
INTO @ITEMcode
END
CLOSE ITEM_cursor
DEALLOCATE ITEM_cursor
Why i use @qstring? It is because the query will be changed by different critiera.
Regards
Edmund
Opening Package Stored In MSDB
Jun 30, 2006I created a package using the ImportExport wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?
TIA,
barkingdog
Calling A Stored Proc From Within Another Stored Proc
Feb 20, 2003I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Stored Proc Calls Another Stored Proc
Jan 13, 2006Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Calling Stored Proc B From Stored Proc A
Jan 20, 2004Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
Calling T SQL Stored Proc From CLR Stored Proc
Aug 30, 2007I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Opening Symmetric Keys In Stored Procedures
Apr 26, 2007I can't seem to use symmetric keys in stored procedures. I am trying to decrypt a column in my stored precedure but the values are all NULL. First I tried a symmetric key that required a password but I kept getting simple syntax errors when I tried to send the password to the stored precedure as a parameter. Now I am trying a symmetric key that uses a certificate.
I also tried using WITH EXECUTE AS SELF and WITH EXECUTE AS CALLER but that made no difference.