May 9, 2003 at 11:23 am
I have created a stored procedure to delete from one table and insert into two others as long as a record doesn't exist. But I can't get the dynamic bit to work. Everything works OK until the last two lines (see below) - the two insert statements. I'm not sure what I'm doing wrong.
Can anyone help?
Here is my sp:
CREATE PROCEDURE InsertOr
@lastName varchar(35), @firstName varchar(35), @sqlVals varchar(1600), @strTransId int
AS
DELETE FROM tempTrans WHERE transID = @strTransId
IF EXISTS(SELECT * FROM translators WHERE Name = @lastName AND firstName = @firstName)
BEGIN
SELECT 'record exists'
END
ELSE BEGIN
declare @strSqlTransExe varchar(1600)
SET @strSqlTransExe = 'insert into translators values (' + @sqlVals + ')'
declare @strSqlBackupExe varchar(1600)
SET @strSqlBackupExe = 'insert into backUpTrans values (' + @sqlVals + ')'
SELECT @strSqlTransExe
EXEC (@strSqlTransExe)
EXEC (@strSqlBackupExe)
END
May 9, 2003 at 11:37 am
Does the @sqlVals variable has multiple values to insert?
Can you post the result of
SELECT @strSqlTransExe ?
May 9, 2003 at 12:37 pm
Yes, it does insert multiple values. Sorry. I should have said that.
The results of the SELECT are: insert into translators values ('Israel','Lisa','53 Dartmout Park Road','London','NW5 1SL','UK','0207 485 2874','0207 267 4469','0207 485 2874','fdl4712@aol.com','French','Spanish','English',,'some stufff about I''m not sure what',,'ITI member','English','0','None','None','None',,,,,'extra','I can translate something',66,09/05/03)
I put "" in for the blank fields. I tried vbscript null as well.
I really appreciate your help.
May 9, 2003 at 12:52 pm
Can you also post the error you recieve?
Why don't try to execute manually the result of the SELECT @strSqlTransExe and see if it inserts right? If it doesn't, also post the error here.
Maybe you should also check that the numbers of values is equal to the columns of the translator table.
And also could be a problem of a single quote that is missing or something more...
May 9, 2003 at 2:04 pm
There is no error. The delete happens, the IF works, but the insert fails silently.
The sqlVals variable is generated from a for...each loop from a SELECT * recordset on an ASP page so all columns should be there. Similarily, the apostrophes go on every field except for the id (an integer) and the date (smalldatetime).
I tried doing it manually and it does generate an error but this is from an asp page. This is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Israel'
Thanks very mcuh
May 9, 2003 at 2:57 pm
No, that's wrong. It's "incorrect syntax near ','"
May 9, 2003 at 9:01 pm
This works... Notice how I doubled up on the single quotes. I think that may be your problem.
CREATE TABLE test (col1 char(1), col2 char(1))
go
CREATE PROC test_it @sqlVals varchar(10)
as
begin
declare @sql varchar(255)
select @sql = 'insert into test values('+@sqlVals+')'
EXEC (@sql)
end
go
exec test_it '''a'',''b'''
May 10, 2003 at 5:16 am
You can avoid the exec altogether if you just put all the column values as paramters to the proc. You could also handle the backup work by putting a trigger on the table and doing it there, that way you would never miss an insert if someone edited the table directly.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply