Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 insert problem

Author  Topic 

johnson
Starting Member

1 Post

Posted - 2001-12-24 : 02:34:23
i have written a stored procedure where i fetch several records with a maximum of 10 records or less a time, And these records are to be inserted as a single record in another table. After i fetch all the records from the source table, pad it to a string variable and try to insert i get an error. The source is below, plz let me know can i achive this insert.



drop proc sp_Attribute
go
create proc sp_Attribute
as
declare @lsSql varchar(250),@lsItemId varchar(50) ,@liAttrCount integer , @liTemp integer
declare @item_id Varchar(13),@Item_desc varchar(30),@attr_id varchar(13),@attr_desc varchar(30)
declare @itemcount int, @count int, @attcnt int, @field varchar(255)

declare cr_item cursor for
select item_ID, item_desc from Item order by Item_ID
select @itemcount = count(*) from item
declare @ivl int
open cr_item
Delete from Item_attribute_Report
while (@itemcount <> 0 )
begin
fetch next from cr_item into @item_ID, @item_desc
declare cr_Attribute cursor for
select attribute_header.attr_id, attribute_header.attr_desc
from item,item_attribute, attribute_header,attribute_detail
where item.Item_id = Item_attribute.item_id and
attribute_header.attr_id = attribute_detail.attr_id and
attribute_header.attr_id = Item_attribute.attr_id and
item_attribute.value_id = attribute_detail.value_id
and item_attribute.item_ID = @item_ID
order by item.item_id
select @count = count(*) from item,item_attribute, attribute_header,attribute_detail where item.Item_id = Item_attribute.item_id and
attribute_header.attr_id = attribute_detail.attr_id and
attribute_header.attr_id = Item_attribute.attr_id and
item_attribute.value_id = attribute_detail.value_id
and item_attribute.item_ID = @item_ID
select @lsItemid = '';
select @liAttrCount = 0;
select @attcnt = @count
open cr_attribute
Select @ivl = 1
while ( @count <> 0)
begin
fetch next from cr_attribute into
@attr_id ,@attr_desc
select @LsSQL = @LsSQL+'' +@attr_id + ', '
print @lsSql
select @count = @count - 1;
select @ivl = @ivl +1
end;
close Cr_Attribute
deallocate Cr_Attribute
while (@attcnt <> 10)
begin
select @LsSQL = @LsSQL+''''', '
select @attcnt = @attcnt + 1
end
select @LsSQL = left(@LsSQL ,len(@LsSQL )-1)
insert into Item_Attribute_Report(Item_ID, Item_Desc,ATTR1,ATTR2,ATTR3,ATTR4,ATTR5,ATTR6,ATTR7,ATTR8,ATTR9,ATTR10)
VALUES(@item_Id, @Item_Desc,@LsSQL)
--@LsSQL is the padded string for all the ATT1 to Att10 values, i get the error 'cause there is no equal no of values for the no of fields given, for which i have padded but it does not work, so tell how can this be achieved.
select @itemcount = @itemcount -1
end
close cr_item
deallocate cr_item


Nazim
A custom title

1408 Posts

Posted - 2001-12-24 : 07:26:09
On first look,your error says you are not passing the no. of values in your insert

like eg:

insert into a(sno,sname) values(12)

this statement will give a error coz there are two values expected and i pass only one.

just print @lsql and check what xactly it returns 10 values each seperated with a comma.
something like this

10,340,35...


-------------------------
Graz's Baby is my Master:)
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-24 : 11:37:40
You also might want to read this article on COALESCE [url]http://www.sqlteam.com/item.asp?ItemID=2368[/url] to see if you can get rid of that mean old ugly nasty cursor you're using. Perhaps COALESCE in conjunction with a SET ROWCOUNT 10 or something similar will get the results you want in a more efficient manner.

Go to Top of Page
   

- Advertisement -