CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Saurabh Moses Ram
Saurabh Moses Ram

Reputation: 59

Cannot convert varchar value to int in stored procedure in SQL Server

I have this stored procedure 

create procedure [dbo].[CreateNewTicket]
    (@Practice_Id as varchar(40),
    )
  /* insert some rows into TICKET table */
As Begin
   DECLARE @prctid as int

   SELECT @prctid = 'select id from PRACTICE_DETAIL where Practice_Name_Description = ' + @Practice_Id + ';'
 end 
 GO

But whenever I am passing value from my C# to stored procedure it is throwing error.

Conversion failed when converting the varchar value 'select id from PRACTICE_DETAIL where Practice_Name_Description = Bankson PT- Daniel (DBA);' to data type int.

Can anyone explain what I am doing wrong?

Upvotes: 1

Views: 2352

Answers (7)

Jarvis Stark
Jarvis Stark

Reputation: 611

You have to set the selected value to integer variable in select statement.

Use the following code:-

SELECT @prctid = id from PRACTICE_DETAIL where Practice_Name_Description = @Practice_Id

Upvotes: 2

Saurabh Moses Ram
Saurabh Moses Ram

Reputation: 59

thank you all. it's working like a charm..

i make the changes like

  DECLARE @prctid as int
  SELECT @prctid = id from PRACTICE_DETAIL where Practice_Name_Description = @Practice_Id

Upvotes: 0

Tim
Tim

Reputation: 28530

As Jarvis put in his answer, you need to assign an INT to the variable @prctid. As your statement is posted, you're trying to assign a VARCHAR because of the single quotes:

'select id from PRACTICE_DETAIL where Practice_Name_Description = ' + @Practice_Id + ';'

This will result in a VARCHAR, hence the error.

What you're looking for is:

SELECT @prctid = select id from PRACTICE_DETAIL where Practice_Name_Description = @Practice_Id

No quotes.

Upvotes: 1

jayvee
jayvee

Reputation: 160

Missing single quote. Try this.

SELECT @prctid = 'select id from PRACTICE_DETAIL where Practice_Name_Description = ''' + @Practice_Id + ''';'

Upvotes: -1

Manoj Reddy
Manoj Reddy

Reputation: 134

Try this

DECLARE @prctid as varchar(max)

SELECT @prctid = "select id from PRACTICE_DETAIL where Practice_Name_Description =  '" + @Practice_Id + "';"

Upvotes: -1

Hitesh
Hitesh

Reputation: 3498

try this

create procedure [dbo].[CreateNewTicket]
(
 @Practice_Id as varchar(40),
)
/* insert some rows into TICKET table */

As Begin
DECLARE @prctid as int
SELECT @prctid = id from PRACTICE_DETAIL where Practice_Name_Description = @Practice_Id;
end 
GO

Upvotes: 1

Dhrumil
Dhrumil

Reputation: 3204

The id field must of type varchar. That is why you are receiving this error.

You must either return an int value or make @prctid as varchar type to make sure you get a proper value.

Hope this helps.

Upvotes: 0

Related Questions