Stored Procedure help please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure help please

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-03-03 : 12:26:45

i have created the following stored procedure:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_CheckUser]
	-- Add the parameters for the stored procedure here
	(@UserEmail varchar(30),
	@@Msg  varchar(50)output,
	@@UserID int output)
AS
declare       @UserID int

	select @UserID =ID from tblUser where Email = @UserEmail  -- see if the email exists

    IF @@RowCount = 0       --can't find the user record, return the message
         Begin
          
         select @@Msg='new user registration', @@UserID=0
		end
    ELSE        -- if the email already exists, return the user id and the message
         begin
            select @@Msg='existing',@@userID =@UserID

		End

when i go to execute it, i always get "existing" :(

i don't understand why, but it never returns "new user registration" even i input a brand new email.

please help.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:29:42

can you use if like below and see

IF (select count(*) from tblUser where Email = @UserEmail) = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 12:31:00

try this?


ALTER PROCEDURE [dbo].[sp_CheckUser]
	-- Add the parameters for the stored procedure here
	(@UserEmail varchar(30),
	@@Msg  varchar(50)output,
	@@UserID int output)
AS

	IF NOT EXISTS ( SELECT 1 FROM tblUser WHERE Email = @UserEmail) BEGIN
		select @@Msg='new user registration', @@UserID=0
		end
    ELSE -- if the email already exists, return the user id and the message
        select
			@@Msg='existing'
			, @@userID = ID
		FROM
			tblUser
		WHERE
			Email = @UserEmail
	End

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-03-03 : 12:32:54

now i have this:


ALTER PROCEDURE [dbo].[sp_CheckUser]
	-- Add the parameters for the stored procedure here
	(@UserEmail varchar(30),
	@@Msg  varchar(50)output,
	@@UserID int output)
AS
declare @i int,
        @Y int,
        @UserID int

	-- select @i=count(*) from tblUser where Email = @UserEmail  -- see if the email exists

    IF (select count(*) from tblUser where Email = @UserEmail) = 0       --can't find the user record, return the message
         Begin
          
         select @@Msg='new user registration', @@UserID=0
    End

    IF @i>0         -- if the email already exists, return the user id and the message
        select @Userid=max(ID) from tblUser where Email = @UserEmail and is_active ='Y'
         begin
            select @@Msg='existing',@@userID =@UserID
     End

but am still getting "existing" if i try to run it :(

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-03-03 : 12:34:35

Charlie, looks like it worked :)

any idea why my other method wasn't working? :(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 02:19:50

quote:
Originally posted by xrum

Charlie, looks like it worked :)

any idea why my other method wasn't working? :(

Becuase you commented the line that assinged value to @i

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources