SQL query returns null instead of int

hi all,

i am having a problem with the following stored procedure.

when i debug it i get the desired result.

when i call it from XSD i get null value.

moreover, when i add the stored procedure to the dataset (xsd) it does not show that the query returns an integer.

how can i correct this?

ALTER PROCEDURE dbo.SignupDetailsAvailable
 (
 @username nvarchar(50),
 @email nvarchar(100)
 )


 

AS
 declare @amount int


  SET NOCOUNT ON
   select @amount = count(*) from Users where Username=@username
  if @amount = 0
  BEGIN
    select @amount = count(*) from Users where Email=@email
   if @amount = 0
    return 0 /* all  is ok */
   ELSE
    return 2 /* Email is taken */
   END
  return 1 /* Username is taken */

Re: SQL query returns null instead of int

Hi i have modified your sp

it will return value...plz check it

ALTER PROCEDURE dbo.SignupDetailsAvailable
     (
     @username nvarchar(50),
     @email nvarchar(100)
     )
      

       

    AS
    BEGIN
		 declare @amount int,@retVal int
	     

		 SET NOCOUNT ON
		 select @amount = count(*) from Users where Username=@username
		 if @amount = 0
		 BEGIN
			   select @amount = count(*) from Users where Email=@email
			  if @amount = 0
			   SET @retVal=0 /* all  is ok */
			  ELSE
			   SET @retVal=2 /* Email is taken */
		 END
		 ELSE
		 Begin
			set @retVal = 1 /* Username is taken */
		 End
		 Select @retVal
    END

Re: SQL query returns null instead of int

hi sandeep ,

thanks a lot!