Dynamic SQL Insert Error when inserting INT 3

[jondow]

Hi,

I have the following dynamic SQL query which inserts values in a WHILE loop using a cursor:

	select @sql = 'Insert into [' + @dwrm_ResponseDataTable + '] (dwr_UserID, dwr_UserEmail) values (''' + @UserID + ''',''' + @UserName + ''')'

	exec (@sql)

This works, however if I extend it to include an INT insert as follows:

	select @sql = 'Insert into [' + @dwrm_ResponseDataTable + '] (dwr_UserID, dwr_UserEmail, dwr_cam_id) values (''' + @UserID + ''',''' + @UserName + ''',' + @cam_id + ')'

	exec (@sql)

I get the following error:


Syntax error converting the nvarchar value 'Insert into [tblDWResponseData1] (dwr_UserID, dwr_UserEmail, dwr_cam_id) values ('01F0849A-DC1F-4D22-967A-676C2FD7F666','r@d.com',' to a column of data type int.


Can anyone help with this?

Thanks!

Rick

 

[jondow]

Forgot to mention I'm using SQL Server 2000.

 

[ArunPrithviraj]

Hi Jon,

In case you are trying to use dynamic queries I believe u will be using nvarchar as datatype for ur @sql variable and prefixing an 'N' before ur statement.

Please look into the below code and try to understand how to solve the above issue. In case u have any queries do post back...

declare @dwrm_ResponseDataTable as varchar(100)
declare @UserID varchar(100)
declare @UserName varchar(100)
declare @cam_id varchar(12)
set @cam_id = '123'
declare @sql nvarchar(200)
set @UserName = 'testuser'
set @UserID = '123'
set @dwrm_ResponseDataTable = 'tabletest'

select @sql = N'Insert into ' + @dwrm_ResponseDataTable + ' (id,username, uservalue) values (''' + @UserID + ''',''' + @UserName + ''',' + 'cast (' + @cam_id + ' as int ))'

print @sql

EXECUTE sp_executesql @sql

Thanks,
AP

 

[jondow]

Hi AP,

Thank you for your reply.

I had used:

DECLARE @sql varchar(8000)

DECLARE @UserID varchar(36)
DECLARE @UserName NVARCHAR(50)
DECLARE @cam_id INT

I changed the @cam_id INT to declare @cam_id varchar(12) as you have and that worked! Thanks for that.

Would you recommend that @sql be a nvarchar rather than a varchar?

 

[ArunPrithviraj]

yes, usually while constructing a dynamic query try to use nvarchar that is the usual standard and it has other implications too.

Thanks,
AP

 

[bborissov]

When you use sp_executesql SP there is NO need to build the whole statement and to cast anything:

DECLARE @sql nvarchar(4000)
DECLARE @cam_id int
DECLARE @UserID varchar(200)
DECLARE @UserName varchar(200)

SET @sql = 'INSERT INTO ['+ @dwrm_ResponseDataTable +
               '] (dwr_UserID, dwr_UserEmail, dwr_cam_id)
             values (@spUserID,@spUserName,@spCam_id)'

EXEC master..sp_executesql @sql, N'@spUserID   varchar(200),
                                   @spUserName varchar(200),
                                   @spCam_id   int',
                           @UserID,@UserName,@cam_id

As you see sp_executesql accept at least 3 parameters:
1. the sql statement you want to execute
2. Parameters list which you want to be passed to this statement
3...N values of each of the parameters.
Check the syntax in BOL.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

Why are you doing this in a cursor and dynamically to bef=gin with. Inserts should never be done with a cursor. I fyou do this in a set based fashion, I'll bet you don;t need the dynamic SQL either.

"NOTHING is more important in a database than integrity." ESquared

 

[jondow]

Hi,

Im using dynamic SQL because the tables (and in some cases columns) that I am inserting into are provided by the initial query. Im open to suggestions if there is a better way of doing this, this is the first time I have used cursors. Here is the rest of the SQL (no yet complete):

DECLARE @sql Nvarchar(4000)

DECLARE @UserID varchar(36)
DECLARE @UserName NVARCHAR(50)
DECLARE @cam_id varchar(12)
DECLARE @cur_complete_date varchar(8)
DECLARE @qat_id varchar(12)
DECLARE @res_id INT
DECLARE @res_textbox NVARCHAR(4000)
DECLARE @res_textarea NVARCHAR(4000)
DECLARE @ans_name NVARCHAR(4000)
DECLARE @ans_multiple NVARCHAR(4000)
DECLARE @res_date DATETIME
DECLARE @atp_id INT
DECLARE @dwrm_ResponseDataTable NVARCHAR(50)
DECLARE @dwrm_ResponseDataDetail varchar(12)


DECLARE mycursor CURSOR FOR
SELECT TOP 100 PERCENT 
	CONVERT(VARCHAR(36),dbo.tblResponse.UserID) AS UserID, 
	dbo.aspnet_Users.UserName, 
	CONVERT(varchar,dbo.tblResponse.cam_id) AS cam_id, 
	ISNULL(CONVERT(CHAR(8), dbo.tblCampaignUser.cur_complete_date, 112),'') as cur_complete_date, 
	CONVERT(varchar,dbo.tblResponse.qat_id) AS qat_id,
	dbo.tblResponse.res_id,
	CONVERT(NVARCHAR(4000),dbo.tblResponse.res_textbox) AS res_textbox,
	CONVERT(NVARCHAR(4000),dbo.tblResponse.res_textarea) AS res_textarea,
	CONVERT(NVARCHAR(4000),dbo.tblAnswer.ans_name) AS ans_name, 
	CONVERT(NVARCHAR(4000),'Temp Placeholder') AS ans_multiple,
	dbo.tblResponse.res_date,
	dbo.tblQuestionAnswerType.atp_id,
	dbo.tblDWResponseMapping.dwrm_ResponseDataTable, 
	dbo.tblDWResponseMapping.dwrm_ResponseDataDetail
FROM         
	dbo.tblDWCampaignMapping 
	INNER JOIN dbo.aspnet_Users 
	INNER JOIN dbo.tblResponse ON dbo.aspnet_Users.UserId = dbo.tblResponse.UserID 
	INNER JOIN dbo.tblCampaignUser ON dbo.tblResponse.cam_id = dbo.tblCampaignUser.cam_id AND dbo.tblResponse.UserID = dbo.tblCampaignUser.UserID 
	INNER JOIN dbo.tblDWResponseMapping ON dbo.tblResponse.qat_id = dbo.tblDWResponseMapping.dwrm_QuestionAnswerTypeID ON dbo.tblDWCampaignMapping.dwcm_CampaignID = dbo.tblResponse.cam_id 
	LEFT OUTER JOIN dbo.tblAnswer ON dbo.tblResponse.res_ans_id_single = dbo.tblAnswer.ans_id
	INNER JOIN dbo.tblQuestionAnswerType ON dbo.tblResponse.qat_id = dbo.tblQuestionAnswerType.qat_id
ORDER BY 
	dbo.tblResponse.UserID, 
	dbo.tblResponse.cam_id,
	dbo.tblDWResponseMapping.dwrm_ResponseDataDetail,
	dbo.tblResponse.qat_id

OPEN mycursor

FETCH NEXT FROM mycursor
INTO	
	@UserID, 
	@UserName, 
	@cam_id, 
	@cur_complete_date, 
	@qat_id,
	@res_id,
	@res_textbox,
	@res_textarea,
	@ans_name, 
	@ans_multiple,
	@res_date,
	@atp_id,
	@dwrm_ResponseDataTable,
	@dwrm_ResponseDataDetail

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

	select @sql = N'Insert into [' + @dwrm_ResponseDataTable + '] (dwr_UserID, dwr_UserEmail, dwr_cam_id, dwr_cur_complete_date, dwr_qat_id_' + @dwrm_ResponseDataDetail + ') values (''' + @UserID + ''',''' + @UserName + ''', cast (' + @cam_id + ' as int ), ''' + @cur_complete_date + ''', cast (' + @qat_id + ' as int ))'
	exec (@sql)

   -- This is executed as long as the previous fetch succeeds.
	FETCH NEXT FROM mycursor
	INTO	
		@UserID, 
		@UserName, 
		@cam_id, 
		@cur_complete_date, 
		@qat_id,
		@res_id,
		@res_textbox,
		@res_textarea,
		@ans_name, 
		@ans_multiple,
		@res_date,
		@atp_id,
		@dwrm_ResponseDataTable,
		@dwrm_ResponseDataDetail
END


CLOSE mycursor
DEALLOCATE mycursor

 

[SQLSister]

Use the cursor select instead of values in the insert. No need for a cursor at all if you use a select to provide insert values instead of a values clause. The reason why you avoid curosrs is because they are very slow. Data in databases should be acted on in sets not one record ata time. You won;t notice the slwoness when you test running against a small dat set, but I've seen cursors that went from taking minutes or hours to run to millesconds or minutes. Cursors are very poor performers in the SQL world.

Using the same proc to insert to multiple tables when information is sent in is a poor choice. Much better to have individual procs for each table or or if statements that take you to the proper table insert based on the value of the input variable. This takes longer to create but is usually more efficient to run, more secure and much easier to maintain and far easier to properly test. Dynamic SQl should be avoided as much as possible.

"NOTHING is more important in a database than integrity." ESquared

 

[jondow]

Hi, thanks for the comments, its good to learn how I could do things better. I didnt really understand what you meant here

Use the cursor select instead of values in the insert.

Could you explain with an example if you have time?

Thanks!

 

[SQLSister]

example

insert into table1 (field1, field2, field3)
select field1, field2, filed 3 from table2 t2
join table3 t3 on t2.idfiled = t3.idfield
where t3.field4 = 'test'

Use your insert statement in the cusor for the insert part and your select statment in the cursor where you currently have the values clause (making sure the fields match up). And take out all the cursor code.

"NOTHING is more important in a database than integrity." ESquared

 

[jondow]

Oh I see. Thanks!