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
-- 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.
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.
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;
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.
精彩评论