Storing variables from queries in stored procedures

Question

I have a stored procedure with a username parameter. I want to use one query to grab a userid, then use this variable in further queries.

Here's what I have so far. It compiles OK, but on execution I get an error "Error converting data type varchar to uniqueidentifier."

ALTER PROCEDURE [dbo].[sp_User_delete]
    @username uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @UserId uniqueidentifier;

    -- I guess I need to do something here to convert the ID
    SET @UserId = 
        (SELECT UserId FROM aspnet_Users WHERE UserName=@username);

    SELECT * FROM dbo.aspnet_UsersInRoles WHERE UserId=@UserId;
END

Answer 1

What is the datatype of UserId in aspnet_users?

You can combine your SET/SELECT into a single statement by the way:

SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @username;

Answer 2

-- Proper way to set a variable, will only work properly if one row is returned
SELECT @UserId = UserId 
FROM aspnet_Users 
WHERE UserName = @username

Also, if UserId on the aspnet_Users table isn't a UniqueIdentifier you will get the error you describe in your post.

UPDATE

After double checking the OP's code I realized that the error isn't in the variable assignment, it's in the WHERE clause.

The UserName field in the aspnet_Users table is probably VARCHAR and the @username param for the SP is uniqueidentifier.

Try changing @username to varchar and see if the statement works.

Answer 3

Are you sure the aspnet_Users.UserId data type is uniqueidentifier?

Your query can be simplified to:

SELECT * 
  FROM dbo.aspnet_UsersInRoles uir
  JOIN aspnet_Users au ON au.userid = uir.userid
 WHERE au.UserName = @username

There's no need for passing the variable handling.

Answer 4

This is just going off my gut, but is the UserName column really a uniqueidentifier? It seems like that would normally be a varchar, so the problem may be the type of your @username parameter...maybe that should be varchar as well.