asked on

stored procedure not returning uniqueidentifier

hi there,

id like the stored procedure bellow to return a uniqueidentifier. however, its returning an integer and i get the following exception

"Msg 206, Level 16, State 2, Procedure GetUserIdByUserName, Line 18
Operand type clash: uniqueidentifier is incompatible with int"

ALTER PROCEDURE [dbo].[GetUserIdByUserName]

(
	@UserName nvarchar(256)
)
AS
	SET NOCOUNT ON;

DECLARE	@return_value uniqueidentifier 

set 

@return_value = (SELECT UserId as userID
				FROM aspnet_Users
				where UserName = @UserName)
				
				
return @return_value;

Open in new window

gplana

dsee what datatype is userid field. You have a datatype conversion error: I think field is defined as int, but your variable is defined as uniqueidentifier. Change it to int.

Hope it helps.

RachelDosSantos

ASKER

userid field is uniqueidentifier and that is the type i need this procedure to return

RachelDosSantos

ASKER

CREATE TABLE [dbo].[aspnet_Users](
      [ApplicationId] [uniqueidentifier] NOT NULL,
      [UserId] [uniqueidentifier] NOT NULL,
      [UserName] [nvarchar](256) NOT NULL,
....

gplana

does this query
SELECT UserId as userID
FROM aspnet_Users

works on your system ? If not, try this one
SELECT [UserID]
FROM aspnet_Users

if this works,change the query inside your procedure and it should work

Raja Jegan R

This would suffice:

or a simple SELECT statement would do instead of a procedure:
SELECT UserId as userID
FROM aspnet_Users
where UserName = ?

ALTER PROCEDURE [dbo].[GetUserIdByUserName]

(
	@UserName nvarchar(256)
)
AS

SELECT UserId as userID
FROM aspnet_Users
where UserName = @UserName

GO

Open in new window

ASKER CERTIFIED SOLUTION

lundnak

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Inteqam

ALTER PROCEDURE [dbo].[GetUserIdByUserName]

(
      @UserName nvarchar(256)
)
AS
      SET NOCOUNT ON;

DECLARE      @return_value uniqueidentifier

set

SELECT @return_value= UserId
                        FROM aspnet_Users
                        where UserName = @UserName


return @return_value;

Anthony Perkins

Inteqam,

You may want to double check that.

Inteqam

yeah, my mistake.

lundnak is right, see http://msdn.microsoft.com/en-us/library/ms174998.aspx

Inteqam

see this too
http://stackoverflow.com/questions/2041013/sql-server-return-uniqueidentifier-from-stored-procedure