Nested stored procedures - 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 2000 Forums

SQL Server Development (2000)

Nested stored procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-20 : 07:33:06

Janet writes "I had a stored procedure that received variables and then concatenated and executed the sql statement. I've detailed it as sp1 below. But, I read that temp tables, views and concatenanted, executed sql statements are bad, bad, bad. So, I changed the select statements to eliminated the temp tables and views and I'm trying to find info on changing sp2 below into nested stored procedures, but can't seem to find some basic stuff on nested stored procedures based on the variable value. Can you direct me somewhere (assuming my assumption above is correct)??? One thing to keep in mind - there's always an @cat, but I only need to include the other variables if they are NOT 0. (Probably obvious to you, but just wanted to be clear.) I've tried searching the forums, but can't seem to find articles except the ones that tell you to do it.

If you can redirect me to an existing example(s), please do so, or
Many many thanks if you can help...

SP1
IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'u' AND name = '#tempStats' )
DROP TABLE #tempStats
SELECT tblStats.SID AS SID, [year], titleID, raceID, hispID, sexID, ISNULL(tblStats.keywords, '') AS keywords
INTO #tempStats FROM chad.dbo.tblCategories with (INDEX(IX_tblCategories_catID)) LEFT OUTER JOIN chad.dbo.tblStats ON tblCategories.SID = tblStats.SID
WHERE tblCategories.catID=@cat
set @sql= 'SELECT distinct xtblTitles.tblID, xtblTitles.tblTitle, ss, sourceShort FROM chad.dbo.#tempStats LEFT OUTER JOIN chad.dbo.xtblSource RIGHT OUTER JOIN '
set @sql=@sql + 'chad.dbo.xtblSS ON xtblSource.sourceID = xtblSS.sourceID RIGHT OUTER JOIN chad.dbo.xtblTitles ON xtblSS.ssID = xtblTitles.ssID LEFT OUTER JOIN '
set @sql=@sql + 'chad.dbo.tblStatTitles ON xtblTitles.tblID = tblStatTitles.tblID ON #tempStats.titleID = tblStatTitles.titleID '
if @src<>0
set @sql=@sql + 'WHERE (xtblSS.sourceID=' + CONVERT(varChar(2),@src) + ') '
if @sex<>0 AND @src<>0
set @sql=@sql + 'AND (#tempStats.sexID=' + CONVERT(varChar(2),@sex) + ') '
if @sex<>0 AND @src=0
set @sql=@sql + 'WHERE (#tempStats.sexID=' + CONVERT(varChar(2),@sex) + ') '
if @race<>0 AND (@src<>0 OR @sex<>0)
set @sql=@sql + 'AND (#tempStats.raceID=' + CONVERT(varChar(2),@race) + ') '
if @race<>0 AND @src=0 AND @sex=0
set @sql=@sql + 'WHERE (#tempStats.raceID=' + CONVERT(varChar(2),@race) + ') '
if @hisp<>0 AND (@src<>0 OR @sex<>0 OR @race<>0)
set @sql=@sql + 'AND (#tempStats.hispID=' + CONVERT(varChar(2),@hisp) + ') '
if @hisp<>0 AND @src=0 AND @sex=0 AND @race=0
set @sql=@sql + 'WHERE (#tempStats.hispID=' + CONVERT(varChar(2),@hisp) + ') '
if @year<>0 AND (@src<>0 OR @sex<>0 OR @race<>0 OR @hisp<>0)
set @sql=@sql + 'AND (#tempStats.[year]=' + CONVERT(varChar(4),@year) + ') '
if @year<>0 AND @src=0 AND @sex=0 AND @race=0 AND @hisp=0
set @sql=@sql + 'WHERE (#tempStats.[year]=' + CONVERT(varChar(4),@year) + ') '
set @sql=@sql + ' ORDER BY xtblTitles.tblTitle'
exec(@sql)

SP2
CREATE PROCEDURE sp_getTblList
@src as int,
@cat as int,
@age as int,
@sex as int,
@race as int,
@hisp as int,
@year as int,
@keywords as varChar(50)
AS

SET NOCOUNT ON
--sixth and final to get sourceShort
select ns5.tblID, tblTitle, ns5.sourceID, pr6.sourceShort from xtblSource pr6,
--fifth nest to get sourceID
(select pr5.sourceID, ns4.tblID, ns4.tblTitle from xtblSS pr5,
--fourth nest table titles
(select pr4.tblID, pr4.tblTitle, ssID from xtblTitles pr4,
--third nested select to get table tblID
(select pr3.tblID from tblStatTitles pr3,
--second nested select for parameters other than age or categories
(select pr2.titleID from tblStats pr2,
--first nested select to get categories
--SHOULDN"T THIS BE A NESTED

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-20 : 07:58:15

>> temp tables, views and concatenanted, executed sql statements are bad, bad, bad
Not necessarily - depends on how they are used.

This will never do anything
IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'u' AND name = '#tempStats' )
DROP TABLE #tempStats

how about something like

where (src = @src or @src = 0)
and (cat = @cat or @cat = 0)
....

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources