Trying to initialize 2 values in 2 variables using 1 query, is it possible to do this. ?

Question

SET @Password = (
    SELECT UserPassword,IsLocked 
    FROM [Authentication].[tblLogin] 
    WHERE UserName=@UserName)

i m trying to get both values userpassword and islocked in two variables to be used in same SP in next query. Is it possible or do i have to write two queries for this. Is it there a concept of arrays in sql server

Answer 1

Use select instead of set:

select 
    @Password = Password
,   @IsLocked = IsLocked
,   @UserLongName = UserLongName
,   @TopSecretPin = 1234
,   @PrefersLinux = case when LastName = 'Torvalds' then 1 else 0 end
FROM [Authentication].[tblLogin] 
WHERE UserName = @UserName