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 */
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
tomerz
Member
61 Points
71 Posts
SQL query returns null instead of int
Jul 14, 2010 07:35 AM|LINK
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 */sandeep tada
Member
208 Points
50 Posts
Re: SQL query returns null instead of int
Jul 14, 2010 11:42 AM|LINK
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 ENDSandeep
Please mark as answer if my post helps you....
Follow me on Blog for tech help
tomerz
Member
61 Points
71 Posts
Re: SQL query returns null instead of int
Jul 15, 2010 04:51 PM|LINK
hi sandeep ,
thanks a lot!