componentization of SPROC - 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)

componentization of SPROC

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-03 : 17:02:20

Hi,

I have a bunch of queries in my application that I need to run against a filter query. What I mean by this is my current SELECT statement has to be compared to another SELECT statement, where the second set of results is removed from the first set. It's just one column.

Example Code of current Query:

SELECT emailAddress FROM emailList

need to be upgraded to

SELECT emailAddress FROM tblUserdetails WHERE emailAddress NOT IN (Select bouncedEmailAddress FROM NDR_EmailMessages)

the filter query might change from time to time so instead of putting this into 6 or 7 queries individually I was wondering if I could make this a SPROC and then somehow call this SPROC inside of another SPROC?

Is this possible?

Thanks very much
mike123

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-03 : 21:13:29

Im not sure Im understanding you correctly... if youre just trying to avoid modifying the filter in 7 different places then you can do something like this:

Create a procedure (like you said) to hold the filter:

create procedure dbo.usp_SelectBounceList
as
set nocount on

select bouncedEmailAddress 
from NDR_EmailMessages

go

Then, in each of the 7 places, exec this proc into a temp table:


-- create table to store bounced list
declare @BouncedList table (EmailAddress varchar(100))

-- populate from proc
insert into @BouncedList (EmailAddress)
	exec dbo.usp_SelectBounceList 

-- return addresses not found in bounced list
select	ud.EmailAddress 
from tblUserDetails ud
left join @BouncedList bl 
	on ud.EmailAddress = bl.EmailAddress
where bl.EmailAddress is null

Or, put the "filter" youre talking about into a view and reference that in each of the 7 places. That way when you alter the view you alter all references to the filter.

Nathan Skerl

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-08-03 : 21:19:31

Hey Mike

I've done something like this in the past.
I had a newsletter engine, but the client wanted to be able to choose the recipient list from some predetermined filters. Something like : User who have joined in the last month, or users from last year who haven't logged in for 6 months.

For each of those filters, I wrote a proc to return the user id, first name, last name and email. Then my "Send Newsletter" proc had a parameter for the name of the required filter proc.

I used that to grab the results of the filter proc and put it in a temp table, which i could then insert into my email queue table. The dynamic bit looked like this :



Create Table #TempUser (
	UserID int,
	FirstName varchar(100),
	LastName varchar(100),
	Email varchar(200)
	)
	
Declare @sql VarChar(4000)
SET @SQL = 'INSERT #TempUser Exec ' + @FilterProc
Exec(@SQL)



INSERT INTO MailQueue (
	FromName,  FromAddress, ToName, ToAddress,
	NewsletterID, UserID, Subject, Body
)

SELECT 
	@FromName, @FromAddress,  Firstname + ' ' + LastName, Email, 
	NewsletterID, UserID, Headline, Body
FROM
	#TempUser
INNER JOIN Newsletter N ON NewsletterID = @NewsletterID

Drop Table #TempUser

Hope that helps

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources