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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 insert problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnson
Starting Member

1 Posts

Posted - 12/24/2001 :  02:34:23  Show Profile  Reply with Quote
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

United Arab Emirates
1408 Posts

Posted - 12/24/2001 :  07:26:09  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 12/24/2001 :  11:37:40  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
You also might want to read this article on COALESCE http://www.sqlteam.com/item.asp?ItemID=2368 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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000