Returning Value from stored procedure

I wrote a Stored Procedure as below:
CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
varchar(7),@.ActPass varchar(45),@.LastUpdate datetime)
AS
UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
@.UserID)
RETURN '2' -- Concurrency Conflict
ELSE
RETURN '1' -- Record has been deleted
END
ELSE
RETURN '0' -- Record has been updated
GO
The content is not important. The issue here is when I execute this Stored
Procedure, I don't get 0, 1 or 2 as a result. It only tells me how many rows
are affected. Why? I thought I should see 0, 1, or 2?
Thank you in advancere-write the Procedure as
CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
varchar(7),@.ActPass varchar(45),@.LastUpdate datetime), @.result int OUTPUT
AS
UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
@.UserID)
SET @.result = 2
ELSE
SET @.result = 1
END
ELSE
SET @.result = 0
RETURN @.result
GO
best Regards,
Chandra
---
"wrytat" wrote:

> I wrote a Stored Procedure as below:
> CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
> varchar(7),@.ActPass varchar(45),@.LastUpdate datetime)
> AS
> UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
> WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT = 0
> BEGIN
> IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
> @.UserID)
> RETURN '2' -- Concurrency Conflict
> ELSE
> RETURN '1' -- Record has been deleted
> END
> ELSE
> RETURN '0' -- Record has been updated
> GO
> The content is not important. The issue here is when I execute this Stored
> Procedure, I don't get 0, 1 or 2 as a result. It only tells me how many ro
ws
> are affected. Why? I thought I should see 0, 1, or 2?
> Thank you in advance|||The return value isnt present to the result, it is a result which can be
get via special properties in the providers, or within a TSQL statement:
DECLARE Returncode int
EXEC @.RC = Someproc
YOu can refer to the Return syntax on BOL.
Sample:
<BOLSample>
CREATE PROCEDURE checkstate @.param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @.param) = 'CA'
RETURN 1
ELSE
RETURN 2
DECLARE @.return_status int
EXEC @.return_status = checkstate '172-32-1176'
SELECT 'Return Status' = @.return_status
GO
</BOLSample>
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
news:466A14D8-2256-47D2-8ECC-C0576672078F@.microsoft.com...
>I wrote a Stored Procedure as below:
> CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
> varchar(7),@.ActPass varchar(45),@.LastUpdate datetime)
> AS
> UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
> WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT = 0
> BEGIN
> IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
> @.UserID)
> RETURN '2' -- Concurrency Conflict
> ELSE
> RETURN '1' -- Record has been deleted
> END
> ELSE
> RETURN '0' -- Record has been updated
> GO
> The content is not important. The issue here is when I execute this Stored
> Procedure, I don't get 0, 1 or 2 as a result. It only tells me how many
> rows
> are affected. Why? I thought I should see 0, 1, or 2?
> Thank you in advance|||I get an error message.
Erro1 170: Line 3: Incorrect syntax near ','.
Must declare the variable '@.result'.
Must declare the variable '@.result'.
Must declare the variable '@.result'.
Must declare the variable '@.result'.
"Chandra" wrote:
> re-write the Procedure as
> CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
> varchar(7),@.ActPass varchar(45),@.LastUpdate datetime), @.result int OUTPUT
> AS
> UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
> WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
> IF @.@.ROWCOUNT = 0
> BEGIN
> IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
> @.UserID)
> SET @.result = 2
> ELSE
> SET @.result = 1
> END
> ELSE
> SET @.result = 0
> RETURN @.result
> GO
>
> --
> best Regards,
> Chandra
> ---
>
> "wrytat" wrote:
>|||Then is it possible to get this returned value in the codings of ASP.nEt
"Jens Sü?meyer" wrote:

> The return value isn′t present to the result, it is a result which can be
> get via special properties in the providers, or within a TSQL statement:
> DECLARE Returncode int
> EXEC @.RC = Someproc
> YOu can refer to the Return syntax on BOL.
> Sample:
> <BOLSample>
> CREATE PROCEDURE checkstate @.param varchar(11)
> AS
> IF (SELECT state FROM authors WHERE au_id = @.param) = 'CA'
> RETURN 1
> ELSE
> RETURN 2
> DECLARE @.return_status int
> EXEC @.return_status = checkstate '172-32-1176'
> SELECT 'Return Status' = @.return_status
> GO
> </BOLSample>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:466A14D8-2256-47D2-8ECC-C0576672078F@.microsoft.com...
>
>|||Just declare it here:
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
DECLARE @.Result INT --<-- HERE
IF @.@.ROWCOUNT = 0
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B7C2766B-C908-4350-ADEC-BBCA688C88B1@.microsoft.com...
>I get an error message.
> Erro1 170: Line 3: Incorrect syntax near ','.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
>
> "Chandra" wrote:
>|||Just not to let you run in further problems, it depends on how you get dour
data:
http://support.microsoft.com/kb/308051/EN-US/
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wrytat" <wrytat@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FDE41EAF-8646-404A-A22E-0602471360FF@.microsoft.com...
> Then is it possible to get this returned value in the codings of ASP.nEt
> "Jens Smeyer" wrote:
>|||sorry! my mistake
CREATE PROCEDURE sp_AssignActPass (@.CustID varchar(4), @.UserID
varchar(7),@.ActPass varchar(45),@.LastUpdate datetime, @.result int OUTPUT)
AS
UPDATE MemberSET ActPass = @.ActPass, LastUpdate = CURRENT_TIMESTAMP
WHERE CustID = @.CustID AND UserID = @.UserID AND LastUpdate = @.LastUpdate
IF @.@.ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT UserID FROM Member WHERE CustID = @.CustID AND UserID =
@.UserID)
SET @.result = 2
ELSE
SET @.result = 1
END
ELSE
SET @.result = 0
RETURN @.result
GO
hope this will work now
best Regards,
Chandra
---
"wrytat" wrote:
> I get an error message.
> Erro1 170: Line 3: Incorrect syntax near ','.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
> Must declare the variable '@.result'.
>
> "Chandra" wrote:
>