May 8, 2003 at 3:11 am
I am trying to create a SP via an asp page but when I try to execute the statement, I get the error message "incorrect syntax near )".
This is the SP I am trying to create:
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
SELECT 'Record Added'
INSERT INTO translators VALUES (@sqlVals)
INSERT INTO backUpTrans VALUES (@sqlVals)
What am I doing wrong?
May 8, 2003 at 3:30 am
You forgot the 'END' to go with the 'ELSE BEGIN' line
May 8, 2003 at 3:30 am
You do not have a END at the very end of the proc to complete the ELSE BEGIN.
Jeremy
May 8, 2003 at 4:02 am
Sorry. that was stupid.
But now I get another error.
'Insert Error: Column name or number of supplied values does not match table definition'
The column values are supplied in an parameter. Is there any way I can get the parameter accepted?
May 8, 2003 at 4:09 am
If you don't specify the columns in an insert statement, then the columns you insert much match the columns in the table.
What I suspect you are trying to do is put all of the values in a single variable and use that one variable in the insert statement. However, SQL Server only sees one value to insert and not a series of values.
Either you have to specify the whole list of values as separate variables in the insert statement or you could use dynamic sql for the insert statment:
declare @strSqlString nvarchar(1000)
set @strSqlString = N'insert into translators values (' + @sqlVals + N')'
print @strSqlString /* For testing purposes */
exec sp_executesql @strSqlString /* Executes the dynamic sql string */
If you print out the dynamic sql you can see exactly what SQL Server will execute. Make sure that you have balanced single quotes.
Jeremy
May 8, 2003 at 5:41 am
Thanks Jeremy. You were right in your assumption and I am going to try and use dynamic sql though it looks hairy. Fingers crossed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply