HELP! Concatinated Values

Hello everyone,

I would really appreciate if someone could help me out with this one.

I need to execute a select statement which returns more than one row but I need the values from all the rows returned in a single string.

For example

SELECT * FROM USERS would produce

ID NAME
1 Jason
2 Mark
3 Whatever

I need the returned value to be a string with 'Jason,Mark,Whatever' as a returned result.

Any thoughts anyone?

RegardsI would look towards using a cursor in a stored procedure to loop through each row and concatenating the values one by one.

I'm no big fan of serverside cursors, even if they have their time and place too, so depending on the environment and application demands I would look into putting such logic in the middle tier or even client side.

Cheers,
Robert|||Originally posted by Rawbat
I would look towards using a cursor in a stored procedure to loop through each row and concatenating the values one by one.

I'm no big fan of serverside cursors, even if they have their time and place too, so depending on the environment and application demands I would look into putting such logic in the middle tier or even client side.

Cheers,
Robert

declare @.name varchar(40),@.result varchar(500)
declare Cursor1 cursor
for
SELECT * FROM USERS
open Cursor1
fetch from Cursor1 into @.name
Set @.result=@.name
while(@.@.fetch_status=0)
begin
set @.result = ','+@.name
fetch from Cursor1 into @.name
end

Help! "#1" in Contains returns unexpected

Hi all,
Need some help with this. On a simple search I use the syntax;
where title like'%#1%'
returns what I expect. However when I use my advanced search that allows
many criteria, I go to using indexed fulltext search and CONTAINS. I found
that the Like syntax makes the advanced search run way too long, so I am
trying to avoid it. The syntax ends up being:
contains((title ),'("#1*")')
Note I include the * to supposedly behave somewhat similar to the "like"
syantax.
Well now everything with the '1' in it is returned. I know that CONTAINS
ignores some punctuation like single-quotes but why the '#' sign? It does
return anything with '1' in it and that is a very small string.
(Yes I also found out that I can't pass the #1 as a querystring parameter in
ASP.NET but that is for another group.)
Is there anyway I can get CONTAINS to recognize the combination of "#1*"
?
Thanks to all...
John,
Sorry, but # is also punctuation and serves as a wordbreaker. I know of no
wordbreakers that treat it as otherwise, but that is what it would take to
get the # indexed in the full-text indexes. (Perhaps someone knows better.)
I researched this with a full text index on a parts catalog, where (as you
can imagine) there are many, many # characters.
Perhaps you can do something like:
SELECT * FROM
(SELECT ... CONTAINS (contains((title ),'("1*")')...) AS B
WHERE B.Title LIKE '%#1%'
A derived table is not guaranteed to force execution order, so you might put
the fulltext query results into a temp table, then query from that for the
LIKE string.
RLF
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:OM8DROlQIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Need some help with this. On a simple search I use the syntax;
> --
> where title like'%#1%'
> --
> returns what I expect. However when I use my advanced search that allows
> many criteria, I go to using indexed fulltext search and CONTAINS. I found
> that the Like syntax makes the advanced search run way too long, so I am
> trying to avoid it. The syntax ends up being:
> --
> contains((title ),'("#1*")')
> --
> Note I include the * to supposedly behave somewhat similar to the "like"
> syantax.
> Well now everything with the '1' in it is returned. I know that CONTAINS
> ignores some punctuation like single-quotes but why the '#' sign? It does
> return anything with '1' in it and that is a very small string.
> (Yes I also found out that I can't pass the #1 as a querystring parameter
> in ASP.NET but that is for another group.)
> Is there anyway I can get CONTAINS to recognize the combination of "#1*"
> ?
> Thanks to all...
>
|||Thanks Russell,
I will just have to do better research on the peculiarities of fulltext
search and find a way to get the results I need without using up the server
resources. I am expecting at least 100 concurrent users.
Where there is a will...
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23DAO3EmQIHA.2268@.TK2MSFTNGP02.phx.gbl...
> John,
> Sorry, but # is also punctuation and serves as a wordbreaker. I know of
> no wordbreakers that treat it as otherwise, but that is what it would take
> to get the # indexed in the full-text indexes. (Perhaps someone knows
> better.) I researched this with a full text index on a parts catalog,
> where (as you can imagine) there are many, many # characters.
> Perhaps you can do something like:
> SELECT * FROM
> (SELECT ... CONTAINS (contains((title ),'("1*")')...) AS B
> WHERE B.Title LIKE '%#1%'
> A derived table is not guaranteed to force execution order, so you might
> put the fulltext query results into a temp table, then query from that for
> the LIKE string.
> RLF
>
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
> news:OM8DROlQIHA.1208@.TK2MSFTNGP03.phx.gbl...
>
|||Are you able to create a new column for FTS indexing that replaced # with a
"token", eg. HASH. That way you could search for "HASH1" which would be
treated as a word. I do this myself for a number of characters which are
handled as word breakers that I need to be able to search on, and store the
"tokenised" version plus any additional keywords I want indexed in a
separate column to the description itself.
Dan
John wrote on Wed, 19 Dec 2007 15:59:54 -0500:

> Thanks Russell,

> I will just have to do better research on the peculiarities of fulltext
> search and find a way to get the results I need without using up the
> server resources. I am expecting at least 100 concurrent users.

> Where there is a will...
[vbcol=seagreen]
> "Russell Fields" <russellfields@.nomail.com> wrote in message news:%23DAO3EmQIHA.2268@.TK2MSFTNGP02.phx.gbl...
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
|||Daniel,
Interesting concept. I will indeed consider that possibility.
Thanks...
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eHY2iNyQIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Are you able to create a new column for FTS indexing that replaced # with
> a "token", eg. HASH. That way you could search for "HASH1" which would be
> treated as a word. I do this myself for a number of characters which are
> handled as word breakers that I need to be able to search on, and store
> the "tokenised" version plus any additional keywords I want indexed in a
> separate column to the description itself.
> Dan
>
> John wrote on Wed, 19 Dec 2007 15:59:54 -0500:
>
>
>
>
>
>
>
>
>
>
>

help writing s.proc in sql2005

hi all.

in my sql2005 i have a function that returns a value. func(x) returns j

how can use it in a select clause inside a s.proce?

select bb, func(xx) as jj , from ....

?

You should be able to include it right in your SELECT statement, since it returns a scalar.

You will, however, need to qualify the function with the schema; SELECT dbo.func(xx) or SELECT myschema.func(xx) etc.

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 user defined function

I have the following function:
create function dbo.AttributesList (@.Customerno varchar(10),
@.sold_to_sales_grp varchar(3), @.attr varchar(2))
returns varchar(15)
as
begin
declare @.sOut varchar(15)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '3'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
-- previous
if @.attr = '4'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels_prev x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '5'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels_prev y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '6'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels_prev z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
which you call with a customer number, a sales group, and an attribute.
The tables it queries are built in a job that gets run overnight.
The results of the query are supposed to string together a distribution
channel if there are more than 1 returned.
product1 could return CH
product2 could return CH, DL
product3 could return nothing
The function as it is works properly. I get the anticipated results
when I run a query that calls this user function.
However, I want to write an online app that will allow the user to
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
The problem is - I can't make it work all the time.
If I put in the where clause:
'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
on for each of the 6 possibilities, it'll return ones with a
Distribution channel = DL but only ones which START with a DL. If DL is
the second one in the list, it doesn't return anything.
I have also tried this in the HAVING clause, to see if I can get it to
come out there but can't.
How can I take the distribution channel (in this case DL) and return
records where DL is in any of the 6 product groups?
Any ideas/suggestions appreciated. I'm stumped.
BCBlasting Cap (goober@.christian.net) writes:
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from product1_channels x
> where x.cust_no = @.Customerno and
>...
> The function as it is works properly. I get the anticipated results
> when I run a query that calls this user function.
> However, I want to write an online app that will allow the user to
> select a channel from a dropdown box, and the query will then return any
> data with that distribution channel anywhere in the current or
> previous year's product areas.
> The problem is - I can't make it work all the time.
This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right.
If you are on SQL 2005, there is built-in syntax for this thanks to
the FOR XML construct, here demonstrated by a sample query that I
keep around:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<<This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right. >>
I am using SQL 2000.
The place I'm running the report is just a straight select, with the
functions being called for 6 columns being returned.
select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region
having
-- if they have bought things in any of the last 3 years
(len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
How would you work a cursor into that select?
DECLARE @.AuthorID char(11)
DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.AuthorID
FETCH NEXT FROM c1
INTO @.AuthorID
END
CLOSE c1
DEALLOCATE c1
Also - is there a more efficient way to do this?
Thanks,
BC

> Blasting Cap (goober@.christian.net) writes:
when I run a query that calls this user function.
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
> This is because the correct behaviour of this query is undefined. See
> http://support.microsoft.com/default.aspx?scid=287515.
> If you are on SQL 2000, you will need to run a cursor to get this
> right.
> If you are on SQL 2005, there is built-in syntax for this thanks to
> the FOR XML construct, here demonstrated by a sample query that I
keep around:
> select CustomerID,
> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
> -- strip the last ',' from the list
> from
> Customers c cross apply
> (select convert(nvarchar(30), OrderID) + ',' as [text()]
> from Orders o
> where o.CustomerID = c.CustomerID
> order by o.OrderID
> for xml path('')) as Dummy(OrdIdList)
> go
>
>|||On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:

>I have the following function:
(snip)
Erland is correct - the UDF depends on undocumented behaviour. Even
though it works today, it might break tomorrow.
But Erland apparently missed the question you asked near the end of your
post:

>If I put in the where clause:
>'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
>on for each of the 6 possibilities, it'll return ones with a
>Distribution channel = DL but only ones which START with a DL. If DL is
>the second one in the list, it doesn't return anything.
And yet, I'll let Erland answer that, since he has authored an excellent
page about this problem, and lots of possible solutions:
http://www.sommarskog.se/arrays-in-sql.html
However, in your case there might be a better way. Instead of first
using undocumented techniques to get a comma-seperated list and then
using a second technique to split those to tables, why not rewrite the
AttributesList to a table-valued function?
Hugo Kornelis, SQL Server MVP|||Blasting Cap (goober@.christian.net) writes:
> The place I'm running the report is just a straight select, with the
> functions being called for 6 columns being returned.
>...
> How would you work a cursor into that select?
In your UDF.

> Also - is there a more efficient way to do this?
Yes. Two options:
1) Upgrade to SQL 2005.
2) Do it client-side.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:
> And yet, I'll let Erland answer that, since he has authored an excellent
> page about this problem, and lots of possible solutions:
> http://www.sommarskog.se/arrays-in-sql.html
> However, in your case there might be a better way. Instead of first
> using undocumented techniques to get a comma-seperated list and then
> using a second technique to split those to tables, why not rewrite the
> AttributesList to a table-valued function?
For the WHERE clause why not simply use an EXISTS against the underlying
table:
WHERE EXISTS (SELECT *
FROM product1_channels p
WHERE p.cust_no = a.cust_no
AND p.distr_channel = 'DL')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Blasting Cap (goober@.christian.net) writes:
> select
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region,
> 'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
> 'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
> 'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
> 'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
> 'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
> 'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
> from sales_customer_list as a
> where cust_no in ('R1411600','R6713155')
> group by
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region
> having
> -- if they have bought things in any of the last 3 years
> (len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
> order by
> a.ship_to_sales_grp,
> a.ship_to_sales_rep_cd,
> cust_name
It seems unnecessary to call the UDFs a second time in the HAVING clause.
I'm a little uncertain of the effect of the GROUP BY in this SELECT as
there is no aggregate functions, but assuming that it works the way you
want I retain them. However you could use a derived table:
SELECT cust_no, cust_name, ...
FROM (select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region) AS x
WHERE len(Prod1_curr) > 0 OR
len(Prod2_curr) > 0 OR
len(Prod3_curr) > 0 OR
len(Prod1_prev) > 0 OR
len(Prod2_prev) > 0 OR
len(Prod3_prev) > 0
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
A derived table is a logical temp table within the query, but not materialis
ed,
and SQL Server recast computation order for the best query plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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.