Help with user defined function

I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.

It works perfect except for one issue. It doesn't check to see if today's date is a holiday.

I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"

It then moves ahead two business days and returns that date.

Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.

I really don't want to rewrite the whole script .

Code------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

--DROP FUNCTION GetStartDate

--declare function receiving two parameters -
--the date we start counting and the number of business days

CREATE FUNCTION GetStartDate (@.startdate datetime, @.days int)
RETURNS datetime
AS
BEGIN

--declare a counter to keep track of how many days are passing
declare @.counter int

/*
Check your business rules. If 4 business days means you
count starting tomorrow, set counter to 0. If you start
counting today, set counter to 1
*/
set @.counter = 1

--declare a variable to hold the ending date
declare @.enddate datetime

--set the end date to the start date. we'll be
-- incrementing it for each passing business day
set @.enddate = @.startdate

/*
Start your loop.
While your counter (which was set to 1), is less than
or equal to the number of business days increment your
end date
*/
WHILE @.counter <= @.days

BEGIN

--for each day, we'll add one to the end date
set @.enddate = DATEADD(dd, 1, @.enddate)

--If the day is between 2 and 6 (meaning it's a week
--day and the day is not in the holiday table, we'll
--increment the counter
IF (DATEPART(dw, @.enddate) between 2 and 6) AND
(@.enddate not in
(
select HolidayDate
from tFederalHoliday
where [HolidayYear] = datepart(yyyy,@.enddate)
)
)
BEGIN
set @.counter = @.counter + 1
END

--end the while loop
END

--return the end date
RETURN @.enddate

--end the function
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------

You can have a table with a list of holidays (both your company declared holidays and public holidays) and have the query check against the table in your UDF. This gives you the flexibility of adding/removing new holidays each year. You can either put the holidays in a table or even hardcode them in the UDF, whichever works best for you.

Help with UDF using OPENROWSET to EXECUTE sproc

Here is the UDF I am trying to create:
ALTER FUNCTION dbo.TieredAccessCounties
(
@.StateCHAR(2)
, @.SourceTableCHAR(1)
, @.UserNameVARCHAR(30)
)
RETURNS TABLE
AS
RETURN
SELECT A.* FROM
OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
AS A
I cannot get this to work. I am getting these messages:
Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
Line 10
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
Line 10
Must declare the variable '@.SourceTable'.
[OLE/DB provider returned message: Deferred prepare could not be
completed.]
OPENROWSET is opening the connection to MDWDATA and executing the following
(literally):
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName '
It has no idea what @.SourceTable and @.UserName are. Unfortunately, there's
no way I know of to pass values into OPENROWSET within a UDF.
Can you describe what you're trying to do? Maybe there's a better way than
using a UDF.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104430908.817837.179350@.z14g2000cwz.googlegr oups.com...
> Here is the UDF I am trying to create:
> ALTER FUNCTION dbo.TieredAccessCounties
> (
> @.State CHAR(2)
> , @.SourceTable CHAR(1)
> , @.UserName VARCHAR(30)
> )
> RETURNS TABLE
> AS
> RETURN
> SELECT A.* FROM
> OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
> 'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
> AS A
> I cannot get this to work. I am getting these messages:
> Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
> Line 10
> Statement(s) could not be prepared.
> Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
> Line 10
> Must declare the variable '@.SourceTable'.
> [OLE/DB provider returned message: Deferred prepare could not be
> completed.]
>
|||The sproc dbo.AccountFetchCounties is a bit of business logic that
takes 3 parms and returns a set of rows representing state-county areas
that are "allowed" for a given username. I need to add this
functionality inside of a much larger stored procedure and I need to
JOIN the output of this sproc with another SELECT. The first problem I
ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
because I had created a temp table to hold the rows back from
AccountFetchCounties (but this in turn was inside of an outer INSERT
into tableX EXECUTE myOuterSproc structure.
So that is why I thought to use User Defined Function to return a
table. But then I see that a UDF cannot call a stored procedure. So I
researched and discovered OPENROWSET as an alternative. But now I am
stuck with this strange message. I really appreciate your help. Thank
you for your quick reply.
|||Can you post some code to duplicate the CANNOT BE NESTED error? I've never
seen it before, and was just about to recommend a temp table. There must be
a way around that...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104433457.065710.65650@.z14g2000cwz.googlegro ups.com...
> The sproc dbo.AccountFetchCounties is a bit of business logic that
> takes 3 parms and returns a set of rows representing state-county areas
> that are "allowed" for a given username. I need to add this
> functionality inside of a much larger stored procedure and I need to
> JOIN the output of this sproc with another SELECT. The first problem I
> ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
> because I had created a temp table to hold the rows back from
> AccountFetchCounties (but this in turn was inside of an outer INSERT
> into tableX EXECUTE myOuterSproc structure.
> So that is why I thought to use User Defined Function to return a
> table. But then I see that a UDF cannot call a stored procedure. So I
> researched and discovered OPENROWSET as an alternative. But now I am
> stuck with this strange message. I really appreciate your help. Thank
> you for your quick reply.
>
|||Adam,
Here's an example:
create table T (
i int
)
go
create proc p as select 4
go
create proc q as
insert into T exec p
go
insert into T exec q
go
drop proc p,q
drop table T
I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
nested. In other words, the statement INSERT INTO T EXEC q will fail
if the procedure q contains an INSERT .. EXEC construction.
SK
Adam Machanic wrote:

>Can you post some code to duplicate the CANNOT BE NESTED error? I've never
>seen it before, and was just about to recommend a temp table. There must be
>a way around that...
>
>
|||"Steve Kass" <skass@.drew.edu> wrote in message
news:uU38dyu7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
> nested. In other words, the statement INSERT INTO T EXEC q will fail
> if the procedure q contains an INSERT .. EXEC construction.
That makes perfect sense. The example you posted doesn't seem to do
anything, whereas at least something like this has some semblance of
purpose:
EXEC ('EXEC (''SELECT 1'')')
... not that I'd do that, but at least it makes more sense than nesting
an INSERT
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||-- here is the 1st fragment, inside of a large production sproc. It
needs to create and populate #Areas which
-- will be used in JOINS later in the sproc and in the inner sprocs it
calls.
create table #areas
(
statechar(2),
countychar(3)
, SMSA_CDvarchar(10))
Execute dbo.AMS_I_GetAreasV2 -- 12/29/2004 JJA: add
parm for tiered-access support of custom areas using
dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID-- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName-- 12/29/2004 JJA: Make Custom Area
Definitions conform to tiered access rules and limits
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTablevarchar(1) = 'P' -- P: Purchase-Money R: Refi B:
Both F: FHA V: VA G: All_Govt
, @.CustomID INT = 0-- 09/20/2004 JJA; 12/09/2004 JJA
, @.Debug int = 0-- 03/01/2004 JJA
)
As
Set NOCount ON
declare @.err int, @.rows int-- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';-- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =
' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0-- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN-- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.'-- 09/20/2004 JJA
INSERT INTO #Areas-- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID--
09/20/2004 JJA
,@.Debug-- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT-- 09/20/2004 JJA
GOTO CommonExitPoint-- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
..........etc. etc.
-----
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
----*
-- PURPOSE: Translate an ID for a custom area into a record set of
--areas that the custom area defines. If any metro-area is part
--of a custom-area definition, its component STATE and COUNTY are
--included in this record set (otherwise SMSA is left as NULL).
-- USAGE: Called by AMS_I_GetAreasV2 to populate #Areas
-- HISTORY: 09/23/2004 JJA - implement new sproc
----*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.CustomIDint-- key to parent table (i.e. has name of this
definition, etc.)
,@.Debugint= 0-- set default to 1 for Query-Analyzer debugging
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CDCHAR(3),
TypeCountyVARCHAR(60),
State_CDCHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties (NULL, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
would have been added to the
--SELECT just below as another table to JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
at the top #AREAS is being
-- populated and I got the complaint about nesting immediately
trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0-- where 0 = 1 rejects record
END = 1-- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD
|||I am sorry I did not chop off all comments from the code posted above
(it is pretty hard to read). I will try to summarize:
In this problem, there are 2 pieces of business logic implemented by
stored procedures and called in numerous places by other stored
procedures.
AMS_I_GetAreasV2 is called to populate a temporary table #AREAS which
is used in JOINS here and is used in reporting elsewhere.
AccountFetchCounties is called to return a set of allowed counties
based on a code and username.
In short, I need to expand the functionality of AMS_I_GetAreasV2 such
that in some cases it calls AccountFetchCounties, joining the allowed
set of rows produced by that sproc with another SELECT.
As I said, when I ran into the complaint about nesting, I tried making
a UDF to EXECUTE AccountFetchCounties but that is disallowed. Then I
tried OPENROWSET with EXECUTE of AccountFetchCounties but that does not
allow parameters to be passed to the sproc. Then I posted the original
question.
To solve this in a semi-ugly way, I have replicated all the
functionality of my sproc, AccountFetchCounties, into a UDF. Then in
the SELECT inside 'AMS_I_GetAreasV2_CustomAreaTranslator', I have
added this UDF which returns a table to my JOIN which works pretty
well.
But I am uncomfortable with the idea of cloning this "business logic"
from SPROC to UDF because of keeping my sanity in the future
maintenance of this code. Thanks in advance for trying to follow my
story and help.
|||Here is a better post of code (now cleaned up and fewer comments) that
caused the original nesting error:
-- this CREATE is inside a large production sproc
-- here is the first relevant fragment:
create table #areas
(
state char(2)
, county char(3)
, SMSA_CD varchar(10)
)
Execute dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P'
, @.CustomID INT = 0
, @.Debug int = 0
)
As
Set NOCount ON
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
--, @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
..........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
SET NOCOUNT ON
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CDCHAR(3),
TypeCountyVARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
-- would have been added to the SELECT just below as another table to
JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
-- at the top #AREAS is being populated and I got the complaint about
-- nesting immediately trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD
|||Please reply if you can get a chance. I tried to describe my problem
better - maybe I gave too much detail. Bottom line is that I had to
clone business logic of a stored procedure into a user defined
function. I do not like having to do this but it works. Like I said, in
trying to extend the processing of the stored procedure, I ran into
multiple restrictions which I have tried to describe.

Help with UDF using OPENROWSET to EXECUTE sproc

Here is the UDF I am trying to create:
ALTER FUNCTION dbo.TieredAccessCounties
(
@.State CHAR(2)
, @.SourceTable CHAR(1)
, @.UserName VARCHAR(30)
)
RETURNS TABLE
AS
RETURN
SELECT A.* FROM
OPENROWSET('SQLOLEDB','MDWDATA';'sa';'pa
sswordX',
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
AS A
I cannot get this to work. I am getting these messages:
Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
Line 10
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
Line 10
Must declare the variable '@.SourceTable'.
[OLE/DB provider returned message: Deferred prepare could not be
completed.]OPENROWSET is opening the connection to MDWDATA and executing the following
(literally):
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName '
It has no idea what @.SourceTable and @.UserName are. Unfortunately, there's
no way I know of to pass values into OPENROWSET within a UDF.
Can you describe what you're trying to do? Maybe there's a better way than
using a UDF.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104430908.817837.179350@.z14g2000cwz.googlegroups.com...
> Here is the UDF I am trying to create:
> ALTER FUNCTION dbo.TieredAccessCounties
> (
> @.State CHAR(2)
> , @.SourceTable CHAR(1)
> , @.UserName VARCHAR(30)
> )
> RETURNS TABLE
> AS
> RETURN
> SELECT A.* FROM
> OPENROWSET('SQLOLEDB','MDWDATA';'sa';'pa
sswordX',
> 'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
> AS A
> I cannot get this to work. I am getting these messages:
> Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
> Line 10
> Statement(s) could not be prepared.
> Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
> Line 10
> Must declare the variable '@.SourceTable'.
> [OLE/DB provider returned message: Deferred prepare could not be
> completed.]
>|||The sproc dbo.AccountFetchCounties is a bit of business logic that
takes 3 parms and returns a set of rows representing state-county areas
that are "allowed" for a given username. I need to add this
functionality inside of a much larger stored procedure and I need to
JOIN the output of this sproc with another SELECT. The first problem I
ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
because I had created a temp table to hold the rows back from
AccountFetchCounties (but this in turn was inside of an outer INSERT
into tableX EXECUTE myOuterSproc structure.
So that is why I thought to use User Defined Function to return a
table. But then I see that a UDF cannot call a stored procedure. So I
researched and discovered OPENROWSET as an alternative. But now I am
stuck with this strange message. I really appreciate your help. Thank
you for your quick reply.|||Can you post some code to duplicate the CANNOT BE NESTED error? I've never
seen it before, and was just about to recommend a temp table. There must be
a way around that...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104433457.065710.65650@.z14g2000cwz.googlegroups.com...
> The sproc dbo.AccountFetchCounties is a bit of business logic that
> takes 3 parms and returns a set of rows representing state-county areas
> that are "allowed" for a given username. I need to add this
> functionality inside of a much larger stored procedure and I need to
> JOIN the output of this sproc with another SELECT. The first problem I
> ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
> because I had created a temp table to hold the rows back from
> AccountFetchCounties (but this in turn was inside of an outer INSERT
> into tableX EXECUTE myOuterSproc structure.
> So that is why I thought to use User Defined Function to return a
> table. But then I see that a UDF cannot call a stored procedure. So I
> researched and discovered OPENROWSET as an alternative. But now I am
> stuck with this strange message. I really appreciate your help. Thank
> you for your quick reply.
>|||Adam,
Here's an example:
create table T (
i int
)
go
create proc p as select 4
go
create proc q as
insert into T exec p
go
insert into T exec q
go
drop proc p,q
drop table T
I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
nested. In other words, the statement INSERT INTO T EXEC q will fail
if the procedure q contains an INSERT .. EXEC construction.
SK
Adam Machanic wrote:

>Can you post some code to duplicate the CANNOT BE NESTED error? I've never
>seen it before, and was just about to recommend a temp table. There must b
e
>a way around that...
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:uU38dyu7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
> nested. In other words, the statement INSERT INTO T EXEC q will fail
> if the procedure q contains an INSERT .. EXEC construction.
That makes perfect sense. The example you posted doesn't seem to do
anything, whereas at least something like this has some semblance of
purpose:
EXEC ('EXEC (''SELECT 1'')')
.. not that I'd do that, but at least it makes more sense than nesting
an INSERT
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||-- here is the 1st fragment, inside of a large production sproc. It
needs to create and populate #Areas which
-- will be used in JOINS later in the sproc and in the inner sprocs it
calls.
create table #areas
(
state char(2) ,
county char(3)
, SMSA_CD varchar(10) )
Execute dbo.AMS_I_GetAreasV2 -- 12/29/2004 JJA: add
parm for tiered-access support of custom areas using
dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID -- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName -- 12/29/2004 JJA: Make Custom Area
Definitions conform to tiered access rules and limits
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P' -- P: Purchase-Money R: Refi B:
Both F: FHA V: VA G: All_Govt
, @.CustomID INT = 0 -- 09/20/2004 JJA; 12/09/2004 JJA
, @.Debug int = 0 -- 03/01/2004 JJA
)
As
Set NOCount ON
declare @.err int, @.rows int -- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = ''; -- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =
' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0 -- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID --
09/20/2004 JJA
,@.Debug -- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT -- 09/20/2004 JJA
GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
----
--
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
----
--*
-- PURPOSE: Translate an ID for a custom area into a record set of
-- areas that the custom area defines. If any metro-area is part
-- of a custom-area definition, its component STATE and COUNTY are
-- included in this record set (otherwise SMSA is left as NULL).
-- USAGE: Called by AMS_I_GetAreasV2 to populate #Areas
-- HISTORY: 09/23/2004 JJA - implement new sproc
----
--*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.CustomID int -- key to parent table (i.e. has name of this
definition, etc.)
,@.Debug int = 0 -- set default to 1 for Query-Analyzer debugging
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties (NULL, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
would have been added to the
-- SELECT just below as another table to JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
at the top #AREAS is being
-- populated and I got the complaint about nesting immediately
trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||I am sorry I did not chop off all comments from the code posted above
(it is pretty hard to read). I will try to summarize:
In this problem, there are 2 pieces of business logic implemented by
stored procedures and called in numerous places by other stored
procedures.
AMS_I_GetAreasV2 is called to populate a temporary table #AREAS which
is used in JOINS here and is used in reporting elsewhere.
AccountFetchCounties is called to return a set of allowed counties
based on a code and username.
In short, I need to expand the functionality of AMS_I_GetAreasV2 such
that in some cases it calls AccountFetchCounties, joining the allowed
set of rows produced by that sproc with another SELECT.
As I said, when I ran into the complaint about nesting, I tried making
a UDF to EXECUTE AccountFetchCounties but that is disallowed. Then I
tried OPENROWSET with EXECUTE of AccountFetchCounties but that does not
allow parameters to be passed to the sproc. Then I posted the original
question.
To solve this in a semi-ugly way, I have replicated all the
functionality of my sproc, AccountFetchCounties, into a UDF. Then in
the SELECT inside 'AMS_I_GetAreasV2_CustomAreaTranslator',
I have
added this UDF which returns a table to my JOIN which works pretty
well.
But I am uncomfortable with the idea of cloning this "business logic"
from SPROC to UDF because of keeping my sanity in the future
maintenance of this code. Thanks in advance for trying to follow my
story and help.|||Here is a better post of code (now cleaned up and fewer comments) that
caused the original nesting error:
-- this CREATE is inside a large production sproc
-- here is the first relevant fragment:
create table #areas
(
state char(2)
, county char(3)
, SMSA_CD varchar(10)
)
Execute dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P'
, @.CustomID INT = 0
, @.Debug int = 0
)
As
Set NOCount ON
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
-- , @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
SET NOCOUNT ON
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
-- would have been added to the SELECT just below as another table to
JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
-- at the top #AREAS is being populated and I got the complaint about
-- nesting immediately trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||Please reply if you can get a chance. I tried to describe my problem
better - maybe I gave too much detail. Bottom line is that I had to
clone business logic of a stored procedure into a user defined
function. I do not like having to do this but it works. Like I said, in
trying to extend the processing of the stored procedure, I ran into
multiple restrictions which I have tried to describe.

Help with UDF using OPENROWSET to EXECUTE sproc

Here is the UDF I am trying to create:
ALTER FUNCTION dbo.TieredAccessCounties
(
@.State CHAR(2)
, @.SourceTable CHAR(1)
, @.UserName VARCHAR(30)
)
RETURNS TABLE
AS
RETURN
SELECT A.* FROM
OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
AS A
I cannot get this to work. I am getting these messages:
Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
Line 10
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
Line 10
Must declare the variable '@.SourceTable'.
[OLE/DB provider returned message: Deferred prepare could not be
completed.]OPENROWSET is opening the connection to MDWDATA and executing the following
(literally):
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName '
It has no idea what @.SourceTable and @.UserName are. Unfortunately, there's
no way I know of to pass values into OPENROWSET within a UDF.
Can you describe what you're trying to do? Maybe there's a better way than
using a UDF.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104430908.817837.179350@.z14g2000cwz.googlegroups.com...
> Here is the UDF I am trying to create:
> ALTER FUNCTION dbo.TieredAccessCounties
> (
> @.State CHAR(2)
> , @.SourceTable CHAR(1)
> , @.UserName VARCHAR(30)
> )
> RETURNS TABLE
> AS
> RETURN
> SELECT A.* FROM
> OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
> 'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
> AS A
> I cannot get this to work. I am getting these messages:
> Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
> Line 10
> Statement(s) could not be prepared.
> Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
> Line 10
> Must declare the variable '@.SourceTable'.
> [OLE/DB provider returned message: Deferred prepare could not be
> completed.]
>|||The sproc dbo.AccountFetchCounties is a bit of business logic that
takes 3 parms and returns a set of rows representing state-county areas
that are "allowed" for a given username. I need to add this
functionality inside of a much larger stored procedure and I need to
JOIN the output of this sproc with another SELECT. The first problem I
ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
because I had created a temp table to hold the rows back from
AccountFetchCounties (but this in turn was inside of an outer INSERT
into tableX EXECUTE myOuterSproc structure.
So that is why I thought to use User Defined Function to return a
table. But then I see that a UDF cannot call a stored procedure. So I
researched and discovered OPENROWSET as an alternative. But now I am
stuck with this strange message. I really appreciate your help. Thank
you for your quick reply.|||Can you post some code to duplicate the CANNOT BE NESTED error? I've never
seen it before, and was just about to recommend a temp table. There must be
a way around that...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104433457.065710.65650@.z14g2000cwz.googlegroups.com...
> The sproc dbo.AccountFetchCounties is a bit of business logic that
> takes 3 parms and returns a set of rows representing state-county areas
> that are "allowed" for a given username. I need to add this
> functionality inside of a much larger stored procedure and I need to
> JOIN the output of this sproc with another SELECT. The first problem I
> ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
> because I had created a temp table to hold the rows back from
> AccountFetchCounties (but this in turn was inside of an outer INSERT
> into tableX EXECUTE myOuterSproc structure.
> So that is why I thought to use User Defined Function to return a
> table. But then I see that a UDF cannot call a stored procedure. So I
> researched and discovered OPENROWSET as an alternative. But now I am
> stuck with this strange message. I really appreciate your help. Thank
> you for your quick reply.
>|||Adam,
Here's an example:
create table T (
i int
)
go
create proc p as select 4
go
create proc q as
insert into T exec p
go
insert into T exec q
go
drop proc p,q
drop table T
I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
nested. In other words, the statement INSERT INTO T EXEC q will fail
if the procedure q contains an INSERT .. EXEC construction.
SK
Adam Machanic wrote:
>Can you post some code to duplicate the CANNOT BE NESTED error? I've never
>seen it before, and was just about to recommend a temp table. There must be
>a way around that...
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:uU38dyu7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
> nested. In other words, the statement INSERT INTO T EXEC q will fail
> if the procedure q contains an INSERT .. EXEC construction.
That makes perfect sense. The example you posted doesn't seem to do
anything, whereas at least something like this has some semblance of
purpose:
EXEC ('EXEC (''SELECT 1'')')
... not that I'd do that, but at least it makes more sense than nesting
an INSERT :)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||-- here is the 1st fragment, inside of a large production sproc. It
needs to create and populate #Areas which
-- will be used in JOINS later in the sproc and in the inner sprocs it
calls.
create table #areas
(
state char(2) ,
county char(3)
, SMSA_CD varchar(10) )
Execute dbo.AMS_I_GetAreasV2 -- 12/29/2004 JJA: add
parm for tiered-access support of custom areas using
dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID -- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName -- 12/29/2004 JJA: Make Custom Area
Definitions conform to tiered access rules and limits
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P' -- P: Purchase-Money R: Refi B:
Both F: FHA V: VA G: All_Govt
, @.CustomID INT = 0 -- 09/20/2004 JJA; 12/09/2004 JJA
, @.Debug int = 0 -- 03/01/2004 JJA
)
As
Set NOCount ON
declare @.err int, @.rows int -- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = ''; -- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0 -- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID --
09/20/2004 JJA
,@.Debug -- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT -- 09/20/2004 JJA
GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-----
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
----*
-- PURPOSE: Translate an ID for a custom area into a record set of
-- areas that the custom area defines. If any metro-area is part
-- of a custom-area definition, its component STATE and COUNTY are
-- included in this record set (otherwise SMSA is left as NULL).
-- USAGE: Called by AMS_I_GetAreasV2 to populate #Areas
-- HISTORY: 09/23/2004 JJA - implement new sproc
----*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.CustomID int -- key to parent table (i.e. has name of this
definition, etc.)
,@.Debug int = 0 -- set default to 1 for Query-Analyzer debugging
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties (NULL, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
would have been added to the
-- SELECT just below as another table to JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
at the top #AREAS is being
-- populated and I got the complaint about nesting immediately
trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||I am sorry I did not chop off all comments from the code posted above
(it is pretty hard to read). I will try to summarize:
In this problem, there are 2 pieces of business logic implemented by
stored procedures and called in numerous places by other stored
procedures.
AMS_I_GetAreasV2 is called to populate a temporary table #AREAS which
is used in JOINS here and is used in reporting elsewhere.
AccountFetchCounties is called to return a set of allowed counties
based on a code and username.
In short, I need to expand the functionality of AMS_I_GetAreasV2 such
that in some cases it calls AccountFetchCounties, joining the allowed
set of rows produced by that sproc with another SELECT.
As I said, when I ran into the complaint about nesting, I tried making
a UDF to EXECUTE AccountFetchCounties but that is disallowed. Then I
tried OPENROWSET with EXECUTE of AccountFetchCounties but that does not
allow parameters to be passed to the sproc. Then I posted the original
question.
To solve this in a semi-ugly way, I have replicated all the
functionality of my sproc, AccountFetchCounties, into a UDF. Then in
the SELECT inside 'AMS_I_GetAreasV2_CustomAreaTranslator', I have
added this UDF which returns a table to my JOIN which works pretty
well.
But I am uncomfortable with the idea of cloning this "business logic"
from SPROC to UDF because of keeping my sanity in the future
maintenance of this code. Thanks in advance for trying to follow my
story and help.|||Here is a better post of code (now cleaned up and fewer comments) that
caused the original nesting error:
-- this CREATE is inside a large production sproc
-- here is the first relevant fragment:
create table #areas
(
state char(2)
, county char(3)
, SMSA_CD varchar(10)
)
Execute dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P'
, @.CustomID INT = 0
, @.Debug int = 0
)
As
Set NOCount ON
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
-- , @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
SET NOCOUNT ON
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
-- would have been added to the SELECT just below as another table to
JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
-- at the top #AREAS is being populated and I got the complaint about
-- nesting immediately trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||Please reply if you can get a chance. I tried to describe my problem
better - maybe I gave too much detail. Bottom line is that I had to
clone business logic of a stored procedure into a user defined
function. I do not like having to do this but it works. Like I said, in
trying to extend the processing of the stored procedure, I ran into
multiple restrictions which I have tried to describe.

Help with UDF Syntax

I am a somewhat newbie to UDFs so don't laugh too hard!
I have 2 tables:
Tbl_Processes (first table)
ID
Process_Name
Included_In_Test
Tbl_Sub_Processes (second table)
ID
Sub_Process_Name
Lookup_To_Tbl_Processes_For_Process (points to the ID field of the
first table)
Included_In_Test
Here is what I am trying to do as explained in plain (I hope) english:
If Included_In_Test in the Processes table is 0 then return 0
BUT, if Included_In_Test in the Processes table is 1 then we have to find
out if any of the Sub Processes are also included. If none are, then we are
testing at the Process level and we still return 1
BUT, if only some of the Sub Processes are included in the test, then we
return 1 only for those that are included, otherwise we return zero.
Here is the code I am trying, and when I do a syntax check, I get a message
saying "Incorrect syntax near the keyword 'to'." Since there is no
occurrence of the character string 'to' in the code, I am puzzled. I believ
e
it has to do with my "SELECT SUM(Case " construct. Can anyone help? Or, am
I missing something completely different and simpler?
CREATE FUNCTION [dbo]. [Is_Process_And_Or_Sub_Process_Included_
In_Test]
(
@.ProcessID int,
@.SubProcessID int
)
RETURNS int
AS
BEGIN
Declare @.Result int
if @.ProcessID is null or @.SubProcessID is null
set @.Result to 0
ELSE
if (SELECT Included_In_Test FROM dbo.Tbl_Processes WHERE ID = @.ProcessID)
= 0
SET @.Result = 0
ELSE
if (SELECT SUM(Case When dbo.Tbl_Sub_Processes.Include_In_Test = 1 then
1 else 0 end) AS NumberOfSubProcessesIncluded) = 0
SET @.Result = 1
else
if (SELECT Included_In_Test FROM dbo.Tbl_Sub_Processess WHERE ID =
@.SubProcessID) = 1
SET @.Result = 1
else
Set @.Result = 0
ENDHi Bill,
set @.Result = 0
HTH, Jens Suessmeyer.|||Hi Bill,
whenever you get an error you can double click on that error and it will (in
many cases) will take you to the line where the error is.
Or the second way to look for an error is to do a <ctrl>+f and to a search
on whatever error you are getting.
In this case it was a trival mistake.
set @.Result = 0 as suggested by Jen
"Bill Sturdevant" wrote:

> I am a somewhat newbie to UDFs so don't laugh too hard!
> I have 2 tables:
> Tbl_Processes (first table)
> ID
> Process_Name
> Included_In_Test
> Tbl_Sub_Processes (second table)
> ID
> Sub_Process_Name
> Lookup_To_Tbl_Processes_For_Process (points to the ID field of the
> first table)
> Included_In_Test
> Here is what I am trying to do as explained in plain (I hope) english:
> If Included_In_Test in the Processes table is 0 then return 0
> BUT, if Included_In_Test in the Processes table is 1 then we have to find
> out if any of the Sub Processes are also included. If none are, then we a
re
> testing at the Process level and we still return 1
> BUT, if only some of the Sub Processes are included in the test, then we
> return 1 only for those that are included, otherwise we return zero.
> Here is the code I am trying, and when I do a syntax check, I get a messag
e
> saying "Incorrect syntax near the keyword 'to'." Since there is no
> occurrence of the character string 'to' in the code, I am puzzled. I beli
eve
> it has to do with my "SELECT SUM(Case " construct. Can anyone help? Or,
am
> I missing something completely different and simpler?
> CREATE FUNCTION [dbo]. [Is_Process_And_Or_Sub_Process_Included_
In_Test]
> (
> @.ProcessID int,
> @.SubProcessID int
> )
> RETURNS int
> AS
> BEGIN
> Declare @.Result int
> if @.ProcessID is null or @.SubProcessID is null
> set @.Result to 0
> ELSE
> if (SELECT Included_In_Test FROM dbo.Tbl_Processes WHERE ID = @.ProcessID
)
> = 0
> SET @.Result = 0
> ELSE
> if (SELECT SUM(Case When dbo.Tbl_Sub_Processes.Include_In_Test = 1 the
n
> 1 else 0 end) AS NumberOfSubProcessesIncluded) = 0
> SET @.Result = 1
> else
> if (SELECT Included_In_Test FROM dbo.Tbl_Sub_Processess WHERE ID =
> @.SubProcessID) = 1
> SET @.Result = 1
> else
> Set @.Result = 0
> END

Help with UDF

Hi,
I've not created one of these before so if anyone can help me get this up
and running that would be great. I want to write a function to obtain the
exchange rate from a table based on three parameters - exhange rate, country
& year.
The complication I have is I want to pass in the value 1 for Euro and 2 for
Dollar so in the function I need an "if" statement to run the appropiate
select statement. So in the function it would be something like this...
if @.iExchangeRate = 1
BEGIN
select Euro from ExchangeRates where CountryID = 35 and YearID = 5
END
ELSE
BEGIN
select Dollar from ExchangeRates where CountryID = 35 and YearID = 5
END
I had a go at creating a UDF based on the above and I get warnings around
the IF statement when creating the function. Is it possible to have
conditions in the UDF.
thanks,
jpYou can have IF in a multi-statement table values function but as you say yo
u got en error, I assume
you are trying an in-line table valued function. You should try to make your
functions in-line as
they can potentially perform much better. But why not use only one SELECT st
atement and use CASE
inside that to do what you want?
CREATE TABLE ExchangeRates (Euro money NULL, Dollar money NULL)
INSERT INTO ExchangeRates VALUES(10, 12)
INSERT INTO ExchangeRates VALUES(NULL, 14)
INSERT INTO ExchangeRates VALUES(15, NULL)
INSERT INTO ExchangeRates VALUES(NULL, NULL)
CREATE FUNCTION x (@.iExchangeRate tinyint)
RETURNS table
AS
RETURN
(
SELECT
CASE
WHEN @.iExchangeRate = 1
THEN Euro
ELSE Dollar
END AS ExchangeRate
FROM ExchangeRates
)
SELECT * FROM x(1)
SELECT * FROM x(2)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message news:Cs_Gf.3414$gB4.2385@.newsfe4-gui.ntli.
net...
> Hi,
> I've not created one of these before so if anyone can help me get this up
and running that would
> be great. I want to write a function to obtain the exchange rate from a ta
ble based on three
> parameters - exhange rate, country & year.
> The complication I have is I want to pass in the value 1 for Euro and 2 fo
r Dollar so in the
> function I need an "if" statement to run the appropiate select statement.
So in the function it
> would be something like this...
> if @.iExchangeRate = 1
> BEGIN
> select Euro from ExchangeRates where CountryID = 35 and YearID = 5
> END
> ELSE
> BEGIN
> select Dollar from ExchangeRates where CountryID = 35 and YearID = 5
> END
> I had a go at creating a UDF based on the above and I get warnings around
the IF statement when
> creating the function. Is it possible to have conditions in the UDF.
> thanks,
> jp
>|||Thanks - got it working with your help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23btVNfjLGHA.3960@.TK2MSFTNGP09.phx.gbl...
> You can have IF in a multi-statement table values function but as you say
> you got en error, I assume you are trying an in-line table valued
> function. You should try to make your functions in-line as they can
> potentially perform much better. But why not use only one SELECT statement
> and use CASE inside that to do what you want?
> CREATE TABLE ExchangeRates (Euro money NULL, Dollar money NULL)
> INSERT INTO ExchangeRates VALUES(10, 12)
> INSERT INTO ExchangeRates VALUES(NULL, 14)
> INSERT INTO ExchangeRates VALUES(15, NULL)
> INSERT INTO ExchangeRates VALUES(NULL, NULL)
> CREATE FUNCTION x (@.iExchangeRate tinyint)
> RETURNS table
> AS
> RETURN
> (
> SELECT
> CASE
> WHEN @.iExchangeRate = 1
> THEN Euro
> ELSE Dollar
> END AS ExchangeRate
> FROM ExchangeRates
> )
> SELECT * FROM x(1)
> SELECT * FROM x(2)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message
> news:Cs_Gf.3414$gB4.2385@.newsfe4-gui.ntli.net...
>