Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error in Stored Procedure: 1

Status
Not open for further replies.

hunterspot

Programmer
Jun 3, 2009
22
US
Hi
I have written this stored procedure to delete records from a look up table. The table has only two fields and the structure is:
1. code varchar(4)
2. description varchar(150).

Here is the code of the stored proc.:

************
-- ================================================================
-- Author: *******
-- Create date: 02/05/2010
-- Description: Deletes record in the Table
-- =================================================================
CREATE PROCEDURE [dbo].[usp_Delete_From_Lookuptable]
(
@TableName varchar(50)
,@Code varchar(4)
)
AS
BEGIN
SET NOCOUNT ON
SET @TableName = RTRIM(@TableName)
declare @sqlstring varchar(250)
set @sqlstring = 'delete from ' + @tablename + 'where code = ' + @code

exec(@sqlstring)

IF @@ERROR = 0 AND @@ROWCOUNT = 1
BEGIN
RETURN 1
END
ELSE
BEGIN
RAISERROR('Error while Deleting from the table', 16,1)
RETURN -1

END
END


GO
**************
This is giving error "Syntax Error converting the varchar value 'A1' tp column of data type Int.
Will you please tell me how to fix this?
Thanks in advance.
 
change:

set @sqlstring = 'delete from ' + @tablename + 'where code = ' + @code

To:

Code:
set @sqlstring = 'delete from [' + @tablename + '] where code = ''' + @code + ''''

First... the square brackets protect you from table names that are reserved words and table names that contain spaces.

Second, notice all those single-quotes at the end. This is what should fix your error.

Also, I would suggest another change to your procedure. One that will help prevent SQL Injection.

Code:
-- ================================================================
-- Author: *******
-- Create date: 02/05/2010
-- Description: Deletes record in the Table
-- =================================================================
CREATE PROCEDURE [dbo].[usp_Delete_From_Lookuptable]
(
@TableName     varchar(50)
,@Code      varchar(4)
)
AS
BEGIN
SET NOCOUNT ON
SET @TableName = RTRIM(@TableName)

If Not Exists(Select * From Information_Schema.Tables Where Table_Name = @TableName)
  Begin
    RAISERROR('Hacker Alert: Table does not exist.', 16,1)
    RETURN -1
  End

declare @sqlstring varchar(250)
set @sqlstring = 'delete from [' + @tablename + '] where code = ''' + @code + ''''

exec(@sqlstring)

IF @@ERROR = 0 AND @@ROWCOUNT = 1
BEGIN
    RETURN 1
END
ELSE
BEGIN
    RAISERROR('Error while Deleting from the table', 16,1)
    RETURN -1

END
END


GO


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top