Syntax error converting the varchar value 'admi' to a column of datatype int.

[Franck]

hello,
I have a pb with a stored procedure:
simple one:
CREATE PROCEDURE dbo.loginRoles(@Role varChar (60) output,
@UserId varChar (60) )
as
declare @nbreRow int
begin
set @nbreRow = (select count(*)
FROM [Northwind].[dbo].[EmailId]
where [UserId]=@UserId)
if ( @nbreRow>0 )
begin
set @Role = ----------------------
(select [roles ]
FROM [Northwind].[dbo].[EmailId]
where [UserId]=@UserId)
end
else
begin
set @Role ='generic' ----------------------
end
return @Role
end

If I assign the value in this statement: set @Role ='generic'
or if it does enter the condition the same message appear


System.Data.SqlClient.SqlException: Syntax error converting the varchar
value 'admi' to a column of data type int.

any ideas ?
thanks

 

[Lale Divringi [MSFT]]

You can only return 'int' types from your stored procedure. Since @Role is
not of type int, it is causing the error.

You already have @Role declared as an output parameter and have set its
value. Therefore you don't need the "return @Role" statement in your stored
procedure. Just remove that line and everything should work.


DISCLAIMER: This posting is provided "AS IS" with no warranties, and confers
no rights.

 

[Franck]

Thanks a lot
it solved my pb!

You can only return 'int' types from your stored procedure. Since @Role is
not of type int, it is causing the error.

You already have @Role declared as an output parameter and have set its
value. Therefore you don't need the "return @Role" statement in your stored
procedure. Just remove that line and everything should work.


DISCLAIMER: This posting is provided "AS IS" with no warranties, and confers
no rights.

hello,
I have a pb with a stored procedure:
simple one:
CREATE PROCEDURE dbo.loginRoles(@Role varChar (60) output,
@UserId varChar (60) )
as
declare @nbreRow int
begin
set @nbreRow = (select count(*)
FROM [Northwind].[dbo].[EmailId]
where [UserId]=@UserId)
if ( @nbreRow>0 )
begin
set @Role = ----------------------
(select [roles ]
FROM [Northwind].[dbo].[EmailId]
where [UserId]=@UserId)
end
else
begin
set @Role ='generic' ----------------------
end
return @Role
end

If I assign the value in this statement: set @Role ='generic'
or if it does enter the condition the same message appear


System.Data.SqlClient.SqlException: Syntax error converting the varchar
value 'admi' to a column of data type int.

any ideas ?
thanks