[Solved] 4 Select in 1 StoreProcedure

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)

[Solved] 4 Select in 1 StoreProcedure

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-10-08 : 10:07:16

Hey there, i dont waht to have 4 different store procedure to do a select, so i do the 4 select depending of what to search:


create procedure [dbo].[sp_sel_MstPlaceOfServiceByIns]

@What as varchar(15),
@DeptID as int

as
begin
	if @What = 'servicearea'
		select ServiceArea, Description from MstServiceArea with(nolock) order by ServiceArea

	if @What = 'insid'
		select InsId, InsName from MstInsurance with(nolock) order by InsId

	if @What = 'deptid'
		select  DeptID, Description from MstDepartment with(nolock) order by DeptID

	if @What = 'subdeptid'
		select  DeptID, SubDeptID, Description from MstSubDept with(nolock) where DeptID = @DeptID order by  SubDeptID 
	
end

Im wondering if this is ok or there is a proper way to do this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:13:19

no problem in doing this unless you're using this procedure as source for application like reporting services. It will not work correctly if the number of columns returned by stored procedure varies for various values of parameter.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-08 : 10:14:12

You can build sql string dynamically depending on your WHERE statements:
create procedure [dbo].[sp_sel_MstPlaceOfServiceByIns] ( @What as varchar(15), @DeptID as int)

as
declare @strSQL varchar (1000)
if @What = 'servicearea'
set strSQL ='select ServiceArea, Description from MstServiceArea with(nolock) order by ServiceArea'

if @What = 'insid'
set strSQL ='select InsId, InsName from MstInsurance with(nolock) order by InsId'

if @What = 'deptid'
set strSQL ='select DeptID, Description from MstDepartment with(nolock) order by DeptID'

if @What = 'subdeptid'
set strSQL ='select DeptID, SubDeptID, Description from MstSubDept with(nolock) where DeptID =' + @DeptID ' order by SubDeptID'

exec (@strSQL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:20:54

quote:
Originally posted by darkdusky

You can build sql string dynamically depending on your WHERE statements:
create procedure [dbo].[sp_sel_MstPlaceOfServiceByIns] ( @What as varchar(15), @DeptID as int)

as
declare @strSQL varchar (1000)
if @What = 'servicearea'
set strSQL ='select ServiceArea, Description from MstServiceArea with(nolock) order by ServiceArea'

if @What = 'insid'
set strSQL ='select InsId, InsName from MstInsurance with(nolock) order by InsId'

if @What = 'deptid'
set strSQL ='select DeptID, Description from MstDepartment with(nolock) order by DeptID'

if @What = 'subdeptid'
set strSQL ='select DeptID, SubDeptID, Description from MstSubDept with(nolock) where DeptID =' + @DeptID ' order by SubDeptID'

exec (@strSQL)

why should you use dynamic sql here. the posted code by OP is much better approach.

CVDpr
Starting Member

41 Posts

Posted - 2008-10-08 : 10:27:29

Any know why when i use the first @What 'servicearea' i doesnot work? , but the rest of the @what works.


create procedure [dbo].[sp_sel_MstPlaceOfServiceByIns]

@What as varchar(15),
@DeptID as int

as
begin
	if @What = 'servicearea'
		select ServiceArea, Description from MstServiceArea with(nolock) order by ServiceArea

	if @What = 'insid'
		select InsId, InsName from MstInsurance with(nolock) order by InsId

	if @What = 'deptid'
		select  DeptID, Description from MstDepartment with(nolock) order by DeptID

	if @What = 'subdeptid'
		select  DeptID, SubDeptID, Description from MstSubDept with(nolock) where DeptID = @DeptID order by  SubDeptID 
	
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:30:49

what do you mean it does not work? are you getting any error? or no results returned?

CVDpr
Starting Member

41 Posts

Posted - 2008-10-08 : 10:40:09

quote:
Originally posted by visakh16

what do you mean it does not work? are you getting any error? or no results returned?

Sorry, yes no results returned, is like the procedure is ignoring the @What 'servicearea'. this is what happend when i run the procedure:

sp_sel_MstPlaceOfServiceByIns 'insu',''
it return the results...

sp_sel_MstPlaceOfServiceByIns 'deptid',''
it return the results...

sp_sel_MstPlaceOfServiceByIns 'subdeptid',1
it return the results...

but whe i run this:

sp_sel_MstPlaceOfServiceByIns 'servicearea',''
no return the results , just say 'Command(s) completed successfully.'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:45:16

make sure you have not put any space or any other characters by mistake in your if condition in procedure

if @What = 'servicearea'...

also check if you've defined @What to have enough length to hold full value passed (your earlier posted cod contains 15 which is ok but i'm not sure you've changed it since then)

CVDpr
Starting Member

41 Posts

Posted - 2008-10-08 : 10:45:47

haha, i cant believe this stupid error:

i declare the @what with varchar(10), but 'servicearea' have 11 character, that was the problem!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:47:36

quote:
Originally posted by CVDpr

haha, i cant believe this stupid error:

i declare the @what with varchar(10), but 'servicearea' have 11 character, that was the problem!

i guessed that right then