March 21, 2010 at 8:07 am
Dear All,
I have created Procedure with a cursor created dynamically as shown below.It not working as i expected as i creating the cursor dynamically.
And the Error is Must declare the scalar variable "@getData" .
and The variable '@getData' does not currently have a cursor allocated to it.
alter PROCEDURE [dbo].[usp_InsertIntoTable]
(
@Table_Name VARCHAR(70),
@Column_Name VARCHAR(70)
)
AS
DECLARE @strSQl VARCHAR(4000)
DECLARE @DateValue VARCHAR(20)
DECLARE @Value VARCHAR(20)
DECLARE @sqlstatement nvarchar(4000)
DECLARE @getData CURSOR
--DECLARE @getTableDate CURSOR
DECLARE @i INT
--SET @getData = CURSOR FOR
BEGIN TRY
SELECT @strSQl = 'SELECT (SUBSTRING(CONVERT(VARCHAR(10), DATUM, 120), 4, 2)'
SELECT @strSQl = @strSQl + '+' + '''/'''+ '+LEFT(DATUM,2)' + '+' + '''/'''+ '+RIGHT(DATUM,4) +' + ''' ''' + '+ Zeit) AS DATUM,';
SELECT @strSQl = @strSQl + 'Replace(' + @Column_Name + ',' + ''',''' + ',' + '''.''' + ') as ' + @Column_Name
SELECT @strSQl = @strSQl + ' FROM ' + @Table_Name
PRINT @strSQl;
set @sqlstatement = 'SET @getData = CURSOR FOR ' + @strSQl
exec sp_executesql @sqlstatement;
SET @i=0;
OPEN @getData
FETCH NEXT FROM @getData INTO @DateValue,@Value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i=@i+1;
PRINT @i;
FETCH NEXT
FROM @getData INTO @DateValue,@Value
END
CLOSE @getData
DEALLOCATE @getData
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
But when i run the cursor as shown below, it works well.
DECLARE @getData CURSOR
DECLARE @i INT
DECLARE @DateValue VARCHAR(20)
DECLARE @Value VARCHAR(20)
SET @i=0;
SET @getData = CURSOR FOR SELECT (SUBSTRING(CONVERT(VARCHAR(10), DATUM, 120), 4, 2)+'/'+LEFT(DATUM,2)+'/'+RIGHT(DATUM,4) +' '+ Zeit) AS DATUM,Replace(KA_HomburgBrl_Niederschlag_N_mm,',','.') as KA_HomburgBrl_Niederschlag_N_mm FROM KA_HomburgBrl_Niederschlag
OPEN @getData
FETCH NEXT FROM @getData INTO @DateValue,@Value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i=@i+1;
PRINT @i;
PRINT @DateValue
PRINT @Value
FETCH NEXT
FROM @getData INTO @DateValue,@Value
END
CLOSE @getData
DEALLOCATE @getData
Can Anybody give me some suggestion how to solve this
Thanks in Advance
March 21, 2010 at 8:37 am
What are you trying to do?
It seems like you won't need a cursor for it.
Side note: you can drop me a PM if you prefer to continue in German (my guess based on "HomburgBrl_Niederschlag") 😉
March 21, 2010 at 8:47 am
HI,
Thanks for your quick reply.
Well,Let me explain the complete scenario.
Here what i m trying to do is i am passing the Table name and column name to the procedure ,then the procedure will create the SQL QUERY automatically.Then based on the query i need to create the CURSOR which suppose to do row by row process , And Insert or update to another Table.I did not mention the code for Insert or update code for now.Currently i am just printing integer value inside the cursor.
Hope this will give you a better idea ,If you have any other solution please let me know.
It is for a German client.but i can't understand German
Best Regards
Ihsan
March 21, 2010 at 9:01 am
Step 1: try to get your code for the insert and update right (that is performing it not row by row but set based). Once that's done, we'll help you to make it dynamic (which is step 2).
If you have problems transforming your RBAR solution to a set based version we might be able to help you with that, too. But we need more information.
By the way: Where are you located? Sounds like "fun" to write software in an unknown language...
March 21, 2010 at 9:13 am
HI ,
Thanks for your quick reply.
Insert and update part i removed from the script for Time being.
Can you give me an example script or some useful links to run this Cursor or give me an example i can do row by row operations.
Best Regards
Ihsan
March 21, 2010 at 9:36 am
ihsanps (3/21/2010)
HI ,...
Can you give me an example script or some useful links to run this Cursor or give me an example i can do row by row operations.
...
No.
For a very simple reason: as long as I don't know the business case and I'm confident that a cursor is a valid/last option I'm not going to present such a solution. A cursor tend to cause performance issues, sooner or later. It also makes the code more complicated than it has to be, leading to increased maintenance effort.
March 21, 2010 at 9:43 am
HI,
Below are the Process which i m doing inside the cursor.Hope this will help you to give me a better solution.
OPEN @getData
FETCH NEXT FROM @getData INTO @DateValue,@Value
WHILE @@FETCH_STATUS = 0
BEGIN
--Checking the Data exists in the Table or not
--If exists Update the Value
--If Not Exists Insert it as new record
FETCH NEXT
FROM @getData INTO @DateValue,@Value
END
CLOSE @getData
DEALLOCATE @getData
Best Regards
Ihsan
March 21, 2010 at 10:00 am
It seems like we have to start at the very beginning:
"Niederschlag" should be stored as a numeric value.
What you're trying to do is to change the german decimal notation to the english version. Obviously someone decided to store that dimension as character... Bad design.
Furthermore, it seems like you're trying to convert the German date format to the English version leading to a date column in character format as well. Again, bad design.
I strongly recommend to completely rethink the database design. You might consider to look into logins that would represent the language for the user.
It seems like the cursor is not the only problem...
March 21, 2010 at 10:15 am
ihsanps (3/21/2010)
HI,Below are the Process which i m doing inside the cursor.Hope this will help you to give me a better solution.
OPEN @getData
FETCH NEXT FROM @getData INTO @DateValue,@Value
WHILE @@FETCH_STATUS = 0
BEGIN
--Checking the Data exists in the Table or not
--If exists Update the Value
--If Not Exists Insert it as new record
FETCH NEXT
FROM @getData INTO @DateValue,@Value
END
CLOSE @getData
DEALLOCATE @getData
Best Regards
Ihsan
I am with Lutz on this - if you provide your actual insert and update logic, it can easily be transformed into a set based MERGE instead of RBAR cursor processing. And, it will have the added benefit of running a lot faster.
Generally, if you need to create dynamic code to update/insert to multiple tables with the same structure there is a problem with the database design. You should rethink that design if you can.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 21, 2010 at 9:59 pm
lmu92 (3/21/2010)
Sounds like "fun" to write software in an unknown language...
Heh... I've found that a lot of people who write T-SQL are actually writing in an unknown language. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2010 at 11:35 pm
HI,
This is the complete working Procedure.I manage to run the full procedure successfully.
ALTER PROCEDURE [dbo].[usp_InsertIntoTable]
(
@Table_Name VARCHAR(70),
@Column_Name VARCHAR(70)
)
AS
DECLARE @strSQl VARCHAR(4000)
DECLARE @DateValue VARCHAR(20)
DECLARE @Value VARCHAR(20)
DECLARE @sqlstatement nvarchar(MAX)
DECLARE @InsideSQl nvarchar(MAX)
DECLARE @getData CURSOR
DECLARE @rowCount NUMERIC(18,0)
--DECLARE @getTableDate CURSOR
DECLARE @i INT
--SET @getData = CURSOR FOR
BEGIN TRY
SELECT @strSQl = 'SELECT (SUBSTRING(CONVERT(VARCHAR(10), DATUM, 120), 4, 2)'
SELECT @strSQl = @strSQl + '+' + '''/'''+ '+LEFT(DATUM,2)' + '+' + '''/'''+ '+RIGHT(DATUM,4) +' + ''' ''' + '+ Zeit) AS DATUM,';
SELECT @strSQl = @strSQl + 'Replace(' + @Column_Name + ',' + ''',''' + ',' + '''.''' + ') as ' + @Column_Name
SELECT @strSQl = @strSQl + ' FROM ' + @Table_Name
PRINT @strSQl;
--set @sqlstatement = 'Declare @getData CURSOR FOR ' + @strSQl
set @sqlstatement = 'SET @getData = CURSOR local fast_forward FOR ' + @strSQl + ';open @getData;'
--PRINT @sqlstatement;
exec sp_executesql @sqlstatement,N'@getData cursor output', @getData OUTPUT;
SET @i=0;
--OPEN @getData
WHILE 1=1
BEGIN
FETCH NEXT FROM @getData INTO @DateValue,@Value
SET @i=@i+1;
PRINT @i;
--PRINT @DateValue;
--PRINT @Value;
SELECT @InsideSQl = '
IF NOT EXISTS(SELECT * FROM Detail_Info WHERE DATUM =''' + @DateValue + ''')
BEGIN
INSERT INTO Detail_Info(DATUM,' + @Column_Name + ') VALUES (''' + @DateValue + ''',''' + @Value + ''')
END
ELSE
BEGIN
UPDATE Detail_Info SET ' + @Column_Name + '=' + '''' + @Value + '''' + ' WHERE DATUM= ''' + @DateValue + '''
END
'
exec sp_executesql @InsideSQl;
IF @@FETCH_STATUS <> 0 OR @@ERROR <> 0
BREAK;
END
--CLOSE @getData
--DEALLOCATE @getData
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
I manage to work the full procedure now.I can say its too slow because of this statment.If take out this line the SP is very fast.
SELECT @InsideSQl = '
IF NOT EXISTS(SELECT * FROM Detail_Info WHERE DATUM =''' + @DateValue + ''')
BEGIN
INSERT INTO Detail_Info(DATUM,' + @Column_Name + ') VALUES (''' + @DateValue + ''',''' + @Value + ''')
END
ELSE
BEGIN
UPDATE Detail_Info SET ' + @Column_Name + '=' + '''' + @Value + '''' + ' WHERE DATUM= ''' + @DateValue + '''
END
'
exec sp_executesql @InsideSQl;
Let me give you more explanation about this system
Step 1 )
I developed a .NET application which read more than 100 files and updates in to SQL DATABASE.And each file contain around 100k Records.
Step 2 )
I manage to insert the data in to tables using BULK INSERT,which is quiet
fast and working smoothly.this one also i manage to do using SP
step 3)
After creating the table i am creating a cosolidated Table which will contain a column named Datrum and a column of each of 100 files.this one also i manage to do using SP
step 4)
After creating the Table i need to insert the Data from each file and update the each column manually.When i insert i am validate the Date is exists or not. if it exists it wil update the value or it will insert the value as new record. I am using Above SP to do the STEP 4.
Can anybody help me to find a solution.
Best Regards
Ihsan
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply