Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David

Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David

Nesting stored procedure, how to ?

Hello,

I'm trying to use nested stored procedure but can't get it to work.

I've created a stored procedure named 'sousmenu1_parents' which does a simple select, no parameter.

here's what I'm trying to achieve now :

CREATE PROCEDURE SelectAllDroitSpe

-- Add the parameters for the stored procedure here

@.UserId int

AS

EXEC sousmenu1_parents;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT sousmenu1_parents.menu_id1 AS sousmenu1_id

FROM authorisations_speciales LEFT JOIN sousmenu1_parents ON authorisations_speciales.page_id = sousmenu1_parents.menu_id1

WHERE authorisations_speciales.utilisateur_id=@.UserId

END

I get 'sousmenu1_parents isn't a valid object name'

Where am I going wrong ?

Thanks a lot.

Flip:

If your stored procedure does a simple select then transform the procdure into an function.

Dave

|||

Hi,

you can′t directly select from a strored procedure although the procedure return a table with the results, you will have to EXEC the procedure and redirect the output into a table (like a temp table)

-Create a Table first (can be also a temp table)
-

INSERT INTO YourTable
EXEC procedurename

-Use the table in your query.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is a good alternative; the problem with the INSERT INTO ... EXEC PROC method is that you can only use this construct at a single level in a nested series; after that you get execution errors. Don't get me wrong, I use this construct frequently but it is definitely not my first choice when I am dealing with nesting a procedure that "is a simple select". You might be able to make a case for avoiding the function for some other reason but this post is titled "Nesting stored procedure, how to?"

|||

Thanks very much.

I'm now looking up on Functions.

I'm new to Sql Server and I'm migrating an Access database.

I used many nested queries in Access and it looks like converting them is going to be a nightmare.

One thing I don't understand : if I can't "directly select from a stored procedure" what is the point in nesting stored procedure ?

|||

Flip:

Stored procedures will nest; however, it sometims requires judicious use of your alternatives. There are several options to return data from a called stored procedure to a calling stored procedure:

Use of output parameter(s)|||

ignition, I was a big user of access in my past and used many nested access queries to produce result sets. Think of Access queries as Views in SQL.

If in Access you had two queries

select col1, col2 from tablea where some where clause.... (query name is Query1)

select count(*) from Query1 (query name is Query2)

In SQL you could create a View:

Create View vw_Query1 as

select col1, col2 from tableA where....some where clause

Select count(*) from vw_Query 1

Views do not allow you to pass parameters (this is where stored procedures or functions come into play)

Reason you might want to nest stored procedures is to utilize other stored procedures to get a resultset or parameter back to the calling procedure and/or to encapsulate standard code that you cannot execute using functions. Functions have specific limitations (can't do data manipulation (insert, create, drop, can only call extended stored procedures) that you can do in stored procedures.

Create Proc usp_Test1 @.tablename varchar(100)

as

exec usp_AddMissingColumns @.tablename (does an ALTER which you can't do in functions)

exec usp_SetBlanksToNull @.tablename

return

|||

Great, it works a treat using a View !

This all make sense now ...

So for all the simple SELECT queries I had in Access (no parameters) should I use views in Sql Server ? is it in any way faster than a stored procedure, or any other advantage of this ?

Thanks again everyone, and in case I don't come back here before next year : have a lot of fun to end 2006 !

|||

Ignition, I wouldn't automatically change every simple query you have in Access to a view. If you're migrating to SQL Server, it's a good time to analyze the MS Access queries you currently have to determine if there is a continued need for them. I know that I would create queries (in Access) to do some ad hoc things, and then leave them there (laziness on my part!). When I started looking at the stored queries (for migration), I realized that many of them could be combined into a handful of views (some did basically the same thing but with different columns).

Think of a view as a subset (either just certain columns, an aggregation, and/or with specific where clauses) of a table. If you are querying the same data with the same where clause continually, this is probably a good candidate for a view... If you are continually looking at only certain columns within a table, probably a candidate for a view...

Views, stored procedures, and functions all have their place to do certain types of activities. If you lookup these items in Books Online, they will give you some scenarios on when/where and how to use them...

Good luck and happy New Year...

|||Convert your SPs to table-valued functions or even views if there is no parameterization so that you can reuse them easily.

nesting stored procedure

hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:

CREATE PROCEDURE dbo.GetSharesTransactionsbyDates

(
@.Startdate as char(10),
@.Enddate as char(10)

)

AS

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

I get complain that dbo is incorrect syntax

ST.Proc. which I try to call call is basicly a select statement with no parameters:

ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATEYou cannot really do what you are trying to do:

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

You cannot add a WHERE clause like that, and you would need to add EXEC to execute the SP. Can you instead create a SP that accepts two dates as parameters?|||Well I can. The idea is that I first bring all transactions up and then only transactions between the chosen dates so I was trying to avoid repeating the same sql select statement but I can rewrite the original SPROC by adding parameters and additional where clause
Thanks for advice.|||alter the sproc, have it accept paramaters with default values:

create procedure spFoo
@.startDate datetime = '1900-01-01',
@.enddate datetime = '2199-12-31'
AS
--whatever

this way, you have a single sp. existng calls continue to work.|||Aaa, thanks.sql

Nesting SQL Transactions

Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.

First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.

My SQL database is relational and is used to store names, addresses, etc of other companies.

What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:

BEGIN TRANSACTION1

BEGIN TRANSACTION2

INSERT INTO COMPANY TABLE

COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2

Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.

Is what I'm trying to do even possible? Thanks in advance for the help!

BEGIN TRANSACTION

INSERT INTO COMPANY TABLE

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION -OR- ROLLBACK TRANSACTION

dim conn as new sqlconnection("{ConnectString}")
conn.open
dim cmd as new sqlcommand("INSERT INTO Table1(column1) VALUES (@.col1) SELECT SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.col1",sqldbtype.{Whatever}).value={Whatever}
dim MyID as integer=cmd.executescalar
dim cmd2 as new sqlcommand("INSERT INTO Table2(Table1ID,col2) VALUES (@.MyID,@.MyVal)",conn)
cmd2.parameters.add("@.MyID",sqldbtype.integer).value=MyID
cmd2.parameters.add("@.MyVal",sqldbtype.{whatever})
for loop here...
cmd2.parameter("@.MyVal").value={whatever}
cmd2.executenonquery
next
conn.close

Now wrap that in try/catch with a transaction and you are all set.|||

Motley-

Thanks for pointing me in the right direction! At first I wasn't sure exactly how to implement what you gave me with what I already have. After some trial and error and info fromthis site I was able to get my transaction to work exactly as I needed it to.

Thanks again for the help!

Nesting Parameters

I'd like to have a chart with a category "group on" expression like this:

=Fields![Parameters!MyParameter.Value].Value

The above doesn't work. Is it because it can't be done, or because I don't have the right syntax?

Never mind. Found it:

=Fields.Item(Parameters!MyParameter.Value).Value

nesting limit exceeded...but I don't understand why.

I have written a recursive function that generates an XML hierarchy. I have gone through the data being selected and I have verified that the hierarchy is only 19 levels at its deepest. (there are several thousand records involved, and 1 level may have several hundred records). But, I am receiving this error:
"Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Here is the function I have written:

ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(SELECT s.ID As "@.id",
s.TITLE as "@.title",
s.CLASS_ID as "@.clsID",
CASE WHEN s.PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(s.ID)
END
FROM SCENE s
WHERE s.PARENT_ID = @.SceneID
FOR XML PATH('Scene'), TYPE)
END

Does anyone have any suggestions as to why I may be getting this error, or how to debug for it or work around it?

Thank you for any advice you can give.

Do you have any records in which ID = PARENT_ID?

Run SELECT ID, PARENT_ID FROM SCENE WHERE ID = PARENT_ID and see.

If so, you might want to consider adding a check constraint to the table that would forbid such a circumstance. Another alternative might be to exit the function if the function detects ID = PARENT_ID.

|||

When I mockup with:

create table dbo.scene
( id integer,
parent_id integer,
class_id integer,
title varchar(20)
)
go

insert into dbo.scene values (1, null, 1, 'This is a test')
insert into dbo.scene values (2, 1, 1, 'Record #2')
insert into dbo.scene values (3, 2, 1, 'Record #3')

and run:

select dbo.fn_WPMTREE (1) as [the Scene]

I get:

-- the Scene
-- -
-- <Scene id="2" title="Record #2" clsID="1"><Scene id="3" title="Record #3" clsID="1" /></Scene>

Is this what you expect?

|||Absolutely brilliant.

I had a scene that had a PARENT_ID = ID. And, that caused the infinite loop. I thought it was a loop being created somewhere, but I thought it was of the type Scene1.Parent_ID = Scene2.ID; Scene2.Parent_ID = Scene1.ID.
But, it was even more direct than that.

Thanks so much!
Sincerely.
roger

nesting DTS packages and returning errors

I've noticed that when I have a sql server agent job that calls a DTS
package, and that package in turn calls another DTS package, when the
inner package fails, it does not return an error to the outer package
and hence the sql server agent job indicates success even though the
inner dts package failed.
Is there a way to get the sql server agent job to indicate a failure
when the inner dts package fails?
Thanks in advance.
Walter
waltmallon@.yahoo.comunfortunately you will have to either write an extra step into the
"called/children" packages that returns a success or failure, I have done
this in the past with the RAISERROR WITH LOG for TSQL and and the err
collection in an ActiveX script this way your package can return a status to
the parent DTS packag, I am sure Darren Green (sqldts.com) will/may have
better ideas
--
Olu Adedeji
"Walter Mallon" <waltmallon@.yahoo.com> wrote in message
news:28db1398.0402190854.46eedc55@.posting.google.com...
> I've noticed that when I have a sql server agent job that calls a DTS
> package, and that package in turn calls another DTS package, when the
> inner package fails, it does not return an error to the outer package
> and hence the sql server agent job indicates success even though the
> inner dts package failed.
> Is there a way to get the sql server agent job to indicate a failure
> when the inner dts package fails?
> Thanks in advance.
> Walter
> waltmallon@.yahoo.com

nesting DTS packages and returning errors

I've noticed that when I have a sql server agent job that calls a DTS
package, and that package in turn calls another DTS package, when the
inner package fails, it does not return an error to the outer package
and hence the sql server agent job indicates success even though the
inner dts package failed.
Is there a way to get the sql server agent job to indicate a failure
when the inner dts package fails?
Thanks in advance.
Walter
waltmallon@.yahoo.comunfortunately you will have to either write an extra step into the
"called/children" packages that returns a success or failure, I have done
this in the past with the RAISERROR WITH LOG for TSQL and and the err
collection in an ActiveX script this way your package can return a status to
the parent DTS packag, I am sure Darren Green (sqldts.com) will/may have
better ideas
--
Olu Adedeji
"Walter Mallon" <waltmallon@.yahoo.com> wrote in message
news:28db1398.0402190854.46eedc55@.posting.google.com...
> I've noticed that when I have a sql server agent job that calls a DTS
> package, and that package in turn calls another DTS package, when the
> inner package fails, it does not return an error to the outer package
> and hence the sql server agent job indicates success even though the
> inner dts package failed.
> Is there a way to get the sql server agent job to indicate a failure
> when the inner dts package fails?
> Thanks in advance.
> Walter
> waltmallon@.yahoo.comsql

Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
Tom
What are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom
|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags are
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly to
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper elements.
> Unless you need to provide group specific properties, I find these elements
> useless and they actually make processing of the documents more expensive in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>
|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote:

Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
TomWhat are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags ar
e
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly t
o
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information
.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper element
s.
> Unless you need to provide group specific properties, I find these element
s
> useless and they actually make processing of the documents more expensive
in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote:
>

nesting cursors

Hi, i have to nest 2 cursors together and frankly i'm lost.

Here's what I have so far

CREATE PROCEDURE SeeAllColumns
AS
SET NOCOUNT ON
DECLARE @.strMessage VARCHAR(100)
DECLARE @.strColumn VARCHAR(100)
DECLARE @.strTable VARCHAR(100)
DECLARE @.strCommand VARCHAR(250)

SELECT @.strMessage = 'SELECT ALL TABLES'
PRINT @.strMessage
DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

OPEN crsTables
FETCH NEXT FROM crsTables INTO @.strTable
WHILE (@.@.FETCH_STATUS = 0) BEGIN

SELECT @.strMessage = 'SELECT ALL COLUMNS'
PRINT @.strMessage

SELECT @.strCommand = ' SELECT ' + @.strColumn + ' FROM ' + @.strTable

EXECUTE (@.strCommand)

FETCH NEXT FROM crsTables INTO @.strTable

END

DEALLOCATE crsTables

PRINT 'DONE'

What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
Can someone help me?

Thanks!Frankly ... you lost me somewhere in the middle of the proc ... could you plz explain what you are trying to do here ...
as far as it seems to me .. this proc will generate as many resultsets as the total no of columns in the database

DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

should probably be

DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
xtype = 'U'

if you are trying to select all user tables|||declare @.tbl sysname
declare @.clm sysname

declare tbls cursor local fast_forward for
select [TABLE_NAME]
from [INFORMATION_SCHEMA].[TABLES]
where [TABLE_TYPE] = 'BASE TABLE'

open tbls

fetch next from tbls into @.tbl
while @.@.fetch_status = 0
begin
declare cols cursor local fast_forward for
select [COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_NAME] = @.tbl

open cols

fetch next from cols into @.clm
while @.@.fetch_status = 0
begin
print @.tbl + ':' + @.clm

fetch next from cols into @.clm
end

close cols
deallocate cols

fetch next from tbls into @.tbl
end

close tbls
deallocate tbls|||you can just get by with 1 cursor if you declare it against this statement (alternatively you can use information_schema.columns view):

select cast(object_name(id) as char(128)), colid, [name] from syscolumns
where objectproperty(id, 'istable')=1 and objectproperty(id, 'ismsshipped')=0
order by id, colid

Nesting a SP within another SP?

I have a stored procedure that calls some UDF User Defined Functions,
the purpose of which is to create row strings out of numerous column
strings for matching uniqueIDs.

The problem is I need to join that SP with some other tables.

The SP I have reads something like:

mySPName
@.myUserID int
SELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningString
FROM myTEMPTableName
GROUP BY myUniqueID
WHERE myTEMPTableName.UserID = @.myUserID

I need to join that result with myTableName on myUniqueID such as:
Select myTableName.myField1, myTableName.myField2,
mySPName.myRunningString
From ...
-- joining myTableName.myUniqueID = mySPName.myUniqueID

Can this be done?
The reason I don't just do it with a View instead of an SP is that I
have that parameter that must be passed to filter the records in
myTEMPTableName.

Any help is appreciated.
lq

oh...
the UDF looks like:

Create Function dbo.fn_myFunctionName(@.myUniqueID as int) returns
nvarchar(500)
AS
BEGIN
DECLARE @.ret_value nvarchar(500)
SET @.ret_value=''
SELECT @.ret_value=@.ret_value + ';' + myString
FROM myTEMPTableName
WHERE
myUniqueID =@.myUniqueID
RETURN RIGHT(@.ret_value,Len(@.ret_value)-2)
ENDlaurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401272245.449a0756@.posting.google.com>...
> I have a stored procedure that calls some UDF User Defined Functions,
> the purpose of which is to create row strings out of numerous column
> strings for matching uniqueIDs.
> The problem is I need to join that SP with some other tables.
> The SP I have reads something like:
> mySPName
> @.myUserID int
> SELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningString
> FROM myTEMPTableName
> GROUP BY myUniqueID
> WHERE myTEMPTableName.UserID = @.myUserID
> I need to join that result with myTableName on myUniqueID such as:
> Select myTableName.myField1, myTableName.myField2,
> mySPName.myRunningString
> From ...
> -- joining myTableName.myUniqueID = mySPName.myUniqueID
> Can this be done?
> The reason I don't just do it with a View instead of an SP is that I
> have that parameter that must be passed to filter the records in
> myTEMPTableName.
> Any help is appreciated.
> lq
> oh...
> the UDF looks like:
> Create Function dbo.fn_myFunctionName(@.myUniqueID as int) returns
> nvarchar(500)
> AS
> BEGIN
> DECLARE @.ret_value nvarchar(500)
> SET @.ret_value=''
> SELECT @.ret_value=@.ret_value + ';' + myString
> FROM myTEMPTableName
> WHERE
> myUniqueID =@.myUniqueID
> RETURN RIGHT(@.ret_value,Len(@.ret_value)-2)
> END

There are some options described here:

http://www.sommarskog.se/share_data.html

From your description, rewriting the stored procedure as a
table-valued UDF sounds like it should be possible.

Simon

Nesting a SP inside a Query

This does not work:
select * from (exec sp_lock) as ex
I want to process sp_lock in code as a table. How can I do this?
SeanSean
Create a table that will contain all columns from sp_lock stored procedure
and the perform
insert into #t exec sp_lock
select * from #t
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:ePz9bgLUFHA.628@.tk2msftngp13.phx.gbl...
> This does not work:
> select * from (exec sp_lock) as ex
> I want to process sp_lock in code as a table. How can I do this?
> Sean
>|||Thanks but...
insert into #t exec sp_lock
gives...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#t'.|||ignore than last message... I'm doing 10 things at once
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:OD$E9WMUFHA.2540@.tk2msftngp13.phx.gbl...
> Thanks but...
> insert into #t exec sp_lock
> gives...
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#t'.
>|||You have to create the temp table first. Try this:
Create Table #Locks
(
spid int
, dbid int
, objId int
, indid int
, type nvarchar(10)
, resource ntext
, mode nvarchar(2)
, status nvarchar(25)
)
Insert #Locks
Exec sp_lock
Granted, I'm using the Force in determining the data types and sizes for the
various columns.
Thomas
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:ePz9bgLUFHA.628@.tk2msftngp13.phx.gbl...
> This does not work:
> select * from (exec sp_lock) as ex
> I want to process sp_lock in code as a table. How can I do this?
> Sean
>sql

Nested Table

I have a table nested inside another table. Within the nesting, I want my
data to show me the detail, and not do a grouping. Unfortunately, the inner
table wants to keep doing an aggregate of the data, but I want to show the
detail. So I have a part number that has multiple customers being impacted
by supply limitations, I want to pull the part number with all of its data as
a single record, and then have a nested group that shows all the customer
name, like this:
AAAA
Acme Inc.
Acme Botanical
Acme Logistics
BBBB
Acme Inc.
Acme Environmental
When I add fields to the nested table, I keep having aggregate functions
appear, and if I don't use the aggregates, and specify scope, the data
element is acting like it is pulling from the parent table.
Thanks for any info.
ShaneI have a similar problem. I have a line of header information, then
multiple lines of detail, all coming from the same data source. RS
documentation says you can nest data regions, but I haven't found any
examples. I tried nesting groups and defined the header group as the parent
of the detail group. But, like Shane, I found that the RS insisted on
treating the detail lines as aggregates.
Is there example somewhere of a nested group that produces output in the
form that Shane shows below?
Thanks
Al
Shane Castle" <ShaneCastle@.discussions.microsoft.com> wrote in message
news:DB333B89-E614-48FC-943B-73163C9B735B@.microsoft.com...
> I have a table nested inside another table. Within the nesting, I want my
> data to show me the detail, and not do a grouping. Unfortunately, the
inner
> table wants to keep doing an aggregate of the data, but I want to show the
> detail. So I have a part number that has multiple customers being
impacted
> by supply limitations, I want to pull the part number with all of its data
as
> a single record, and then have a nested group that shows all the customer
> name, like this:
> AAAA
> Acme Inc.
> Acme Botanical
> Acme Logistics
> BBBB
> Acme Inc.
> Acme Environmental
> When I add fields to the nested table, I keep having aggregate functions
> appear, and if I don't use the aggregates, and specify scope, the data
> element is acting like it is pulling from the parent table.
> Thanks for any info.
> Shane|||My work around for this is to have your first report get all the
parts. You would add just one table. The first detail row you place
the part fields. In your second detail row place a subreport.
Make another report for companies. place only a table into this report
and have company fields in a single row. You might have to mess with
page border width stuff.
Use the part id or whatever and pass that into the subreport.
What will happen is that for each part. You'll print your part fields,
then it'll go to the second detail line and run the subreport which
gets all your companies for that part id.
Maybe not the best way to do this, but it's very simple, easy, and
clean.
Shane Castle <ShaneCastle@.discussions.microsoft.com> wrote in message news:<DB333B89-E614-48FC-943B-73163C9B735B@.microsoft.com>...
> I have a table nested inside another table. Within the nesting, I want my
> data to show me the detail, and not do a grouping. Unfortunately, the inner
> table wants to keep doing an aggregate of the data, but I want to show the
> detail. So I have a part number that has multiple customers being impacted
> by supply limitations, I want to pull the part number with all of its data as
> a single record, and then have a nested group that shows all the customer
> name, like this:
> AAAA
> Acme Inc.
> Acme Botanical
> Acme Logistics
> BBBB
> Acme Inc.
> Acme Environmental
> When I add fields to the nested table, I keep having aggregate functions
> appear, and if I don't use the aggregates, and specify scope, the data
> element is acting like it is pulling from the parent table.
> Thanks for any info.
> Shane

nested stored procs

Hi Guys, is there a way of nesting a stored procedure within another...i dont mean calling one within another...more like

creating one within another e.g

Create proc TEST1

as

Create proc TEST2

as

select 'this is inner proc'

where by running test one creates test2

i tried this and got an error:

Msg 156, Level 15, State 1, Procedure TEST1, Line 3

Incorrect syntax near the keyword 'proc'.

is this possible if so how, cos i am trying to create a master Proc that creates a DB, and Tables and store procs, hence i want to use it like a Batch file or script.

To nest stored procedures, you must create them separately and have one proc call the other -- maybe something like this:

create procedure A

as

print 'This is procedure a.'

go

create procedure B

as

exec A

go

exec B

-- - Output: -
-- This is procedure a.

If you are trying to create a procedure that creates other procedures, you will need to take a different approach. Stored procedures cannot directly create other database objects.

|||

You cannot create a stored procedure from within a stored procedure.

You can create a T-SQL script file, and then execute that script file from the command line using OSQL.exe or SQLCommand.exe

Refer to Books Online for OSQL utility, or SQLCommand.

|||Thanks Kent yeah i do now this form of nesting but its not suitable as i am not calling a proc from a proc|||

thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

ta.

|||

One thing to remember is that when using scripts, each use of 'GO' will clear all variables. You may have to re-establish them for the next object.

Sometimes, from the stored procedure, I have loaded a [Name-Value] table with input parameter values that will be used as variables in the script file. The values can be pulled whenever needed.

|||

>> thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

You would have to create the SPs in the newly created database so your SP create statements couldn't be in-line anyway.

Usually I do this via osql or just concatenated scripts.

|||

One other option would be to use dynamic sql to create the procedure. The code below in option 1 creates a permanent procedure that is then executed. The code in option 2 creates a temporary stored procedure that is then executed. Temporary procedures are nice when the task you are executing doesn't need to be permanent. HTH.

-Chris

--Option 1

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE myProc2 AS select getdate()')

exec myProc2

END;

GO

EXEC myProc1

--Option 2

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE #myProc2 AS select getdate()')

exec #myProc2

END;

GO

EXEC myProc1

|||

Why Not..! Yes you can create... But it is bit different....

You have to use the procedure numbers...

The main storedproc will be visible to world rest will be hidden.

You can call them from your main proc / externally..

Example:

Code Snippet

Create proc MyGroupedProc

(

@.Param as int

)

as

Begin

Exec MyGroupedProc;2 @.Param

End

Go

Create Proc MyGroupedProc;2

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [2]'

Select @.Param as [@. 2]

Exec MyGroupedProc;3 @.param

End

Go

Create Proc MyGroupedProc;3

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [3]'

Select @.Param as [@. 3]

End

Go

ExecMyGroupedProc 1

Select * from Sysobjects Where Name Like 'MyGroupedProc%' --It only list the Main Proc not other 2

ExecMyGroupedProc;2 1 --You can execute the hidden stored proc directly

Nested Rollback/Commits Question

Hello all
I have a question regarding Rollbacks and Committs when
you are nesting stored procedures. My problem is that i
continue to get the following error.
Transaction count after EXECUTE indicates that a COMMIT
or ROLLBACK TRANSACTION statement is missing. Previous
count = 0, current count = 1
I am not sure what i am doing wrong. Any help would be
appriciated. Here is a sample SP that i may be nesting
in a larger SP.
---
CREATE PROCEDURE usp_Update_Client_Biography
@.Client_id as integer,
@.Biography as varchar(1000)
AS
SET NOCOUNT ON
DECLARE @.intErrorCode integer
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode = 0
BEGIN TRANSACTION
IF @.intErrorCode = 0
BEGIN
UPDATE Client_Biography
SET biography = @.Biography
WHERE biz_association_id = @.Client_id
SELECT @.intErrorCode = @.@.Error
END
IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @.intErrorCode
THANKS
BAPerhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||When you rollback a transaction it will rollback all the transactions that
it is nested in. So you won't have any open transaction by the time you get
to your errorhandler. You can catch this by changing your code (at the end)
to:
IF @.@.TRANCOUNT > 0
IF @.intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
You might want to have a look at the SET XACT_ABORT option. If you have it
on any error will cause the transaction to roll back and prevent any further
code in the batch to execute. The disadvantage of this of course is that you
can't do any custom error handling, the advantage is that you don't have to
do any custom error handling.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||This is a multi-part message in MIME format.
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
declare
@.trancount int
set @.trancount =3D @.@.trancount
if @.trancount =3D 0
begin tran MyTran
else
save tran MyTran
-- do the work, check errors
if @.@.ERROR =3D 0
begin
if @.trancount =3D 0
commit tran
end
else
rollback tran MyTran
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BA Baracus" <JCoxEUP@.hotmail.com> wrote in message =news:06ea01c3810f$02b36670$a001280a@.phx.gbl...
Thanks for the help!
Is there any way you can show me some T-SQL that will show me your suggestion? If i understand you correctly, i should check the transaction count before beginning the transaction? This has been an ongoing issue with me and i am very appriciative of your help!
thanks BA
>--Original Message--
>Perhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
>trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=3Ddjq&as=20
ugroup=3Dmicrosoft.public.sqlserver
>
>"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
>> Hello all
>> I have a question regarding Rollbacks and Committs when
>> you are nesting stored procedures. My problem is that i
>> continue to get the following error.
>> Transaction count after EXECUTE indicates that a COMMIT
>> or ROLLBACK TRANSACTION statement is missing. Previous
>> count =3D 0, current count =3D 1
>> I am not sure what i am doing wrong. Any help would be
>> appriciated. Here is a sample SP that i may be nesting
>> in a larger SP.
>> ---
>> CREATE PROCEDURE usp_Update_Client_Biography
>> @.Client_id as integer,
>> @.Biography as varchar(1000)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.intErrorCode integer
>> SELECT @.intErrorCode =3D @.@.Error
>> IF @.intErrorCode =3D 0
>> BEGIN TRANSACTION
>> IF @.intErrorCode =3D 0
>> BEGIN
>> UPDATE Client_Biography
>> SET biography =3D @.Biography
>> WHERE biz_association_id =3D @.Client_id
>> SELECT @.intErrorCode =3D @.@.Error
>> END
>>
>> IF @.intErrorCode =3D 0 AND @.@.TRANCOUNT > 0
>> COMMIT TRANSACTION
>> ELSE
>> ROLLBACK TRANSACTION
>>
>> RETURN @.intErrorCode
>>
>> THANKS
>> BA
>
>.
>
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
declare
@.trancount int
set @.trancount =3D =@.@.trancount
if @.trancount =3D =0
begin tran MyTran
else
save tran MyTran
-- do the work, check =errors
if @.@.ERROR =3D 0
begin
if =@.trancount =3D 0
= commit tran
end
else
rollback =tran MyTran
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"BA Baracus" wrote in =message news:06ea01c3810f$02=b36670$a001280a@.phx.gbl...Thanks for the help!Is there any way you can show me some T-SQL that =will show me your suggestion? If i understand you correctly, i =should check the transaction count before beginning the transaction? =This has been an ongoing issue with me and i am very appriciative of your help!thanks BA >--Original Message-->Perhaps @.@.TRANCOUNT is 1 when you enter the =procedure? If you exit the proc with some other>trancount then when =entering, you get such error. And remember that ROLLBACK exits the transaction>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.>>-- >Tibor Karaszi, SQL Server MVP>Archive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver>>>"B.A. =Baracus" =wrote in message news:065201c38109$f1=f0e150$a001280a@.phx.gbl...> Hello all>> I have a question regarding Rollbacks =and Committs when> you are nesting stored procedures. My =problem is that i> continue to get the following error.>> Transaction count after EXECUTE indicates =that a COMMIT> or ROLLBACK TRANSACTION statement is missing. Previous> count =3D 0, current count =3D =1>> I am not sure what i am doing wrong. Any help would be> appriciated. Here is a sample SP that i may be nesting> =in a larger SP.>> --->>= CREATE PROCEDURE usp_Update_Client_Biography> @.Client_id as integer,> @.Biography as varchar(1000)>> =AS>> SET NOCOUNT ON>> =DECLARE @.intErrorCode integer> SELECT @.intErrorCode =3D @.@.Error>> IF @.intErrorCode =3D 0> BEGIN =TRANSACTION>> IF @.intErrorCode =3D 0> BEGIN> UPDATE Client_Biography> SET biography =3D @.Biography> WHERE biz_association_id =3D @.Client_id>> SELECT @.intErrorCode =3D =@.@.Error> END>>> IF @.intErrorCode =3D 0 AND =@.@.TRANCOUNT > 0> COMMIT TRANSACTION> ELSE> =ROLLBACK TRANSACTION>>> RETURN @.intErrorCode>>> THANKS> BA>>>.>

---

--=_NextPart_000_009C_01C380EE.56F75F50--|||Hi.
I'm currently reading SQL Server 2000 Stored Proc 7 XML Programing,
2nd ed. By Dejan Sunderic - ISBN 0-07-222896-2
So far it has been an excellent book IMHO though I'm only on chapter 9
I have just read the chapter dealing with errorcodes & transactions,
using the information in the book I've got a template that I've
started to use for all my stored procs which might be of some use to
you.
What I would like is if any Guru out there can comment on the template
and let me know before I go to far with it if its as good as what the
book seems to be saying it is.
Thanks & HTH.
Al
/*
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Function : usp__Template
** Language : T-SQL
** Description : This is a template for all stored procs
** :
** Input : None
** Returns : None
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Ver Date Description of modification
**
---
** 1.0 date here text here
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
*/
CREATE PROCEDURE [dbo].[usp__Template]
-- @.intInValuesHere INT ,
-- @.intInValuesMoreHere INT ,
-- @.intOutputParam INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.intErrorCode INT , -- to store the errorcodes
@.intTranCountOnEntry INT , -- Store transaction count
@.intAnotherVariable INT
SELECT @.intErrorCode = @.@.Error ,
@.intTranCountOnEntry = @.@.TranCount ,
@.intAnotherVariable = 0
if @.@.TranCount = 0
BEGIN TRANSACTION
-- T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
-- more T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
IF @.@.TranCount > @.intTranCountOnEntry
COMMIT TRANSACTION
RETURN 0
/****************************************
******* ERROR HANDLER CODE BELOW ********
*****************************************/
ERROR_HANDLER:
IF @.@.TranCount > @.intTranCountOnEntry
ROLLBACK TRANSACTION
RAISERROR ('Some Error Message here...',16,1)
RETURN @.intErrorCode
GO
On Mon, 22 Sep 2003 06:03:41 -0700, "B.A. Baracus"
<JCoxEUP@.hotmail.com> wrote:
>Hello all
>I have a question regarding Rollbacks and Committs when
>you are nesting stored procedures. My problem is that i
>continue to get the following error.
>Transaction count after EXECUTE indicates that a COMMIT
>or ROLLBACK TRANSACTION statement is missing. Previous
>count = 0, current count = 1
>I am not sure what i am doing wrong. Any help would be
>appriciated. Here is a sample SP that i may be nesting
>in a larger SP.
>---
>CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
>AS
>SET NOCOUNT ON
>DECLARE @.intErrorCode integer
>SELECT @.intErrorCode = @.@.Error
>IF @.intErrorCode = 0
> BEGIN TRANSACTION
>IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
>IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
>ELSE
> ROLLBACK TRANSACTION
>
>RETURN @.intErrorCode
>
>THANKS
>BA