T-sql: Conversion failed when converting the varchar value '61B08729-BB4F-426C-B20B-697F40F458C5' to data type int

Question

I am calling one stored procedure inside another and for some reason SQL Server doesn't like the parameter I am supplying it. Its probably something simple but I am not sure why it doesn't like it.

Stored procedure 1

USE [UserManagerTest]
GO
/****** Object:  StoredProcedure [dbo].[sp_UserManager_Log_User_Off]    Script Date: 11/19/2012 11:33:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================      
-- Author:    Nick Gowdy      
-- Create date: 13-11-2012      
-- Description:  Used by model is UserManager to filter data returned to code          
-- =============================================      
ALTER PROCEDURE [dbo].[sp_UserManager_Log_User_Off] 
                                           @userid           UNIQUEIDENTIFIER

AS 
  BEGIN 

 -- Get SessionID from UserID
 DECLARE @sessionid VARCHAR(60)
 DECLARE @userid uniqueidentifier
 select @userid = '61B08729-BB4F-426C-B20B-697F40F458C5'

 EXEC @sessionid = aspnet_Membership_Custom_GetUserSessionsByUserId @userid
 select @sessionid as sessionid

 -- Delete user session based on sessionid and userid
 EXEC aspnet_Membership_Custom_DeleteUserSession @sessionid, @userid
 END    

Stored procedure 2

USE [UserManagerTest]
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_Custom_GetUserSessionsByUserId]    Script Date: 11/19/2012 12:16:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[aspnet_Membership_Custom_GetUserSessionsByUserId]
    @UserId      uniqueidentifier
AS
BEGIN

declare @SessionId varchar(60)
select @SessionId = ( SELECT    
        [UserId]
    FROM    
        [dbo].[aspnet_Custom_UserSessionActivity]
    WHERE   
        [UserId] = @UserId)

        --select @SessionId

RETURN @SessionId
end

Error is this when I execute sp_usermanager_log_user_off

Msg 245, Level 16, State 1, Procedure aspnet_Membership_Custom_GetUserSessionsByUserId, Line 17
Conversion failed when converting the varchar value '61B08729-BB4F-426C-B20B-697F40F458C5' to data type int.

Any ideas? Thanks!

Answer 1

My best guess is that you defined a PROCEDURE when you wanted in fact a FUNCTION.

Stored procedure returns an int, which is the error code. You are trying to return something else (a uniqueidentifier, that is) and it's not working.

You can also modify your procedure to supply OUTPUT parameters.

MSDN Documentation ( http://msdn.microsoft.com/en-us/library/ms187926.aspx ) :

Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

Answer 2

Use long or float - that string is likely too large to be converted to an integer value