September 22, 2011 at 8:12 am
Hi Friends,
im getting error while executing
"Cursorfetch: The number of variables declared in the INTO list must match that of selected columns"
below is my query.....it seems error in selecting the query....when i execute that query using stored procedure, it really works fine...but when it comes to cursor, its not...so can one help me pls?
Alter procedure Sp_Coded (@TableName varchar(15), @ColumnName varchar(10), @CodeID int, @Number bigint, @MaxLength int) as
begin
declare @JurisID int, @CodedID varchar(4), @CodeIDDescr varchar(60), @SrcCodeDescr varchar(60), @IsConverted int, @DecNumber bigint
Set @DecNumber = @Number
Declare MySql cursor for
select 1501 as JurisID, + Cast(@CodeID as Varchar(4)) + ' as CodedID, ' + @TableName+ '.' + @ColumnName + ' as CodeIDDescr,' + @TableName+ '.' + @ColumnName +' as SrcCodeDescr, 1 as IsConverted' +
' from dbo.' + @TableName +
' left outer join SrcCodeMap on ' + @TableName +'.'+ @ColumnName + '= SrcCodeMap.SrcCodeDescr' +
' where LEN(' +@TableName+'.'+@ColumnName + ')>' + convert(varchar,@MaxLength,20)+ ' and JurisID is null'
--Print @sql
OPEN mysql
FETCH NEXT FROM mysql INTO @JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @IsConverted
select @JurisID,@CodedID,@CodeIDDescr,@SrcCodeDescr,@IsConverted
WHILE @@FETCH_STATUS = 0
BEGIN
insert into SrcCodeMap (JurisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted) Values (@JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @DecNumber, @IsConverted)
Set @DecNumber = @DecNumber - 1
FETCH NEXT FROM mysql INTO @JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @IsConverted
END
CLOSE mysql
DEALLOCATE mysql
end
Thanks,
Charmer
September 22, 2011 at 8:27 am
I doubt you really need a cursor but the problem is the select statement for your cursor. Whatever you posted here is a total disaster. It looks like maybe you were using dynamic sql and sort of half removed it. Why do you need dynamic sql here at all? More importantly why do you need a cursor? If this is the extent of your code you could remove the cursor and does this with a single insert statement. It would be easier to understand, easier to debug and most importantly WAY faster.
The error message you got is pretty self explanatory on its own.
_______________________________________________________________
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/
September 22, 2011 at 8:53 am
Hi,
yes, i need dynamic query because i have to pass the table name and column name values through parameters as i shown in the query....i need cursor because i need a column to get decremented for each row insertion...if there is any other way to decrement the column without cursor....let me know that...
Thanks,
Charmer
September 22, 2011 at 9:20 am
prakash 67108 (9/22/2011)
Hi,yes, i need dynamic query because i have to pass the table name and column name values through parameters as i shown in the query....i need cursor because i need a column to get decremented for each row insertion...if there is any other way to decrement the column without cursor....let me know that...
What do you mean by decrement a column? It looks like maybe the purpose of this stored procedure is to receive the table name, column name, and value to run as an insert into the SrcCodeMap table???
If I understand what your proc is doing you could do something like the following:
declare @sql varchar(500)
set @sql = 'select top 5 name from sysobjects'
create table #Sample(name varchar(50))
insert #Sample
exec (@sql)
Using your code the contents of your sproc would be something like;
declare @sql varchar(2000) = 'select 1501 as JurisID, + Cast(' + @CodeID + ' as Varchar(4)) as CodedID, ' + @TableName+ '.' + @ColumnName + ' as CodeIDDescr,' + @TableName+ '.' + @ColumnName +' as SrcCodeDescr, 1 as IsConverted' +
' from dbo.' + @TableName +
' left outer join SrcCodeMap on ' + @TableName +'.'+ @ColumnName + '= SrcCodeMap.SrcCodeDescr' +
' where LEN(' +@TableName+'.'+@ColumnName + ') > + convert(varchar,' + @MaxLength + ',20) and JurisID is null'
--Print @sql
insert into SrcCodeMap (JurisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted)
exec (@sql)
_______________________________________________________________
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/
September 22, 2011 at 9:38 am
yeah thats why i use stored procedure....i pass a parameter value dynamically for decrement... decrement is like...for example: if my dynamic value is 90 if my select query chooses 5 rows, i need a column value in the destination table like 90(first row) ,89(second row) likewise 88,87,86 to be inserted for all the five rows...
thats what i mean for decrement....
the query which i sent works fine if i declare it as sproc...but if i declare it as cursor, i dont get the result...
i hope you understand now
Thanks,
Charmer
September 22, 2011 at 9:41 am
I don't think you can use dynamic sql as the query for a cursor. Just take my example above and run your dynamic sql to fill a temp table and then have your cursor look at the temp table. That should get you there.
_______________________________________________________________
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/
September 22, 2011 at 9:45 am
yeah pal, i know i dont need a cursor to get this done....but for decrementing a column value which is passed dynamically , i came to know that i should use cursor to decrement the value for each row....because cursor is for row by row operation...so i use cursor....
Thanks,
Charmer
September 22, 2011 at 9:49 am
Ahh I missed that part somewhere along the way. You certainly don't need to use a cursor for this. You can use ROW_NUMBER(). I will toss together an example so you can see what I mean.
_______________________________________________________________
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/
September 22, 2011 at 9:51 am
Any chance you can post the ddl for SrcCodeMap along with a few rows of sample data?
_______________________________________________________________
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/
September 23, 2011 at 6:02 am
Hi,
I got it fixed...
Thank you buddy.......
Thanks,
Charmer
September 23, 2011 at 7:02 am
Any chance you can post your solution so others who have this issue can find a solution? Glad you were able to get your issue resolved.
_______________________________________________________________
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/
September 23, 2011 at 8:51 am
Hi,
the miskate where i did was in selecting the query..my select query works fine but when i declared it as cursor, since the column values were getting dynamically...in some way i dont know, the column values were not validating separately... its not matching with the variables which i declared....so what i did was, created a temp DB as stored the vaules into that...and from there i pulled all the values into my destination table using cursor..why i must needed cursor was for,as it does row by row operation...
thanks buddy
Thanks,
Charmer
September 23, 2011 at 8:54 am
glad you could fix it. I still say you don't need a cursor for this, but that is your choice. Feel free to come back and post some ddl and sample data if you want to drop the cursor and make this process lightning fast.
_______________________________________________________________
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/
September 26, 2011 at 4:45 am
HI,
Below is my DDL....Please take a look at this before i say my requirements...i am done with this script....but as you asked my DDL, i show you mine...
CREATE PROCEDURE Sp_Coded (@TableName varchar(15), @ColumnName varchar(10), @CodeID int, @Number bigint, @MaxLength int) AS
BEGIN
DECLARE @MySql varchar(5000)
SET @MySql = 'select 1501 as JurisID,' + Cast(@CodeID as Varchar(4)) + ' as CodedID, ' + @TableName+ '.' + @ColumnName + ' as CodeIDDescr,' + @TableName+ '.' + @ColumnName +' as SrcCodeDescr, 1 as IsConverted' +
' from dbo.' + @TableName +
' left outer join SrcCodeMap on ' + @TableName +'.'+ @ColumnName + '= SrcCodeMap.SrcCodeDescr' +
' where LEN(' +@TableName+'.'+@ColumnName + ')>' + convert(varchar,@MaxLength,20)+ ' and JurisID is null'
IF OBJECT_ID('tempDB', 'U') IS NOT NULL
DROP TABLE tempDB;
CREATE TABLE tempDB (JurisID int,CodedID varchar(4), CodeIDDescr varchar(60), SrcCodeDescr varchar(60), IsConverted int)
INSERT INTO tempDB (JurisID,CodedID,CodeIDDescr,SrcCodeDescr,IsConverted)
EXEC(@MySql)
DECLARE @JurisID int, @CodedID varchar(4), @CodeIDDescr varchar(60), @SrcCodeDescr varchar(60), @IsConverted int, @DecNumber bigint
SET @DecNumber = @Number
DECLARE MySql CURSOR FOR SELECT * FROM tempDB
OPEN Mysql
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @IsConverted
WHILE @@FETCH_STATUS = 0
BEGIN
insert into SrcCodeMap (JurisID, CodeID, CodeIDDescr, SrcCodeDescr, PnxCodeValue, IsConverted) Values (@JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @DecNumber, @IsConverted)
SET @DecNumber = @DecNumber - 1
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @CodeIDDescr, @SrcCodeDescr, @IsConverted
END
CLOSE Mysql
DEALLOCATE Mysql
END
SOURCE TABLE
ID PA Dist Custom1
1 NOR NTR Phoenix1
2 SOU YOU Phoenix2
3 WES PO Phoenix3
4 ES TUM Phoenix4
5 EA TU Phoenix5
DESTINATION TABLE
JurisID CodeID CodeIDDescr SrcCodeDescr PnxCodeValue IsConverted
1501 6002 SOU SOU SO 0
1501 6006 NTR NTR NT 0
1501 6002 EA EA EA 0
now my needs...
i want to insert into the destination table of only whatever the values of PA column whose length is greater than 2 which is not found on SrcCodeDecr column...the corresponding PnxCodevaule column must be decremented for every insertion...like 99 for 1st row,97 for 2nd row and so on....no duplicate values are allowed....for the next run the value should automatically starts with 96 and so on....
Do you get me now, buddy...?
Gimme your idea to get done this....
Thanks
Thanks,
Charmer
September 26, 2011 at 8:22 am
This is really not too difficult but you need to help me help you. I can do the sql for a set based version pretty quickly but at best it is going to be an example because you did not provide me with all the ddl. ddl is the scripts to create your tables. All you posted is the dml (or the code to manipulate your data). I really don't want to spend an hour putting together my best guess at a source table and destination table. Additionally what some test input values would make this a LOT easier. Make sure it is clear what this insert is trying to do. You showed what the data looks like in the "source table" (I assume this must the table that would be referenced by @TableName)??? When you script the tables it makes my life a lot easier if you include some data in a consumable format (insert statements). Here is an example of what I mean by making your data readily consumable.
create table #Source
(
ID int,
PA varchar(5),
Dist varchar(5),
Custom1 varchar(25)
)
insert #Source (ID, PA, Dist, Custom1)
values
(1, 'NOR', 'NTR', 'Phoenix1'),
(2, 'SOU', 'YOU', 'Phoenix2'),
(3, 'WES', 'PO', 'Phoenix3'),
(4, 'ES', 'TUM', 'Phoenix4'),
(5, 'EA', 'TU', 'Phoenix5')
Notice how all you have to do is hit F5 in SSMS and the test data is ready to go. Yes it is going to take you a bit of time to put all this together but keep in mind that nobody on here is paid. We are all volunteers so we ask you to do the legwork to get a tested solution.
If you want to try to tackle this yourself ROW_NUMBER() will get exactly what you need. I really can't quite figure out how you are using @Number and @DecNumber. And for each insert you are decrementing a counter? Where do you get the original value? What are you going to do when the value is less than zero?
_______________________________________________________________
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply