Incorrect syntax near keyword 'WHERE' problem 2

[willydude]

I am writing some UPDATE sprocs. One is below.
When I execute the procedure, enter an EthnicityCode and a new description, I get back the error message "Incorrect syntax near keyword 'WHERE'". Note that the ALTER works OK. It's just when I execute that I get the error.

TIA,

Bill

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Admin_UpdateEthnicityCodes]
(@EthnicityCode varchar (2), 
@Description varchar (50),
@Message varchar (50) output)

as

--declare variables
Declare
@Continue bit,
@SQL varchar (500)
SET @Continue = 1
SET @Message = ''

Begin TRANSACTION Admin_UpdateEthnicityCodes
--validate that the Ethnicity code entered by the user
--exists in the table
IF @EthnicityCode IS NULL OR datalength(@EthnicityCode) = 0
BEGIN
SET @Message = 'Please Enter an Ethnicity Code'
SET @Continue = 0
End
ELSE
BEGIN  --start building the SQL string. Note UPDATE and SET keywords
SET @SQL = 'UPDATE tbl_Admin_EthnicityCodes SET ' 
--note single quotes 
--note space before and after SET above

IF Datalength(LTRIM(@Description)) > 0 --there is a desc update
BEGIN  --put new description info into @SQL variable 
--	with first @SQL line from above
SET @SQL = @SQL + 'Description = ''' + @Description + ''','
END

--finish building the SQL string
Set @SQL = @SQL + 'WHERE EthnicityCode = ' + @EthnicityCode

--Execute the SQL String
EXECUTE(@SQL)
--check for errors
IF @@Error <> 0  --there is an error
Begin  --if error, Rollback Tranaction
RollBack Transaction Admin_UpdateEthnicityCodes
RaisError ('Update Failed.', 18, 1)
Return 99
end
ELSE  --no errors. COMMIT Transaction
Commit Transaction
Return 0
end

 

[r937]

i'm gonna guess that it is a dangling comma in front of the word WHERE

r937.com | rudy.ca

 

[willydude]

I believe you are correct. I forgot the following:

If Right(@SQL, 1) = ','
  Set @SQL = Left(@SQL, Len(@SQL)-1)
  --finish building the SQL string
Set @SQL = @SQL + 'WHERE EthnicityCode = ' + @EthnicityCode
end

But with the above in the sproc, when I enter an Ethnicity Code of 'A', or 'W' or whatever, I now get the message "Invalid column name 'A'."

A, W, etc. are not column names, but values in the column.

Bill

 

[bborissov]

You must make the value of @EthnicityCode a string for dynamic SQL:

Set @SQL = @SQL + 'WHERE EthnicityCode = ''' + @EthnicityCode+''''

(check the numbers of quetes, I am not sure if they are exactly as they needed

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[willydude]

Thnaks for the help.

Do you see anything I can do to "tighten up" or improve the sproc above?

Bill

 

[bborissov]

You don't need dynamic SQL at all here, you could do the code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Admin_UpdateEthnicityCodes]
(@EthnicityCode varchar (2),
@Description varchar (50),
@Message varchar (50) output)

as

--declare variables
Declare
@Continue bit,
SET @Continue = 1
SET @Message = ''

--validate that the Ethnicity code entered by the user
--exists in the table
IF @EthnicityCode IS NULL OR datalength(@EthnicityCode) = 0
   BEGIN
       SET @Message = 'Please Enter an Ethnicity Code'
       SET @Continue = 0
   End
ELSE
   BEGIN  
       IF Datalength(LTRIM(@Description)) > 0
          -- There is no need to start transaction if
          -- wrong parameters are passed.
          Begin TRANSACTION Admin_UpdateEthnicityCodes

          --there is a desc update
          BEGIN
              UPDATE tbl_Admin_EthnicityCodes
                     SET Description = @Description
              WHERE EthnicityCode = @EthnicityCode
          END

          --check for errors
          IF @@Error <> 0  --there is an error
             Begin  --if error, Rollback Tranaction
                 RollBack Transaction
                          Admin_UpdateEthnicityCodes
                 RaisError ('Update Failed.', 18, 1)
                 Return 99
             end
          ELSE  --no errors. COMMIT Transaction
             Commit Transaction
             Return 0
   end

(not tested at all)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP