Solved

stored procedure not returning uniqueidentifier

Posted on 2010-09-06
10
968 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:RachelDosSantos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 33612229
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.
0
 

Author Comment

by:RachelDosSantos
ID: 33612238
userid field is uniqueidentifier and that is the type i need this procedure to return
0
 

Author Comment

by:RachelDosSantos
ID: 33612250
CREATE TABLE [dbo].[aspnet_Users](
      [ApplicationId] [uniqueidentifier] NOT NULL,
      [UserId] [uniqueidentifier] NOT NULL,
      [UserName] [nvarchar](256) NOT NULL,
....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:gplana
ID: 33612306
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33612445
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

0
 
LVL 7

Accepted Solution

by:
lundnak earned 500 total points
ID: 33614377
You can only return a datatype of INT from a stored procedure.  You either need to create an output parameter or change the SPROC to a Scalar-based User Defined Function.

This is the example of an output parameter.

You call the sproc this way:

Declare @UserID uniqueidentifier
Exec GetUserIDByUserName @UserName = 'Joe', @Return_Value=@UserID output
ALTER PROCEDURE [dbo].[GetUserIdByUserName]

(
	@UserName nvarchar(256),
        @Return_Value uniqueidentifier output
)
AS
	SET NOCOUNT ON;

DECLARE	@return_value uniqueidentifier 

set 

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

Open in new window

0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33614834
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;
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33623986
Inteqam,

You may want to double check that.
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33624236
yeah, my mistake.

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

Expert Comment

by:Inteqam
ID: 33624242
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question