merging stored procs - 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)

merging stored procs

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-10-18 : 08:51:55

Hi,
At present I have two separate stored procedures sp1 and sp2.
sp1 is something like:

declare @param1 bit = null

select
field1, field2 from table1
where (@param1 is null OR field3 = @param1)

sp2 is something like:

declare param2 varchar(10) = null

if @param2 is null set @param2 = ''

select
field1, field2, field3, ...
from table1...
where
field7 = 'somevalue'
AND (field5 like @param2 + '%')

Now, I am trying to place the two above sps into one stored procedure (sp)...
How can I do this?
Note that each sp i.e. sp1 or sp2 can accept null parameters too.
Any thoughts please?
Thanks

misscrf
Starting Member

10 Posts

Posted - 2010-10-18 : 17:24:39

Wouldnt you just make a new stored proc like this?


Create Procedure dbo.NewProcName (@param1 bit = null, param2 varchar(10) = null )
AS 
BEGIN
if @param2 is null set @param2 = ''
select 
field1, field2 from table1
where (@param1 is null OR field3 = @param1)

select
field1, field2, field3, ...
from table1...
where
field7 = 'somevalue'
AND (field5 like @param2 + '%')

END

Am I missing something?

This is still meant to give you 2 outputs. If you are looking to combine the 2 queries into 1 result set, it may help to better understand the fields being selected and their criteria.

Thanks!

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-10-19 : 04:21:34

Not quite because you are returning two select queries whereas I want to return only one.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 04:30:42

Please be more clear what you want.
In your example there are two queries with diffent count of columns.
Do you want one sp that is, depending on the given parameters, executing only one of that queries?

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