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.
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_Attributegocreate proc sp_Attributeas 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 -1end 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 insertlike 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 this10,340,35...-------------------------Graz's Baby is my Master:) |
|
|
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. |
|
|
|
|
|
|
|