All I want to do is feed a varchar into a stored procedure and get an id back... what am I

So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it. Here is the code:

#############################################

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR

AS
BEGIN
DECLARE @.postedbyid UNIQUEIDENTIFIER

SET @.postedbyid = (SELECT UserId
FROM aspnet_Users
WHERE (UserName = @.username))
END

###Which returns this###

Running [dbo].[GetUserIdForUser] ( @.username = jason ).

No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

#############################################

This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build. If anyone can tell me what I am doing wrong I would appreciate it. I have tried a few things that have resulted in different failures. If I remove any references to variables (delete SET @.postedbyid = and replace @.username with 'jason') I can get it to return a result. If I put @.username in though it doesn't work. Here are the examples of those:

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR

AS
BEGIN

SELECT UserId
FROM aspnet_Users
WHERE (UserName = @.username)
END

###Which returns this###

Running [dbo].[GetUserIdForUser] ( @.username = jason ).

UserId
------------
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

Here is a sanity check to show that the data is in fact in the database:

ALTER PROCEDURE dbo.GetUserIdForUser
AS
BEGIN
SELECT UserId
FROM aspnet_Users
WHERE (UserName = 'jason')
END

Running [dbo].[GetUserIdForUser].

UserId
------------
No rows affected.
(1 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

Anyone have any ideas on what I am doing wrong?

Try setting the size for the parameter in your proc.|||

Have you tried an output parameter?

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR,
@.postedbyid uniqueidentifier OUTPUT

AS
BEGIN

SELECT @.postedbyid = UserId
FROM aspnet_Users
WHERE (UserName = @.username)
END

|||

Yea, I thought about that but when I do that I get this:

Running [dbo].[GetUserIdForUser] ( @.username = jason, @.postedbyid = <DEFAULT> ).

Procedure or Function 'GetUserIdForUser' expects parameter '@.postedbyid', which was not supplied.
No rows affected.
(0 row(s) returned)
@.postedbyid =
@.RETURN_VALUE =
Finished running [dbo].[GetUserIdForUser].

For some reason OUTPUT is both and in and an output and it wants an initial variable. Regardless of what I assign it or even if I leave it null it results in the same issue.

|||

I did, same result:

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR(255)

AS
BEGIN
DECLARE @.postedbyid UNIQUEIDENTIFIER

SELECT @.postedbyid = UserId
FROM aspnet_Users
WHERE (UserName = @.username)
END

### RESULT ###

Running [dbo].[GetUserIdForUser] ( @.username = jason ).

No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

Any other ideas?

|||

This one works:

ALTER

PROCEDURE [dbo].GetUserIdForUser]

@.UserName

NVARCHAR(255)

AS

BEGIN

SELECT UserIdFROM dbo.aspnet_Users-- WHERE LoweredUserName = LOWER(@.UserName)WHERE UserName= @.UserName

RETURN 0

END

|||What are you expecting back from the stored proc? You would either need to use a SELECT statement or OUTPUT parameters to return values. Currently there's none.|||

This one works:

ALTER

PROCEDURE [dbo].GetUserIdForUser]

@.UserName

NVARCHAR(255)

AS

BEGIN

SELECT UserIdFROM dbo.aspnet_Users-- WHERE LoweredUserName = LOWER(@.UserName)WHERE UserName= @.UserName

RETURN 0

END

Right, but using that, how do I pass that UserId to something else?

|||What is the datatype of userid in your aspnet_users table?|||

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR(255),
@.postedbyid INT OUTPUT
AS
BEGIN

SELECT @.postedbyid = UserId
FROM aspnet_Users
WHERE (UserName = @.username)

END

When you need tro execute it:

Declare @.Pid int
EXEC dbo.GetUserIdForUser 'testusername', @.pid OUTPUt
SELECT @.pid as PostedByID

|||

You can use the userid in your sp like:

ALTER

PROCEDURE [dbo].[foo1]

@.username

NVARCHAR(255),

@.CurrentTimeUtc

datetime=NULL

AS

BEGIN

DECLARE

@.UserIduniqueidentifier

SET

@.CurrentTimeUtc=getdate()

BEGINSELECT @.UserId=UserIdFROM aspnet_UsersWHERE @.UserName= UserName

UPDATE dbo.aspnet_Users

SET LastActivityDate= @.CurrentTimeUtc

WHEREUserid=@.UserId

END

|||

What is the datatype of userid in your aspnet_users table?

---

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************

The datatype is UNIQUEIDENTIFIER. I am using the asp.net user authentication and that is the format it is in. I could probably just add another column called id as an INT, but at this point I want to know why it isn't working. It is acting like it needs to be converted but even when I try that it doesn't work. There is something strange about uniqueidentifiers but it doesn't seem to be documented anywhere.

|||

limno:

You can use the userid in your sp like:

ALTERPROCEDURE [dbo].[foo1]

@.usernameNVARCHAR(255),

@.CurrentTimeUtcdatetime=NULL

AS

BEGIN

DECLARE @.UserIduniqueidentifier

SET @.CurrentTimeUtc=getdate()

BEGIN

SELECT @.UserId=UserIdFROM aspnet_UsersWHERE @.UserName= UserName

UPDATE dbo.aspnet_Users

SET LastActivityDate= @.CurrentTimeUtc

WHEREUserid=@.UserId

END

I did this and it updated the date; however, it did not select the userid.

|||

EUREKEA!! I got it. So the problem is that UNIQUEIDENTIFIER has to be converted. Once I started searching for that I found all kinds of instances of people running into it. I just don't have the experience doing this yet to be any good at troubleshooting it. Thanks for all your help everyone and thanks for putting up with my naivete. Here is how I got it to work:

ALTER PROCEDURE dbo.GetUserIdForUser @.usernameNVARCHAR(255), @.useridVARCHAR(36) =''OUTPUTASBEGINSELECT @.UserId =CONVERT(VARCHAR(36), UserId)FROM aspnet_UsersWHERE @.UserName = UserNameEND

Here is the result:

Running [dbo].[GetUserIdForUser] ( @.username = jason, @.userid = <DEFAULT> ).

No rows affected.
(0 row(s) returned)
@.userid = FBF8AF84-BE4A-4BA4-A393-5519C9C00932
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].