multiple updates to table

Hello all,

I have a parallel process updating a single table from various sources using update table statements using a key column.

I'm afraid the process will fail when an update will occur to a record with the same key simultaneously.

Does anyone have a suggestion how to accomplish this? Is there a way timing the updates in queue?

Thanks.

There is no concurrent update in SQL Server, DML statement are queued and executed one by one (if they are not in a transaction). There are sure scenarios which would lead to concurrency conflicts, but this has to be handled by your frontedn application.

HTH, Jens Suessmeyer.'

http://www.sqlserver2005.de

|||

I assume you have multiple clients pumping data into a single table, right?

The next question is what you mean by key? Like an identity key? If so, that won't happen. SQL Server single threads identity key generation so that no two rows will get the same key.

multiple statements on insert trigger (mssql2000)

Can I have more than 1 statement fire on an insert trigger as I have
illustrated below, or do I use 2 insert triggers?
.....or is there a better solution?
Thanks Soc.
++++++++++++++++++++++++++++++++++++++++
+++++
CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
FOR INSERT
AS
update table1 set column1=column2 where column3='GREEN' and column4 is null
update table1 set column5='peter' where column3='GREEN' where
column5<>'john'
++++++++++++++++++++++++++++++++++++++++
+++++soc
I'm not sure I inderstand you.
Triggers are fired per statement not per Insert. Why do you perform two
updating on the same table within a trigger, can explain what are you trying
to do?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||Can an insert trigger do 2 updates along the lines of the trigger below?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
> null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||On Thu, 7 Apr 2005 12:25:52 +0100, soc wrote:

>Can I have more than 1 statement fire on an insert trigger as I have
>illustrated below, or do I use 2 insert triggers?
>.....or is there a better solution?
>Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
>CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
>FOR INSERT
>AS
>update table1 set column1=column2 where column3='GREEN' and column4 is null
>update table1 set column5='peter' where column3='GREEN' where
>column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
Hi soc,
You can use as many statements as you wish in the code of a trigger. But
you can't use two WHERE clauses in one UPDATE statement (as you do in
your second update).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple SQL Statements in one SQLCommand

I am just wondering if it is possible using SQL Server 2000 to have multiple SQL Statements executed with one sqlComm.ExecuteNonQuery(); call?

Absolutely. You can just string them together. (Or you can separate them with semicolons for clarity.)

Multiple SQL statements in a stored procedure

Hi!

I got 2 stored procedure, proc1 executes proc2,
proc2 does some updates and inserts on different tables ...

proc1:

ALTER PROCEDURE
AS
execute proc2

SELECT * FROM tblFoo
______________________
my problem is, that when executing proc1, I receive the message:

"THE SP executed successfully, but did not return records!"

But I need the resultset from "SELECT * FROM tblFoo" that is executed
at the end of proc1.
I'm not sure, but I think that I solved a similira problem with "set
nocount on", I put it into both SP, but it's still the same ... no
resultset ...

How can I display "SELECT * FROM tblFoo" within a SP, where SQL
statements are executed before?!

Thank you!Err, stupid question but did you check that tblFoo actually has records in
it?

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1117053332.823938.254990@.g14g2000cwa.googlegr oups.com...
> Hi!
> I got 2 stored procedure, proc1 executes proc2,
> proc2 does some updates and inserts on different tables ...
>
> proc1:
> ALTER PROCEDURE
> AS
> execute proc2
> SELECT * FROM tblFoo
> ______________________
> my problem is, that when executing proc1, I receive the message:
> "THE SP executed successfully, but did not return records!"
> But I need the resultset from "SELECT * FROM tblFoo" that is executed
> at the end of proc1.
> I'm not sure, but I think that I solved a similira problem with "set
> nocount on", I put it into both SP, but it's still the same ... no
> resultset ...
> How can I display "SELECT * FROM tblFoo" within a SP, where SQL
> statements are executed before?!
> Thank you!

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.
Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
|||You can use derived queries:
select col1
, col2
from (select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>
|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>
|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye
|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.
Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
|||You can use derived queries:
select col1
, col2
from (select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>
|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>
|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye
|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
--
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple Select Statements in 1 Query

i'm going to guess that there's no way to do this in T-SQL (or any
other SQL implementation), but I figured I'd ask around:
basically, what I'm looking to do is replace this union statement::
SELECT intX
FROM Table1
UNION
SELECT intY
FROM Table1
with a syntactical shortcut:
SELECT intX
SELECT intY
FROM Table1
The need for this shortcut is that my FROM clause is very complex, and
copying it multiple times is messy (and frankly, I'm in no mood to
create a View given the number of non-unique column names in the joined
tables). Anyone have any suggestions?scottstein@.gmail.com wrote:
> i'm going to guess that there's no way to do this in T-SQL (or any
> other SQL implementation), but I figured I'd ask around:
> basically, what I'm looking to do is replace this union statement::
> SELECT intX
> FROM Table1
> UNION
> SELECT intY
> FROM Table1
> with a syntactical shortcut:
> SELECT intX
> SELECT intY
> FROM Table1
> The need for this shortcut is that my FROM clause is very complex, and
> copying it multiple times is messy (and frankly, I'm in no mood to
> create a View given the number of non-unique column names in the joined
> tables). Anyone have any suggestions?
In SQL Server 2005 you can use a CTE. You'll have to assign some unique
column names though:
WITH T1 (intx, inty)
AS
(SELECT ... /* your complex logic here */ )
SELECT intx
FROM T1
UNION
SELECT inty
FROM T1 ;
There may be a better solution by changing the complex part of your
query that you didn't post.
David Portas
SQL Server MVP
--|||if are aren't on 2K5 yet, you can cross join with a sequence table,
like this:
select case when t=1 then expr1 else expr2 end
from(
-- your really complex expression goes here
select 'expr1' expr1, 'expr2' expr2)c
cross join
(select 1 t union all select 2) t
expr1
expr2
(2 row(s) affected)|||Thanks David + Alexander, I appreciate your responses. Unfortunately,
CTE has the same problem as the view -- unique column names. I haven't
even seen 2005 yet, so that isn't an option for now. Again, this
problem isn't unbearable, but it just makes my code a little messy.

Multiple Select statements + Stored Procedure

Hi all,

I have 2 select statements in my Stored Proc.

I want to display the results of each query in my DataGridView.

However, only the data of the last select query is returned.

Why is this?

Thanks.

At a time you can only bind one resultset. if you need both the resultset on your page, you need to have 2 datagrid view.

If you use dataset,

grid1.DataSource = dataset.Tables[0];

grid2.DataSource = dataset.Tables[1];

If you use datareader,

grid1.DataSource = datareader;

datareader.NextResultSet();

grid2.DataSource = datareader;

|||

Ideally I want all data displayed in one DataGridView.

Each SELECT Query returns the exact same Columns but differing data.

|||

As both queries return the same columns, could you use a union clause in your stored proc?

eg

Code Snippet

CREATE PROC proc

AS

SELECT col1, col2

FROM table1

UNION ALL
SELECT col1, col2

FROM table2

This would mean you'd have just one results set.

If its more complicated than that, you could put the results of each query into a temp table/variable and just select from that?

HTH!

|||

May be something like this...

Select 'Table 1' as Source, * From FirstTable

Union ALL

Select 'Table 2' as Source, * From SecondTable

Order By

Source, ....

|||If there any relation between the tables,you can group them in the gridview|||

Thanks!!

UNION ALL works.

multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?

multiple select statement in a stored-proc?

I am using SQL sever 2k and C#.

There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?

What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?

I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.

stored procedure example: NorthWind database:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @.@.ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

C# code example:

//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());

Thanks for your help.

reader.NextResult()

|||it works for SqlDataReader. however XmlTextReader seems don't have this method. anyway I can just use SqlDataReader.
thanks very much.

multiple select in one sp

I was wondering if it's possible to have a stored procedure that hastwo(or more) select statements which you can combine as a single result set.I am not able to use union as my select returns different number of columns.My selects are like this,This example is using only two table but i can have more then 2 tables in situation..

ELECT SUM(col1) AS sumcol1
FROM tbl1
WHERE (ID = @.para1')

SELECT SUM(col1) AS sumcol1, SUM(col2) AS sumcol2, SUM(col3)
AS sumcol3, SUM(col4) AS sumcol1
FROM tbl2
WHERE (id = @.para1).

thanks

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

|||

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

Both can be accessed from C#/VB, but they are separate result sets.

If a single result set is needed, then pad out the two queries with innocuous (sp?) dummy columns and union all them together.

|||

david wendelken:

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

That's right. That's because I thought that was what the questioner wanted. Now I've re-read the original question, I can see it isn't.

Doh.

|||

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

|||

nb123:

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

cast(null as int) as dummy_int
cast(null as varchar(6)) as dummy_varchar6

or just use 0 or '' if zero or an empty space works better for you

|||

Thanks David,it works