Conversion failed when converting the varchar value to data type int.

Question

0.00/5 (No votes)

See more:

Hi,
my sp as below,

SQL

ALTER PROCEDURE [dbo].[SpGetAllSelectedDepartments]
	@LocationId int,
	@DepartmentIds varchar(50) 
AS
BEGIN
  if @LocationId=0
	begin
		select SdeptId,SdeptDepartmentName,SdeptSlocId from SgDepartment where SdeptStatus =1 and SdeptId in(@DepartmentIds)order by SdeptDepartmentName;
	end
  else
	begin
		select SdeptId,SdeptDepartmentName,SdeptSlocId from SgDepartment where SdeptStatus =1 and SdeptSlocId=@LocationId 
		and SdeptId  in (@DepartmentIds)
		order by SdeptDepartmentName;

	end
END

but getting error when execute the sp as below,

EXEC SpGetAllSelectedDepartments 2,'18,6'

error:-

Conversion failed when converting the varchar value '18,6' to data type int.
where SdeptId column datatype is int.
why? :(

Posted 31-Dec-14 0:11am

hasbina

Updated 31-Dec-14 0:18am

v2

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abraham Perl · Answer 1 · 2014-12-31T09:57:00

execute this, which creates a table function that parses a comma delimited value into a table, join with that table

SQL

ALTER FUNCTION [dbo].[fn_MultiValueParameter]
    (@Parameter NVARCHAR(MAX))
RETURNS @Table TABLE (Value NVARCHAR(250))
AS
BEGIN

WHILE CHARINDEX(',',@Parameter) > 0
    BEGIN
        INSERT @Table VALUES (LEFT(@Parameter,charindex(',',@Parameter)-1))
        set @Parameter = LTRIM(Replace(@Parameter,left (@Parameter,charindex(',',@Parameter)),''))
    END

    INSERT INTO @Table VALUES (@Parameter)

RETURN
END

SQL

ALTER PROCEDURE [dbo].[SpGetAllSelectedDepartments]
    @LocationId INT,
    @DepartmentIds VARCHAR(50)
AS
BEGIN
    IF @LocationId = 0
        BEGIN
            SELECT  SdeptId, SdeptDepartmentName, SdeptSlocId
            FROM    SgDepartment INNER JOIN fn_MultiValueParameter( @DepartmentIds ) 
		  ON SdeptId = Value
            WHERE   SdeptStatus = 1
            ORDER BY SdeptDepartmentName;
        END
    ELSE
        BEGIN
            SELECT  SdeptId, SdeptDepartmentName, SdeptSlocId
            FROM    SgDepartment INNER JOIN fn_MultiValueParameter( @DepartmentIds ) 
		  ON SdeptId = Value
            WHERE   SdeptStatus = 1 AND
                    SdeptSlocId = @LocationId
            ORDER BY SdeptDepartmentName;
         END
END

Sisir Patro · Answer 2 · 2014-12-31T00:22:00

Hi,

Here the SQL server considers the "18,6" into a single element and tries to convert that into integer and fails as "," can't be converted into integer.

So you should not use these kind of strings for your purpose. The only way to do it is you have to make it work is dynamic query.

Read the following article for the dynamic query.

Thanks
Sisir Patro

Sumit Jawale · Answer 3 · 2015-01-01T17:51:00

Solution 3

Hi,
Instead of passing comma separated value you can pass table as input parameter to your stored procedure

http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx[^]

Posted 1-Jan-15 17:51pm

Sumit Jawale

Conversion failed when converting the varchar value to data type int.

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Conversion failed when converting the varchar value to data type int.

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us