CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
George
George

Reputation: 113

How are strings (varchar) passed into SQL stored procedures?

I am trying to pass a string into a stored procedure. My code is:

CREATE PROCEDURE AddCondition
    @CondName varchar(50),
    @CondDesc varchar(50)
AS
    DECLARE @CondID int

    --This code gets the next ID for the table
    SET @CondID = (SELECT MAX([Condition ID]) FROM [dbo].[Tube Conditions]) + 1
    SET @CondID = ISNULL(@CondID,1)

    --This code adds records to the table
    INSERT INTO [dbo].[Tube Conditions] ([Condition ID],[Condition Name],[Description])
    VALUES (@CondID, @CondName, @CondDesc)
GO

I try to execute with:

DECLARE @aName varchar(50), @aDesc varchar(50)
SET @aName = 'Lathed'
SET @aDesc = 'The tube has been lathed to size'

EXECUTE AddCondition @aName, @aDesc
GO

But I keep getting the error:

Msg 245, Level 16, State 1, Procedure AddCondition, Line 51
Conversion failed when converting the varchar value 'Lathed' to data type int.

This same process has worked when dealing with int and float values, so I'm not sure what I'm missing here. I am using SQL Server 2014 Management Studio with SQL Server Express.

Thanks in advance!

Upvotes: 1

Views: 69

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Agree with Jamiec you probably have [dbo].[Tube Conditions] of type int

a single statement is a transaction 

INSERT INTO [dbo].[Tube Conditions] ([Condition ID],[Condition Name],[Description])
SELECT MAX(isnull([Condition ID],0)) + 1, 'Lathed', 'The tube has been lathed to size'
FROM [dbo].[Tube Conditions]

Upvotes: 0

Jamiec
Jamiec

Reputation: 136114

From the code you have posted there is only 1 thing that will generate the error you specify, and that is if the [Condition Name] column in table [dbo].[Tube Conditions] is of type int, rather than varchar as expected by the insert statement.

As an aside: this is a very dangerous way to generate the next ID in a table - its screaming for concurrency issues when 2 records get inserted at the same time. SQL will generate this identity for you, look it up.

Upvotes: 5

Related Questions