Stored procedure

[lttleastig]

He guys when i execute this stored procedure by right clicking on it and press execute then entering a value for PW i get a NULL value returned any idea why?

USE [DR2_Member]
GO
/****** Object:  StoredProcedure [dbo].[up_php]    Script Date: 02/26/2011 18:26:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[up_php]
/*
=============================================
Last Update by :	Eric Kim
Update date: 2008-06-27
Description:	
PARAMETER :
RETURN : change password in text format
// IAH 연동 함수
//		2009-06-29 : Eric Kim, PW 검사를 게임서버에서 하도록 변경
=============================================
*/
(
	@PW	nvarchar(50)
)
AS
SET NOCOUNT ON

BEGIN

DECLARE @Err int
SET @Err = 0

-- *** Added by IAHGames ***
-- To convert nvarchar to binary
-- ***
DECLARE @binarypassword varbinary(max);
DECLARE @hexstring nvarchar(max);
SET @hexstring = @PW;
SELECT @binarypassword = Cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
FROM (SELECT CASE SubString(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)
-- ***


update dbo.Member set PW = @binarypassword where ID = 'philip'


end

any replies would be appreciated thanks :D

[lttleastig]

using SQL server 2008 enterprise R2 btw

[BitBlt]

You are not setting a return value.

<snip>
UPDATE dbo.Member SET PW = @binarypassword WHERE ID = 'philip'
if @@error = 0  --check to see if the update worked
   begin
      return(0)   
   else
      return(1) -- or some other number that's meaningful to you
   end
end

What are you expecting to get back? If you want a result set of some sort, you should be issuing a select statement somewhere, preferably somewhere after the heavy work is done. If you want to receive an output parameter, you have to construct it that way (easy to do...it's in the manual).

You can also use "select @@rowcount" for the number of rows updated.