Stored Procedure not working

Posted by Shijohnjoseph under Sql Server on 12/17/2011 | Points: 10 | Views : 2514 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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"

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

0	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

0	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

Latest Posts