Doubt in stored procedure - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Doubt in stored procedure

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-07 : 03:42:02

Hi team, i wrote one stored procedure and its executing fine but the thing is it unable to retrive the result to asp.net code in one codniton.(.i.e If(FNNeutral == 'N')). In other condition its working fine.. I think i didn't wrote in correct way. Please look in to this.


ALTER Procedure Tbl_MyReligion
(
 @FNNeutral varchar(10),  
 @DetailedAncestry varchar(50),
 @FName varchar(50),  
 @LName varchar(50),  
 @Ancestry varchar(50)  
)
As
BEGIN
 DECLARE 
 @cmd AS NVARCHAR(max),
 @Reg varchar(1000)

 If(@FNNeutral = 'Y')
	Begin
 	   SET @cmd = 'SELECT Religion FROM TBL_Religion Where FNNeutral = ''' + @FNNeutral + '';
	    EXEC sp_executesql @cmd 
	End
 else If(@FNNeutral = 'N')
	Begin
      SELECT @Reg = Religion FROM Tbl_FirstName Where Name =  @FName  and Ancestry = @Ancestry;   
	  If(@Reg = '')
		Begin
			SELECT @Reg = Religion FROM Tbl_LastName Where Name =  @LName  and Ancestry = @Ancestry;
			If(@Reg = '')
				Begin
					 SET @cmd = 'SELECT Religion FROM TBL_Religion Where FNNeutral = ''' + @FNNeutral + '';
					 EXEC sp_executesql @cmd 
				End
		End
	End

END

In the above stored procedure..in else condition, i need to get religion as output. But at the same time i need to write 2 conditions there. if religion is empty with first query..it has to check with second query. if not then with third query.. I am getting result in @Reg variable. But unable to get in .net code. i.e when i run this, i need to get result(means display result) instead of showing command runs succesfully.
Exec Tbl_MyReligion 'N','PAKI','roushanara','begum','MUSL'

developer :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 03:59:18

If @Reg <> '' then your sp does nothing!

No, you're never too old to Yak'n'Roll if you're too young to die.

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-07 : 04:09:16

I want you to correct my stored procedure to return a value(religion).

I am seding parameter @FNNeutral as values 'Y' or 'N'.
if @FNNeutral is 'Y', then it has to excute the below query and return the religion value.

'SELECT Religion FROM TBL_Religion Where FNNeutral = ''' + @FNNeutral + ''

if @FNNeutral is 'N' then it has to retrive religion data from Tbl_Firstname using this query.


SELECT Religion FROM Tbl_FirstName Where Name =  @FName  and Ancestry = @Ancestry

If the religion value is empty then it checks in Tbl_LastName table with below query.


SELECT Religion FROM Tbl_LastName Where Name =  @LName  and Ancestry = @Ancestry

still if religion is empty it has get the religion from below query.

'SELECT Religion FROM TBL_Religion Where FNNeutral = ''' + @FNNeutral + ''

I hope my requirement is clear for you. Please give stored procedure for this.

quote:
Originally posted by webfred

If @Reg <> '' then your sp does nothing!

No, you're never too old to Yak'n'Roll if you're too young to die.

developer :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 04:27:30

Try this:


declare @Reg int
set @Reg=null

if(@FNNeutral = 'Y')
Begin
  select @Reg=Religion FROM TBL_Religion Where FNNeutral = 'Y'
End
Else
Begin
  select @Reg=Religion FROM Tbl_FirstName Where Name =  @FName  and Ancestry = @Ancestry
End

If(@Reg is null)
Begin
  select @Reg=Religion FROM Tbl_LastName Where Name =  @LName  and Ancestry = @Ancestry
End

If(@Reg is null)
Begin
  select @Reg=Religion FROM TBL_Religion Where FNNeutral = 'N'
End

select @Reg

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 04:27:42

Why are you using Dynamic sql?

See this helps


ALTER Procedure Tbl_MyReligion
(
 @FNNeutral varchar(10),  
 @DetailedAncestry varchar(50),
 @FName varchar(50),  
 @LName varchar(50),  
 @Ancestry varchar(50)  
)
As

SELECT Religion FROM TBL_Religion 
Where 
(FNNeutral = @FNNeutral and @FNNeutral = 'Y')
or
(Name =  @FName  and Ancestry = @Ancestry  and @FNNeutral = 'N')
or
(Name =  @LName  and Ancestry = @Ancestry  and @FNNeutral = 'N')
or
(FNNeutral = @FNNeutral and @FNNeutral = 'N')

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 04:29:50

quote:

Originally posted by madhivanan

Why are you using Dynamic sql?

See this helps


ALTER Procedure Tbl_MyReligion
(
 @FNNeutral varchar(10),  
 @DetailedAncestry varchar(50),
 @FName varchar(50),  
 @LName varchar(50),  
 @Ancestry varchar(50)  
)
As

SELECT Religion FROM TBL_Religion 
Where 
(FNNeutral = @FNNeutral and @FNNeutral = 'Y')
or
(Name =  @FName  and Ancestry = @Ancestry  and @FNNeutral = 'N')
or
(Name =  @LName  and Ancestry = @Ancestry  and @FNNeutral = 'N')
or
(FNNeutral = @FNNeutral and @FNNeutral = 'N')

Madhivanan

Failing to plan is Planning to fail

This wouldn't work because the source isn't only TBL_Religion.

No, you're never too old to Yak'n'Roll if you're too young to die.

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-07 : 04:39:49

Hi Webfred,

It works perfect. I am looking for the same. Thank you.

quote:

Originally posted by webfred

Try this:


declare @Reg int
set @Reg=null

if(@FNNeutral = 'Y')
Begin
  select @Reg=Religion FROM TBL_Religion Where FNNeutral = 'Y'
End
Else
Begin
  select @Reg=Religion FROM Tbl_FirstName Where Name =  @FName  and Ancestry = @Ancestry
End

If(@Reg is null)
Begin
  select @Reg=Religion FROM Tbl_LastName Where Name =  @LName  and Ancestry = @Ancestry
End

If(@Reg is null)
Begin
  select @Reg=Religion FROM TBL_Religion Where FNNeutral = 'N'
End

select @Reg

No, you're never too old to Yak'n'Roll if you're too young to die.

developer :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 04:40:22

Then try this


ALTER Procedure Tbl_MyReligion
(
 @FNNeutral varchar(10),  
 @DetailedAncestry varchar(50),
 @FName varchar(50),  
 @LName varchar(50),  
 @Ancestry varchar(50)  
)
As

SELECT Religion FROM 
(
select Religion from TBL_Religion  Where (FNNeutral = @FNNeutral)
union all
select Religion from TBL_FirstName  where (Name =  @FName and FNNeutral =  @FNNeutral and @FNNeutral = 'N')
union all
select Religion from TBL_LastName  where (Name =  @LName and FNNeutral =  @FNNeutral and @FNNeutral = 'N')
)
 as t

Madhivanan

Failing to plan is Planning to fail

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-07 : 04:40:40

Yes. it wont work bcz of changing the table names in each query.

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

Why are you using Dynamic sql?

See this helps

ALTER Procedure Tbl_MyReligion
(
@FNNeutral varchar(10),
@DetailedAncestry varchar(50),
@FName varchar(50),
@LName varchar(50),
@Ancestry varchar(50)
)
As

SELECT Religion FROM TBL_Religion
Where
(FNNeutral = @FNNeutral and @FNNeutral = 'Y')
or
(Name = @FName and Ancestry = @Ancestry and @FNNeutral = 'N')
or
(Name = @LName and Ancestry = @Ancestry and @FNNeutral = 'N')
or
(FNNeutral = @FNNeutral and @FNNeutral = 'N')

Madhivanan

Failing to plan is Planning to fail

This wouldn't work because the source isn't only TBL_Religion.

No, you're never too old to Yak'n'Roll if you're too young to die.

developer :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 04:42:20

quote:
Originally posted by satish.gorijala

Yes. it wont work bcz of changing the table names in each query.
quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

Why are you using Dynamic sql?

See this helps

ALTER Procedure Tbl_MyReligion
(
@FNNeutral varchar(10),
@DetailedAncestry varchar(50),
@FName varchar(50),
@LName varchar(50),
@Ancestry varchar(50)
)
As

SELECT Religion FROM TBL_Religion
Where
(FNNeutral = @FNNeutral and @FNNeutral = 'Y')
or
(Name = @FName and Ancestry = @Ancestry and @FNNeutral = 'N')
or
(Name = @LName and Ancestry = @Ancestry and @FNNeutral = 'N')
or
(FNNeutral = @FNNeutral and @FNNeutral = 'N')

Madhivanan

Failing to plan is Planning to fail

This wouldn't work because the source isn't only TBL_Religion.

No, you're never too old to Yak'n'Roll if you're too young to die.

developer :)

See my previous reply
I posted a new solution

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 04:46:00

quote:

Originally posted by madhivanan

Then try this


ALTER Procedure Tbl_MyReligion
(
 @FNNeutral varchar(10),  
 @DetailedAncestry varchar(50),
 @FName varchar(50),  
 @LName varchar(50),  
 @Ancestry varchar(50)  
)
As

SELECT Religion FROM 
(
select Religion from TBL_Religion  Where (FNNeutral = @FNNeutral)
union all
select Religion from TBL_FirstName  where (Name =  @FName and FNNeutral =  @FNNeutral and @FNNeutral = 'N')
union all
select Religion from TBL_LastName  where (Name =  @LName and FNNeutral =  @FNNeutral and @FNNeutral = 'N')
)
 as t

Madhivanan

Failing to plan is Planning to fail

This would return more than OP expected.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 04:47:09

quote:
Originally posted by satish.gorijala

Hi Webfred,

It works perfect. I am looking for the same. Thank you.
quote:
Originally posted by webfred

Try this:

declare @Reg int
set @Reg=null

if(@FNNeutral = 'Y')
Begin
select @Reg=Religion FROM TBL_Religion Where FNNeutral = 'Y'
End
Else
Begin
select @Reg=Religion FROM Tbl_FirstName Where Name = @FName and Ancestry = @Ancestry
End

If(@Reg is null)
Begin
select @Reg=Religion FROM Tbl_LastName Where Name = @LName and Ancestry = @Ancestry
End

If(@Reg is null)
Begin
select @Reg=Religion FROM TBL_Religion Where FNNeutral = 'N'
End

select @Reg

No, you're never too old to Yak'n'Roll if you're too young to die.

developer :)

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 04:50:08

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

Then try this

ALTER Procedure Tbl_MyReligion
(
@FNNeutral varchar(10),
@DetailedAncestry varchar(50),
@FName varchar(50),
@LName varchar(50),
@Ancestry varchar(50)
)
As

SELECT Religion FROM
(
select Religion from TBL_Religion Where (FNNeutral = @FNNeutral)
union all
select Religion from TBL_FirstName where (Name = @FName and FNNeutral = @FNNeutral and @FNNeutral = 'N')
union all
select Religion from TBL_LastName where (Name = @LName and FNNeutral = @FNNeutral and @FNNeutral = 'N')
)
as t

Madhivanan

Failing to plan is Planning to fail

This would return more than OP expected.

No, you're never too old to Yak'n'Roll if you're too young to die.

Becuase I missed a condition?


ALTER Procedure Tbl_MyReligion
(
 @FNNeutral varchar(10),  
 @DetailedAncestry varchar(50),
 @FName varchar(50),  
 @LName varchar(50),  
 @Ancestry varchar(50)  
)
As

SELECT Religion FROM 
(
select Religion from TBL_Religion  Where (FNNeutral = @FNNeutral)
union all
select Religion from TBL_FirstName  where (Name =  @FName and Ancestry = @Ancestry and FNNeutral =  'N')
union all
select Religion from TBL_LastName  where (Name =  @LName and Ancestry = @Ancestry and FNNeutral = 'N')
)
 as t

Madhivanan

Failing to plan is Planning to fail

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-12-07 : 04:51:16

Webfred, Which one is the best when we consider performance. Either the union all sp as below one or the previous "If/Else" one?

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

Then try this

ALTER Procedure Tbl_MyReligion
(
@FNNeutral varchar(10),
@DetailedAncestry varchar(50),
@FName varchar(50),
@LName varchar(50),
@Ancestry varchar(50)
)
As

SELECT Religion FROM
(
select Religion from TBL_Religion Where (FNNeutral = @FNNeutral)
union all
select Religion from TBL_FirstName where (Name = @FName and FNNeutral = @FNNeutral and @FNNeutral = 'N')
union all
select Religion from TBL_LastName where (Name = @LName and FNNeutral = @FNNeutral and @FNNeutral = 'N')
)
as t

Madhivanan

Failing to plan is Planning to fail

This would return more than OP expected.

No, you're never too old to Yak'n'Roll if you're too young to die.

developer :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 05:03:25

Hi satish.gorijala

our solution is a bit doubtful because I am not sure if for example this
select @Reg=Religion FROM Tbl_FirstName Where Name = @FName and Ancestry = @Ancestry
could fail because @Reg can take only ONE value returned by the query and I don't know if it is possible that this query returns more than one value.
In case of performance I don't know. You should have a test and a look at execution plan...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources