return

whats wrong with this SP? I want @.id to contain the row identity of the newly created row as a return value.

ALTER PROCEDURE setCountry
(
@.name varchar( 50 ) = NULL,
@.alt varchar( 24 ) = NULL,
@.code varchar( 3 ) = NULL,
@.id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )
@.id = @.@.identity
RETURN

INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )select@.id = @.@.identity

couple of things :
if you'd like to return this id back to asp.net you need to return it as an OUTPUT parameter..check out BOL for OUTPUT Parameters in stored procs..

also i'd recommend using SCOPE_IDENTITY() rather than @.@.IDENTITY. check out BOL again for the differences between them.

hth|||Thanks - but what is BOL?|||RETURN @.id

??

personally I'd do it this way

SET NOCOUNT ON
-- do insert
...
SELECT @.@.Identity|||BOL = Books On Line - best reference for sql server 2000. Free Download from microsoft.

hth|||Thanks! - I got the BOL acronym too - duh - Books On Line. I will try it now and actually may use SCOPE_IDENTITY() in place of @.@.identity.|||Atrax, I think the "return" method is better as it won't incur a result set. Although I'd use a OUTPUT param rather than return, I prefer to have that indicate some form of "state of the operation".|||Okay - now I can retrieve the result using ExecuteScalar - or DataReader or both?? Because when I run it in VS I dont see the results of the procedures. I mean it adds the row, but I don't see any output in the OUTPUT window.|||if you just need to return the ID you'd be better off using executescalar().

in vb.net

dim userID as integer
...
'open connection
...
userid=sqlcommand.ExecuteScalar()
...
'close connection

and use OUTPUT parameter to return the output form the stored proc...BOL had some samples no how to do it..

hth

retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and insert.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.
I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
sql

retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and inser
t.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>