December 1, 2015 at 9:39 pm
All,
I have been working on this project for a while and seem to have hit a roadblock. I have a two stored procedure.
The first decides the table name and passes this to the second stored procedure as a parameter
The second procedure needs to use that table name parameter and loop through the records in the table and make updates.
Below is a sample using a cursor which did not work? Any suggestions on how I can make this work:
ALTER PROCEDURE [dbo].[usrSetLTDNormDist]
-- Add the parameters for the stored procedure here
@Symbol nvarchar(50),
@LTDMean float,
@LTDStdev float
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TableName nvarchar(60)
DECLARE @Volume bigint;
DECLARE @NormDist float;
Declare @TransDate date;
SET @TableName = 'VNAS'+ REPLACE(@Symbol, '-', '_')
DECLARE @SQLCommand1 NVARCHAR(MAX)
SET @SQLCommand1 = replace(N'
Declare @RecCursor1 Cursor For
Select [Volume], [TRANSDATE] from <@TableName>'
,'<@TableName>', @TableName)
EXECUTE dbo.sp_executesql @sqlCommand1
-- Open Cursor **** DOES NOT WORK **** CURSOR NOT SET ****
Open @RecCursor1
Fetch Next From @RecCursor1
Into @Volume, @TransDate
While @@FETCH_STATUS = 0
-- Begin Logic for slected symbol
Begin
-- Do Stuff ******
Fetch Next From @RecCursor1
Into @Volume, @TransDate
End;
Close @RecCursor1
Deallocate @RecCursor1
END
December 2, 2015 at 12:24 am
Cursor name shouldn't start with '@', Declare will fail with syntax error.
...
SET @SQLCommand1 = replace(N'
Declare RecCursor1 Cursor For
Select [Volume], [TRANSDATE] from <@TableName>'
,'<@TableName>', @TableName)
EXECUTE dbo.sp_executesql @sqlCommand1
Open RecCursor1
...
And it's good idea to try to avoid using cursors at all.
December 2, 2015 at 7:33 am
Thank You, did not see that at all.
December 2, 2015 at 7:45 am
Do you REALLY need a cursor here? I see why you are using dynamic sql but /*DO STUFF*/ sounds like it is doing DML "stuff" which is almost always possible without a cursor. The advantage is it will be exponentially faster than using a cursor. If you want help getting rid of the cursor give us some meat on the bone for the guts of that cursor and we can help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply