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.
