Stored Procedure not working

Posted by Shijohnjoseph under Sql Server on 12/17/2011 | Points: 10 | Views : 1121 | Status : [Member] | Replies : 7
please can anyone fix the error
when executing the below sp its working but getting null values on UserCode,AgentsCode,UserName,UserPass which are getting values from other source
please can anyone fix this problem below mu code
USE [ImpressPackageOnline]
GO
/****** Object: StoredProcedure [dbo].[Create_PkgAgents] Script Date: 12/17/2011 11:26:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Create_PkgAgents]

@FName varchar(100),@LName varchar(100),
@CmpyName varchar(100),@Email varchar(100),@Phone varchar(100),
@City varchar(100),@Address varchar(100)

as

BEGIN
DECLARE @AgentCode varchar(100);
DECLARE @UserCode varchar(100);
DECLARE @UserName varchar(100);
DECLARE @UserPass char(5);

set @AgentCode='AGNT_'+CONVERT(varchar(100),((select MAX(User_Agent_Code) from Agents_SignUpInfo)+1));

if(@AgentCode=null)
BEGIN
set @AgentCode='AGNT_1216';
END
set @UserCode='IMPRESS'+CONVERT(varchar(100),((select MAX(User_Code) from Agents_SignUpInfo)+1));

if(@UserCode=null)
BEGIN
set @UserCode='IMPRESS1216';
END
set @UserName= @FName;
BEGIN
EXECUTE password$generate
set @UserPass=(SELECT CONVERT(varchar(100),@UserPass));
END

insert into Agents_SignUpInfo (User_Agent_Code,User_FirstName,User_LastName,User_CompanyName,User_Code,User_Email,User_Phone,User_City,User_Address)
values(@AgentCode,@FName,@LName,@CmpyName,@UserCode,@Email,@Phone,@City,@Address);
insert into Agents_TempUserInfo (UserCode,AgentsCode,UserName,UserPass) values(@UserCode,@AgentCode,@UserName,@UserPass)

END

"Personality has the power to open many doors, but character must keep them open"


Responses

Posted by: Hmanjarawala on: 12/17/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi,

rewrite your procedure as follows:

USE [ImpressPackageOnline]
GO
/****** Object: StoredProcedure [dbo].[Create_PkgAgents] Script Date: 12/17/2011 11:26:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Create_PkgAgents]

@FName varchar(100),@LName varchar(100),
@CmpyName varchar(100),@Email varchar(100),@Phone varchar(100),
@City varchar(100),@Address varchar(100)

as

BEGIN
DECLARE @AgentCode varchar(100);
DECLARE @UserCode varchar(100);
DECLARE @UserName varchar(100);
DECLARE @UserPass char(5);

set @AgentCode='AGNT_'+CONVERT(varchar(100),((select ISNull(MAX(User_Agent_Code),0) from Agents_SignUpInfo)+1));

if(@AgentCode=null)
BEGIN
set @AgentCode='AGNT_1216';
END
set @UserCode='IMPRESS'+CONVERT(varchar(100),((select ISNULL(MAX(User_Code), 0) from Agents_SignUpInfo)+1));

if(@UserCode=null)
BEGIN
set @UserCode='IMPRESS1216';
END
set @UserName= @FName;
BEGIN
EXECUTE password$generate
set @UserPass=(SELECT CONVERT(varchar(100),@UserPass));
END

insert into Agents_SignUpInfo (User_Agent_Code,User_FirstName,User_LastName,User_CompanyName,User_Code,User_Email,User_Phone,User_City,User_Address)
values(@AgentCode,@FName,@LName,@CmpyName,@UserCode,@Email,@Phone,@City,@Address);
insert into Agents_TempUserInfo (UserCode,AgentsCode,UserName,UserPass) values(@UserCode,@AgentCode,@UserName,@UserPass)

END


Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shijohnjoseph on: 12/17/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks Himamshu for your reply.. my half of the problem solved but my password section not working please can u check

"Personality has the power to open many doors, but character must keep them open"

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/17/2011 [Member] Bronze | Points: 25

Up
0
Down
will you tell me from where do you get password?

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shijohnjoseph on: 12/17/2011 [Member] Starter | Points: 25

Up
0
Down
EXECUTE password$generate

set @UserPass=(SELECT CONVERT(varchar(100),@UserPass));


"Personality has the power to open many doors, but character must keep them open"

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/17/2011 [Member] Bronze | Points: 25

Up
0
Down
What is password$generate?

what will it returns and how value falls into @UserPass variable?

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shijohnjoseph on: 12/17/2011 [Member] Starter | Points: 25

Up
0
Down
its an another proc which i had created for generate random passwords
it generate a set of password i need to assign that generated password into @UserPass value but i dont know how can i execute it!.
please help

"Personality has the power to open many doors, but character must keep them open"

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/17/2011 [Member] Bronze | Points: 25

Up
0
Down
will you show me the code of your password generate procedure???

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Shijohnjoseph, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response