开发者

Storing variables from queries in stored procedures

开发者 https://www.devze.com 2023-01-05 11:36 出处:网络
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.

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消