?
Solved

Nested stored proc or UDF?

Posted on 2009-04-19
7
Medium Priority
?
312 Views
Last Modified: 2012-05-06
Hi all,

I have a bunch of stored procs that share identical WHERE clauses, so to make it easier to maintain I'd like to encapsulate the WHERE clauses into a seperate stored proc or UDF. I'm not sure which would be better, and also how to write the code for it.

So far I've written a stored proc (code for this is below) that pulls out the IDs for all the records I need to validate against, so in the calling stored procs I want to do something like:

SELECT whatever
FROM whatever
WHERE digitalStockID IN
    (EXEC spSelectValidDigital_Web)

...but I get an 'Incorrect syntax near the keyword EXEC' error.

So, my question is:
a) Am I going about this the right way? and
b) How do I write the code correctly?

Thanks a lot!
Chris
ALTER PROCEDURE [dbo].[spSelectValidDigital_Web]
(
@CustomerCountryCode INT
)
AS
 
---SET NOCOUNT ON
 
DECLARE @SydneyTime DATETIME
SET @SydneyTime = dbo.fnGetSydneyTime(GETDATE())
 
SELECT DISTINCT
ds.[digitalStockID]
 
FROM
dbo.digitalStock ds
INNER JOIN dbo.digitalLicensors dl ON ds.licensorID = dl.digitalLicensorsID
INNER JOIN dbo.digitalTracks dt ON ds.digitalStockID = dt.digitalStockID
LEFT OUTER JOIN dbo.digitalWorldwideReleases dwr ON ds.digitalStockID = dwr.digitalStockID
LEFT OUTER JOIN dbo.digitalReleaseDates drd ON ds.digitalStockID = drd.digitalStockID
 
WHERE
 
--- Exclude distributors we don't deal with anyomore
CASE WHEN @CustomerCountryCode <> 1 AND (ds.licensorID IN (1,4)) THEN
	0
ELSE
	1
END > 0
 
--- Make sure it is allowed to go online ---
AND ds.online = 1
AND (dt.online = 1)
AND ds.[excludeFromCatalogue] <> 1
AND (dt.availableSeperately = 1 or dt.availableSeperately IS null)
 
--- Exclude full mix tracks for the time being
AND dt.[duration] < 1200000
 
--- Release date calculations next ---
AND (ds.digitalStockID IN
		(SELECT     digitalStockID
		FROM          dbo.digitalReleaseDates
		WHERE      countryID = @CustomerCountryCode)
    OR ds.digitalStockID IN
		(SELECT digitalStockID
		FROM dbo.digitalWorldwideReleases)
	)
 
AND (
		(@SydneyTime BETWEEN drd.releaseStartDate AND drd.releaseEndDate)
	OR
		(@SydneyTime BETWEEN dwr.releaseStartDate AND dwr.releaseEndDate )
	OR
		(@SydneyTime > drd.releaseStartDate)
	OR
		(@SydneyTime > dwr.releaseStartDate)
	)
 
--- Region checking next ---
AND (drd.countryID = @CustomerCountryCode OR ds.digitalStockID IN
		(SELECT digitalStockID
		FROM dbo.digitalWorldwideReleases
		)
	)

Open in new window

0
Comment
Question by:squeakyloboy
  • 4
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
Chris M earned 1000 total points
ID: 24182192
Hi,
You cannot execute a procedure like a nested select statement.
Therefore your tsql code SELECT whatever FROM whatever WHERE digitalStockID IN
    (EXEC spSelectValidDigital_Web)
would definitely not run.
Please note that a stored procedure is not always taken as a nexted query in this case.
It's best that you instead use a view and place your modified contents of the procedure in there or you use a temporary table, let your proceure populate it and run your query against it.
Regards,
Chris Musasizi
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24182197
We have done it like this, create a table-valued udf like below then use it in a join to the tables in the query to use it

SELECT whateverColumns
FROM whateverTable wT
INNER JOIN dbo.spSelectValidDigital_Web(@CustomerCountryCode ) DW on wT.digitalStockID = DW.digitalStockID

CREATE FUNCTION [dbo].[udfSelectValidDigital_Web]
(
@CustomerCountryCode INT
)
RETURNS 
@DigitalIds Table (digitalStockID int)
AS
 
---SET NOCOUNT ON
 
DECLARE @SydneyTime DATETIME
SET @SydneyTime = dbo.fnGetSydneyTime(GETDATE())
 
INSERT INTO @DigitalIds (digitalStockID)
SELECT DISTINCT
ds.[digitalStockID]
 
FROM
dbo.digitalStock ds
INNER JOIN dbo.digitalLicensors dl ON ds.licensorID = dl.digitalLicensorsID
INNER JOIN dbo.digitalTracks dt ON ds.digitalStockID = dt.digitalStockID
LEFT OUTER JOIN dbo.digitalWorldwideReleases dwr ON ds.digitalStockID = dwr.digitalStockID
LEFT OUTER JOIN dbo.digitalReleaseDates drd ON ds.digitalStockID = drd.digitalStockID
 
WHERE
 
--- Exclude distributors we don't deal with anyomore
CASE WHEN @CustomerCountryCode <> 1 AND (ds.licensorID IN (1,4)) THEN
	0
ELSE
	1
END > 0
 
--- Make sure it is allowed to go online ---
AND ds.online = 1
AND (dt.online = 1)
AND ds.[excludeFromCatalogue] <> 1
AND (dt.availableSeperately = 1 or dt.availableSeperately IS null)
 
--- Exclude full mix tracks for the time being
AND dt.[duration] < 1200000
 
--- Release date calculations next ---
AND (ds.digitalStockID IN
		(SELECT     digitalStockID
		FROM          dbo.digitalReleaseDates
		WHERE      countryID = @CustomerCountryCode)
    OR ds.digitalStockID IN
		(SELECT digitalStockID
		FROM dbo.digitalWorldwideReleases)
	)
 
AND (
		(@SydneyTime BETWEEN drd.releaseStartDate AND drd.releaseEndDate)
	OR
		(@SydneyTime BETWEEN dwr.releaseStartDate AND dwr.releaseEndDate )
	OR
		(@SydneyTime > drd.releaseStartDate)
	OR
		(@SydneyTime > dwr.releaseStartDate)
	)
 
--- Region checking next ---
AND (drd.countryID = @CustomerCountryCode OR ds.digitalStockID IN
		(SELECT digitalStockID
		FROM dbo.digitalWorldwideReleases
		)
	)
Return

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24182205
sorry that sample usage should have been:
SELECT whateverColumns
FROM whateverTable wT
INNER JOIN dbo.udfSelectValidDigital_Web(@CustomerCountryCode ) DW on wT.digitalStockID = DW.digitalStockID
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24182228
One more correction.  You have to put the body of the function in a BEGIN END block.  Just put BEGIN after the AS line and END at the end of it.  Sorry about that.
0
 

Author Comment

by:squeakyloboy
ID: 24182238
Hi Chris, thanks for the reply.

Ok, I'm going to go with the temp table option - I don't have much experience with these, I just tried this:

WHERE ds.[digitalStockID] IN
      (
      CREATE TABLE #tempDigital (id INT PRIMARY KEY)
      
      INSERT INTO [#tempDigital] (
            [id]
      ) VALUES (
            EXEC [spSelectValidDigital_Web]
      @CustomerCountryCode = 1
      )
)

...but I get these errors:

Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'EXEC'.
0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 1000 total points
ID: 24184111
What he meant by using a temp table was something like this
--First create and populate the temp table
CREATE TABLE #tempDigital (id INT PRIMARY KEY CLUSTERED)
 
INSERT INTO [#tempDigital] ([id]) 
EXEC [spSelectValidDigital_Web] @CustomerCountryCode = 1
 
--Then use it like one of these
SELECT whatever
FROM whatever
WHERE digitalStockID IN
    (Select Id from #tempDigital)
    
--- OR
 
SELECT whatever
FROM whatever
INNER JOIN #tempDigital tempD
on whatever.digitalStockID = tempD.Id

Open in new window

0
 

Author Comment

by:squeakyloboy
ID: 24189636
Ok got it, thanks guys, I'll split points between you.

Thanks for the help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question