INSERT fails in stored proc, works in Query Analyzer 2

[cwadams1955]

Got something weird here. I have an INSERT query inside a stored proc, used to copy records to a new record. It's been working previously; yesterday the table layouts were changed to add a single identity column as the primary key to the table, now the INSERT fails. It doesn't give an error (it hits the IF @@ERROR statement, then continues on,) and if I set a breakpoint, RETURN the SQL statement as created, and copy it into Query Analyzer, it runs perfectly - the new record is inserted exactly as it should be. Here's the relevant code from the stored procedure:

CREATE PROCEDURE dbo.sp_CopyBillRecords

@WEDateFrom             datetime,
@WEDateTo               datetime,
@ProjectFrom            varchar(10),
@ProjectTo              varchar(10),
@TaskFrom               varchar(50), 
@TaskTo		        varchar(50), 
@ServiceAreaIn          varchar(4) , 
@ErrCode                integer = 0 OUTPUT,
@BillRecordNo           bigint OUTPUT

AS

DECLARE	
/* -------------------------------------------
Temp variables for header record
---------------------------------------------- */
@BillRecNo              bigint,
@ProjectNo              varchar (10), 
@TaskNo		        varchar (50), 
@WEDate		        datetime, 
@ServiceArea            varchar (4), 
@Client	                varchar (255), 
@ReportDate             datetime, 
@Status		        smallint, 
@ApprovedBy             varchar (20), 
@ApprovalDate           datetime, 
@Complete               bit, 
@Comment                varchar (50), 
/* -------------------------------------------
Temp variables for Working fields
---------------------------------------------- */
@SqlState               nvarchar (4000),
@NewSequenceNo          bigint,
@NewBillRecordNo        bigint

SET NOCOUNT ON

/* -------------------------------------------
Begin SQL transaction
---------------------------------------------- */
BEGIN TRANSACTION

	/* -------------------------------------------
	Copy Header record first
	---------------------------------------------- */
	SELECT @Client = Client 
	FROM	[dbo].[tblBillRecordHeader] 
	WHERE ([ProjectNo] = @ProjectFrom 
	 AND	[TaskNo] = @TaskFrom 
	 AND	[WEDate] = @WEDateFrom 
	 AND	[ServiceArea] = @ServiceAreaIn
	AND	[BillRecordNo]=@BillRecordNo)

	--exec sp_executesql @SqlState, N'@Client varchar (255) OUTPUT', @Client = @Client OUTPUT

	/* -------------------------------------------
	Set up for new Header record.
	Not all fields will be written out,
	several should use the default values only.
	---------------------------------------------- */
	SET 	@ProjectNo = @ProjectTo
	SET	@TaskNo = @TaskTo
	SET	@WEDate = @WEDateTo
	SET 	@ServiceArea = @ServiceAreaIn

	SET @SqlState = 'INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	(' + dbo.quotestring(@ProjectNo) + ', '
					+ dbo.quotestring(@TaskNo) + ', '
					+ dbo.quotestring(@WEDate) + ', '
					+ dbo.quotestring(@ServiceArea) + ', '
					+ dbo.quotestring(@Client) + ')'

--	return @SqlState

	exec (@SqlState)
	IF @@ERROR<>0
		Begin
			-- Write to header failed
			SET @ErrCode=91
			GOTO TRAN_FAILED
		End

... and here's the copied SQL statement as it's created with the test data:

INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	('175508001', '500.100', 'Aug  8 2008 12:00AM', 'COE', '63430')

When I run the stored proc the error number doesn't change from 0, but when I set a breakpoint and RETURN the SQL statement, this is what I get:

Server: Msg 245, Level 16, State 1, Procedure sp_CopyBillRecords, Line 126
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	('175508001', '500.100', 'Aug  8 2008 12:00AM', 'COE', '63430')' to a column of data type int.

Any ideas as to what gives? Thanks.

 

[bborissov]

As SQLSister said you must use Dynamic SQL only in controlled circumstances and if you have no other choice. But one thing, when you use Dyn. SQL you must ALWAYS use sp_executesql stored procedure. That way you decrease possibilities of SQL Injections.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cwadams1955]

Thanks, I'll keep that in mind. I've been programming for a long time, but SQL Server is relatively new to me.