May 6, 2008 at 8:07 am
Here is the error message for code which is very ineffecient. I'm curious why my insert string produces this error and how do I make the insert string variable work.
Syntax error converting the nvarchar value 'INSERT INTO DBO.TMPVSVIEW_PO_ACCRUAl_RCHESS_HDR(ponum , podate ,office ,vendnum ,vendname ,status ,Acreation_date ,poitem_org ,item ,descr ,vendorpn,qty ,price ,fullrecv ,
Bcreation_date, qty_recdv) VALUES (' to a column of data type int.
---
--- Actual Code
---
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
DECLARE @EXE_DATEsmalldatetime
DECLARE @SQLLOGINVARCHAR (30)
DECLARE @TBLNAMEVARCHAR (8000)
DECLARE @STR1 NVARCHAR (4000)
DECLARE @STR2VARCHAR (4000)
DECLARE @DELIMITER VARCHAR(100)
DECLARE @SSQL AS VARCHAR(4000)
-- **********************************************************************************************************************************
-- INITILIZING VARIABLES
-- **********************************************************************************************************************************
SET @SQLLOGIN= SYSTEM_USER
SET @EXE_DATE= '2008-04-30'
SET @TBLNAME = 'DBO.TMPVSVIEW_PO_ACCRUAl_'+UPPER(RIGHT(@SQLLOGIN,LEN(@SQLLOGIN)-4))+'_HDR'
SET @STR1 = 'DROP TABLE '+@TBLNAME
EXEC (@STR1)
SET @STR2 = 'CREATE TABLE '+@TBLNAME+'(
ponum int NOT NULL ,
podate smalldatetime ,
office nvarchar (6) ,
vendnum nvarchar (30) ,
vendname nvarchar (60) ,
status nvarchar (1) ,
Acreation_date smalldatetime ,
poitem_org smallint ,
item nvarchar (20) ,
descr nvarchar (255),
vendorpn nvarchar (50),
qty float,
price money,
fullrecv bit,
Bcreation_date smalldatetime,
qty_recdv float)'
EXEC(@STR2)
-- build insert string
set @STR1 = 'INSERT INTO '+@TBLNAME+'(ponum , podate ,office ,vendnum ,vendname ,status ,Acreation_date ,poitem_org ,item ,descr ,vendorpn,qty ,price ,fullrecv ,
Bcreation_date, qty_recdv) VALUES ('
SET @DELIMITER = ','
--SET @DELIMITER = REPLACE(@DELIMITER, '"', )
--
Declare po_info_cursor CURSOR
for
select
A.ponum , A.podate , A.office , A.vendnum , A.vendname , A.status
, A.creation_date , B.poitem_org , B.item , B.vendorpn , B.descr
, B.qty , B.price , B.fullrecv , B.creation_date
FROM dbo.pohdr A, dbo.poitem B
where A.ponum = b.ponum
and A.podate <= @EXE_DATE
and A.ponum = 33936
--
-- PO HEADER CURSOR
--
DECLARE @ponum int , @podate smalldatetime ,@office nvarchar(6), @vendnum nvarchar(30), @vendname nvarchar(60) , @status nvarchar(1), @pohdr_creation_date smalldatetime
--
-- PO LINE CURSOR
--
DECLARE @poitem_org smallint , @item nvarchar (20) , @vendorpn nvarchar(50) , @item_descr nvarchar(255) , @qty float , @price money , @fullrecv bit , @poitem_creation_date smalldatetime
DECLARE @qty_recdv float
OPEN po_info_cursor
fetch next from po_info_cursor
into @ponum , @podate , @office, @vendnum , @vendname , @status , @pohdr_creation_date , @poitem_org , @item , @vendorpn
, @item_descr , @qty , @price , @fullrecv , @poitem_creation_date
while @@fetch_status = 0
Begin
PRINT 'PO# '+CAST(@PONUM AS CHAR(10));
PRINT 'lINE '+CAST(@poitem_org AS CHAR(10));
Declare po_recdv_cursor CURSOR
for select sum(qty) from poallocrecv PR
where CAST(PR.ponum AS VARCHAR(10)) = CAST(@ponum AS VARCHAR(10))
and CAST(PR.poitem_org AS VARCHAR(10)) = CAST(@poitem_org AS VARCHAR(10))
and PR.recvdate <= @EXE_DATE
group by PR.ponum ,PR.poitem_org ;
OPEN po_recdv_cursor
fetch next from po_recdv_cursor into @qty_recdv
Close po_recdv_cursor
DEALLOCATE po_recdv_cursor
SET @SSQL = @STR1
+ @ponum
+ @DELIMITER + @podate
+ @DELIMITER + @office
+ @DELIMITER + @vendnum
+ @DELIMITER + @vendname
+ @DELIMITER + @status
+ @DELIMITER + @pohdr_creation_date
+ @DELIMITER + @poitem_org
+ @DELIMITER + @item
+ @DELIMITER + @item_descr
+ @DELIMITER + @vendorpn
+ @DELIMITER + @qty
+ @DELIMITER + @price
+ @DELIMITER + @fullrecv
+ @DELIMITER + @poitem_creation_date
+ @DELIMITER + @qty_recdv
+')'
PRINT @SSQL
fetch next from po_info_cursor
into @ponum , @podate, @office , @vendnum , @vendname , @status , @pohdr_creation_date , @poitem_org , @item , @vendorpn , @item_descr , @qty , @price , @fullrecv , @poitem_creation_date
End
Close po_info_cursor
DEALLOCATE po_info_cursor
May 6, 2008 at 12:27 pm
The error means you're trying to add numeric (int) values to a string. You have to cast/convert them first.
It's when you start adding values to the list of what to insert.
Now, with that out of the way, is there a reason that you're using a cursor for this, instead of something like:
set @STR1 = 'INSERT INTO '+@TBLNAME+'(ponum , podate ,office ,vendnum ,vendname ,status ,Acreation_date ,poitem_org ,item ,descr ,vendorpn,qty ,price , fullrecv ,
Bcreation_date, qty_recdv)
SELECT A.ponum , A.podate , A.office , A.vendnum , A.vendname , A.status
, A.creation_date , B.poitem_org , B.item , B.vendorpn , B.descr
, B.qty , B.price , B.fullrecv , B.creation_date
FROM dbo.pohdr A, dbo.poitem B
where A.ponum = b.ponum
and A.podate <= @EXE_DATE
and A.ponum = 33936
(I haven't checked the above for the sequence of columns, I'm just illustrating the principle.)
A statement like that will be faster than a cursor, and easier to work with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 2:14 pm
Thank you - using the cast function while building the string for the insert worked.
Also I understand that the code inititally presented was totaly ineffeceint which I stated in in my original question. I was really after trying to get the concept down for building an Insert statment using a string.
Once again thank you for your help.
RC
May 7, 2008 at 7:49 am
Understood on the efficiency thing.
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply