Hi
I'm tring to write a statement to analyse what orders were open on the first
day of each month from a system and return one data set with the months
listed and all the orders open during that month. eg.
Mon Order_No
Jan 001
Jan 002
Jan 003
Feb 002
Feb 003
Feb 004
The orders all have an open and closed date, so I want to check for each
month if the first of the month falls between the open and closed date of
each order.
I've set up a dummy database for testing - what I'd like to know is whether
CASE statements be made to match more than once :
SELECT MyNewField =
CASE
WHEN data1 = 1 THEN 'Is One'
WHEN data1 > 1 then 'Not One'
end,
data2
FROM APW_Test
my table is as follows
data1
1
2
3
4
So I would hope to see one result for the number 1 (Is One) and two results
for the remaining numbers because they match both case statements. However,
CASE seems to match the first statement and then stop for each record.
Is there a way I can achieve the result I want fairly simply ?
Thanks in advance.
AndrewHi ... I made a mistake in my logic. What I meant was
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 0 then 'Greater Than Zero'
so I expect 1 to appear twice. All else the same.
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||Andrew
you can simply use procedure/function to use if condition.
However post DDL,Sample data to help you better
Regards
R.D
"Andrew Webb" wrote:
> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
>
> "Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
> news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>|||On Fri, 16 Sep 2005 08:39:06 +0100, "Andrew Webb"
<andrew.webb@.eme-med.co.uk> wrote:
> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
CASE returns only the first match.|||Andrew,
It sounds to me like you need a JOIN operation, not a CASE expression.
Post the CREATE TABLE and INSERT statements for some specific
data if you want a more careful answer, but this might be close:
select data1, DisplayAnswer
from T join (
select 'equal 1' as TestCondition, 'Is One' as DisplayAnswer
union all
select 'above 1', 'Not One'
) C
on (
TestCondition = 'equal 1' and data1 = 1
) or (
TestCondition = 'above 1' and data1 > 1
)
If you select only from your 4-row table, with no other table
joined in, you cannot obtain a result that contains any row
more than once.
Steve Kass
Drew University
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||On Fri, 16 Sep 2005 08:30:00 +0100, Andrew Webb wrote:
>Hi
>I'm tring to write a statement to analyse what orders were open on the firs
t
>day of each month from a system and return one data set with the months
>listed and all the orders open during that month. eg.
>Mon Order_No
>Jan 001
>Jan 002
>Jan 003
>Feb 002
>Feb 003
>Feb 004
>The orders all have an open and closed date, so I want to check for each
>month if the first of the month falls between the open and closed date of
>each order.
Hi Andrew,
You could use a calendar table or a table of integers to get this. I'll
give an example with a table of integers that's made up "on the fly".
You can expand it as needed, or make a real table of integers (as
explained on http://www.aspfaq.com/show.asp?id=2516).
DECLARE @.StartDate smalldatetime
,@.EndDate smalldatetime
SET @.StartDate = '20050101' -- Should be first of the month
SET @.EndDate = '20051201'
SELECT DATEADD(month, Numbers.n, @.StartDate) AS Mon,
Orders.Order_No
FROM Orders
INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15) AS Numbers(n)
WHERE Orders.OpenDate < DATEADD(month, Numbers.n, @.StartDate)
AND Orders.CloseDate > DATEADD(month, Numbers.n, @.StartDate)
AND DATEADD(month, Numbers.n, @.StartDate) <= @.EndDate
ORDER BY Numbers.n, Orders.Order_No
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label return. Show all posts
Showing posts with label return. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
can an INSERT statement RETURN a value?
I only know a little bit of SQL for Access databases, so sorry if this is a silly question!
Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)
Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?
Thanks in advance!create proc <blah-blah> (@.blah1 varchar(blah), @.blah2 varchar(blah-blah) )
as
declare @.RetVal int, @.Error int
begin tran
insert <blah> (blah1, blah2) values (@.blah1, @.blah2)
select @.Error = @.@.error, @.RetVal = scope_identity()
if @.Error != 0 begin
raiserror ('failed to insert into blah', 16, 1)
rollback tran
return 1
end
commit tran
select NewIdentityValue = @.RetVal
return 0
OR, you can define @.RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.|||...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.
blindman|||it actually appears that the answer is YES. explanation blindman?|||We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...
ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.|||It sounds like the answer is really our favorite "yes and no". Yes, @.@.error acts as a sort of return value, but in the strict definition of a return value, you can not have
exec @.retvalue = "insert into table values (...)" Does that explain it better?|||Yes, No?
You can get Id back without stored procedure!
create table test(id int identity primary key
,code varchar(10))
go
create trigger ins_test on test
for insert
as
select id from inserted
go
insert test values('A')
go
id
----
1
Just get recordset from command object...|||True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)|||Originally posted by Seppuku
True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)
But it is possible... ;)|||you can do it even without a trigger, but from a command object.
create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
go
Just assign the following command to it:
insert test1 (f2) values ('A') select RetVal=@.@.identity -- or scope_identity() for sql2k
Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)
Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?
Thanks in advance!create proc <blah-blah> (@.blah1 varchar(blah), @.blah2 varchar(blah-blah) )
as
declare @.RetVal int, @.Error int
begin tran
insert <blah> (blah1, blah2) values (@.blah1, @.blah2)
select @.Error = @.@.error, @.RetVal = scope_identity()
if @.Error != 0 begin
raiserror ('failed to insert into blah', 16, 1)
rollback tran
return 1
end
commit tran
select NewIdentityValue = @.RetVal
return 0
OR, you can define @.RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.|||...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.
blindman|||it actually appears that the answer is YES. explanation blindman?|||We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...
ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.|||It sounds like the answer is really our favorite "yes and no". Yes, @.@.error acts as a sort of return value, but in the strict definition of a return value, you can not have
exec @.retvalue = "insert into table values (...)" Does that explain it better?|||Yes, No?
You can get Id back without stored procedure!
create table test(id int identity primary key
,code varchar(10))
go
create trigger ins_test on test
for insert
as
select id from inserted
go
insert test values('A')
go
id
----
1
Just get recordset from command object...|||True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)|||Originally posted by Seppuku
True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)
But it is possible... ;)|||you can do it even without a trigger, but from a command object.
create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
go
Just assign the following command to it:
insert test1 (f2) values ('A') select RetVal=@.@.identity -- or scope_identity() for sql2k
Sunday, February 19, 2012
Can a stored procedure open excel and call a macro? or vice versa
I googled it but found nothing.
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set b
ut
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set b
ut
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks
Can a stored procedure open excel and call a macro? or vice versa
I googled it but found nothing.
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done :)
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set but
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done :)
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set but
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks
Can a sql 2005 function return more than a variable
Hi,
I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :
SELECT *, dbo.fn_GetDistance (...) AS Distance
In this function, i have a Latitude and i want this Latitude to be also returned.
It is possible or a function can return only one variable?
If it is possible, what's the syntax of it?
Thanks in advance
You can return a table, something like this:
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEFUNCTION LongLatDistance
(-- Add the parameters for the function here
@.ZipCode1varchar(10),@.ZipCode2varchar(10))
RETURNS @.ResultTableTABLE(LongitudeDecimal(18,6),Latitudedecimal(18,6),Distancedecimal(18,6))
AS
BEGIN
INSERTINTO @.ResultTable (Longitude,Latitude,Distance) SELECT FieldsFROM TableName
RETURN
END
GO
|||And i call it how from the stored procedure?
|||SELECT Longitude,Latitude,Distance FROM dbo.LongLatDistance('90210',92630')
I wasnt clear on what exactly else you wanted to return. The sample is only returning the coords of one zip code, you would modify it obviously for your situation.
Thursday, February 16, 2012
Can a SP return a table? If so, how?
Is it possible for a stored procedure to return a table? If so, can you
give me an example?
TIA,
Larry WoodsTable variables can NOT be passed as parameters... However you may
1. insert rows into another table which can be used by someone else
2. insert into atable exec thestoredproc
3. select * from openquery(linkedservername, 'exec thestoredproc') as a
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.lwoods.com> wrote in message
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||--yes!!!
--example:
---
use northwind
go
create procedure dbo.spSelectTable
AS
select
EmployeeID
,LastName
,FirstName
from dbo.Employees
go
exec dbo.spSelectTable
go
Joao Mossmann
"Larry Woods" <larry@.lwoods.com> escreveu na mensagem
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||Another option could be a SQL 2000 function, which can
return a table variable... Bruce
create function dbo.funcName
(@.inputParm Varchar(20))
returns @.TableX TABLE (TableID integer,
TableLevelNo integer, TableName sysname,
PK_TableName sysname NULL)
>--Original Message--
>Is it possible for a stored procedure to return a table?
If so, can you
>give me an example?
>TIA,
>Larry Woods
>
>.
>|||Thanks to all. Good answers.
I ALWAYS get good professional answeres here!
Larry Woods
"Larry Woods" <larry@.lwoods.com> wrote in message
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||Another problem:
I have multiple records that I would like updated as a group so that I can
take advantage of the transaction capabilities of SP's. What I had
envisioned was passing a table into the SP then let the SP work on it.
Assuming that this is the CORRECT way to attack this, can I create a
temporary table, then pass the name of the table into the SP?
Does this make sense?
TIA,
Larry Woods
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eGwwDZwlDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Table variables can NOT be passed as parameters... However you may
> 1. insert rows into another table which can be used by someone else
> 2. insert into atable exec thestoredproc
> 3. select * from openquery(linkedservername, 'exec thestoredproc') as a
> Hope this helps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > Is it possible for a stored procedure to return a table? If so, can you
> > give me an example?
> >
> > TIA,
> >
> > Larry Woods
> >
> >
> >
>|||If the calling environment is just another piece of SQL code or another
stored proc, perhaps a temporary table (probably global) would do the trick?
Otherwise, a where clause for use in dynamic SQL or a list of PK values as a
string would be the other two solutions that come to mind.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e2Kz3EloDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Another problem:
> I have multiple records that I would like updated as a group so that I can
> take advantage of the transaction capabilities of SP's. What I had
> envisioned was passing a table into the SP then let the SP work on it.
> Assuming that this is the CORRECT way to attack this, can I create a
> temporary table, then pass the name of the table into the SP?
> Does this make sense?
> TIA,
> Larry Woods
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:eGwwDZwlDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Table variables can NOT be passed as parameters... However you may
> > 1. insert rows into another table which can be used by someone else
> > 2. insert into atable exec thestoredproc
> > 3. select * from openquery(linkedservername, 'exec thestoredproc') as a
> >
> > Hope this helps.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > > Is it possible for a stored procedure to return a table? If so, can
you
> > > give me an example?
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> > >
> >
> >
>
give me an example?
TIA,
Larry WoodsTable variables can NOT be passed as parameters... However you may
1. insert rows into another table which can be used by someone else
2. insert into atable exec thestoredproc
3. select * from openquery(linkedservername, 'exec thestoredproc') as a
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.lwoods.com> wrote in message
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||--yes!!!
--example:
---
use northwind
go
create procedure dbo.spSelectTable
AS
select
EmployeeID
,LastName
,FirstName
from dbo.Employees
go
exec dbo.spSelectTable
go
Joao Mossmann
"Larry Woods" <larry@.lwoods.com> escreveu na mensagem
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||Another option could be a SQL 2000 function, which can
return a table variable... Bruce
create function dbo.funcName
(@.inputParm Varchar(20))
returns @.TableX TABLE (TableID integer,
TableLevelNo integer, TableName sysname,
PK_TableName sysname NULL)
>--Original Message--
>Is it possible for a stored procedure to return a table?
If so, can you
>give me an example?
>TIA,
>Larry Woods
>
>.
>|||Thanks to all. Good answers.
I ALWAYS get good professional answeres here!
Larry Woods
"Larry Woods" <larry@.lwoods.com> wrote in message
news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Is it possible for a stored procedure to return a table? If so, can you
> give me an example?
> TIA,
> Larry Woods
>
>|||Another problem:
I have multiple records that I would like updated as a group so that I can
take advantage of the transaction capabilities of SP's. What I had
envisioned was passing a table into the SP then let the SP work on it.
Assuming that this is the CORRECT way to attack this, can I create a
temporary table, then pass the name of the table into the SP?
Does this make sense?
TIA,
Larry Woods
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eGwwDZwlDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Table variables can NOT be passed as parameters... However you may
> 1. insert rows into another table which can be used by someone else
> 2. insert into atable exec thestoredproc
> 3. select * from openquery(linkedservername, 'exec thestoredproc') as a
> Hope this helps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > Is it possible for a stored procedure to return a table? If so, can you
> > give me an example?
> >
> > TIA,
> >
> > Larry Woods
> >
> >
> >
>|||If the calling environment is just another piece of SQL code or another
stored proc, perhaps a temporary table (probably global) would do the trick?
Otherwise, a where clause for use in dynamic SQL or a list of PK values as a
string would be the other two solutions that come to mind.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e2Kz3EloDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Another problem:
> I have multiple records that I would like updated as a group so that I can
> take advantage of the transaction capabilities of SP's. What I had
> envisioned was passing a table into the SP then let the SP work on it.
> Assuming that this is the CORRECT way to attack this, can I create a
> temporary table, then pass the name of the table into the SP?
> Does this make sense?
> TIA,
> Larry Woods
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:eGwwDZwlDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Table variables can NOT be passed as parameters... However you may
> > 1. insert rows into another table which can be used by someone else
> > 2. insert into atable exec thestoredproc
> > 3. select * from openquery(linkedservername, 'exec thestoredproc') as a
> >
> > Hope this helps.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:#LW5aUwlDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > > Is it possible for a stored procedure to return a table? If so, can
you
> > > give me an example?
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> > >
> >
> >
>
can a SELECT return this result ?!
Hello, this is a table fragment - Table1
Voucher (Int) , RNo (Varchar(20) , Amount (int)
this is a data example
Voucher , RNo , Amount
--
1 , R1 , 100
2 , R1 , -100
3 , R1 , 100
4 , R1 , 50
5 , R1 , 25
6 , R2 , 30
7, R2 , 20
--
now i need to select rows for all amounts in same RNo that does not have
corresponsing amount in opposite sign, like that
Result needed
Voucher , RNo , Amount
--
3, R1 , 100
4, R1 , 50
5, R1 , 25
6 , R2 , 30
7, R1 , 20
--
the first 2 rows have same RNo='R1' and they have same value but opposite
sign 100 , -100 so they will make each other disappear
the third row with voucher=3 then does not find corresponding -100 for same
RNo because it disappeared in first comparison.
i hope im clear but i can't figure out a way to select this retult rather
than using a cursor ! any help
Thank you
BassamDo:
SELECT MAX( voucher ), RNo, SUM( Amount )
FROM tbl
GROUP BY RNo, ABS( Amount ) ;
Anith|||Hi Anith,
This Query wont work for the following set of data
1 r1 100
2 r1 100
3 r1 -100
4 r1 50
5 r1 25
6 r2 30
7 r2 20
Your Query fetches voucher No 3 with values as +100 , this row is not there
in the table at all.
the right result would be to fetch voucher 1 with amount 100.
The following Query will work out for all cases
select * from vouch
where amt > 0 and voucher not in
(
select
min(v2.voucher)
from
vouch v1
inner join vouch v2 on
v1.rno = v2.rno and
abs(v1.amt) = v2.amt and
v1.voucher > v2.voucher
where v1.amt < 0
)
- Sha Anand
"Anith Sen" wrote:
> Do:
> SELECT MAX( voucher ), RNo, SUM( Amount )
> FROM tbl
> GROUP BY RNo, ABS( Amount ) ;
> --
> Anith
>
>|||>> Your Query fetches voucher No 3 with values as +100 , this row is not
Based on the OP's narrative, it is not clear if the row with voucher 1 or
voucher 2 is the "corresponding" row for the one with voucher 3. In any
case, considering any row with a positive amount value, the query can be
changed to:
SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
..
Anith|||Anith,
> SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
> ...
how about this data?
1 r1 100
2 r1 100
3 r1 -100
4 r1 100
5 r1 100
what is the correct output?|||Consider this set of data:
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 21 , 'R1' ,-100 UNION ALL
SELECT 31 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
go
your query returns:
select * from table1
where amount > 0 and voucher not in
(
select
min(v2.voucher)
from
table1 v1
inner join table1 v2 on
v1.rno = v2.rno and
abs(v1.amount) = v2.amount and
v1.voucher > v2.voucher
where v1.amount < 0
)
Voucher RNo Amount
-- -- --
3 R1 100
4 R1 50
5 R1 25
6 R2 30
7 R2 20
31 R1 100
61 R2 30
62 R2 30
(8 row(s) affected)
I think it should return only 1 row for the amount of 100|||Argh...
; WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM tbl )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
Anith|||Consider this data:
delete from Table1;
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 101 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 12 , 'R1' ,-100 UNION ALL
SELECT 13 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
I ran this:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
and got this:
Voucher RNo Amount
-- -- --
5 R1 25
13 R1 100
101 R1 100
6 R2 30
7 R2 20
61 R2 30
62 R2 30
(7 row(s) affected)
Note that 50 is missing and 100 is twice, there should be 100 only
once.
I tweaked your query as follows:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY Amount
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
and got the results which I think are correct:
Voucher RNo Amount
-- -- --
7 R2 20
5 R1 25
6 R2 30
61 R2 30
62 R2 30
4 R1 50
101 R1 100
(7 row(s) affected)
What do you think?|||Alexander Kuznetsov wrote:
> WITH cte AS (
> SELECT Voucher, RNo, Amount,
> RANK() OVER ( PARTITION BY Amount
> ORDER BY RNo, voucher ) AS "rank"
> FROM table1 )
> SELECT Voucher, RNo, Amount
> FROM cte c1
> WHERE ( SELECT COUNT(*) FROM cte c2
> WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
This is another version using row_number (it's easier to understand for me):
WITH cte AS
(
SELECT
Voucher, RNo, Amount,
ROW_NUMBER() OVER ( PARTITION BY RNo, Amount
ORDER BY voucher ) AS rn
FROM table1
)
SELECT c1.Voucher, c1.RNo, c1.Amount
FROM cte c1
WHERE NOT EXISTS
(
SELECT * FROM cte c2
WHERE c2.RNo = c1.RNo AND c2.amount = -c1.amount AND c2.rn = c1.rn
);
Btw, if MS implemented EXCEPT ALL, this would be so simple:
select RNo, amount from table1 where amount >= 0
except all
select RNo, -amount from table1 where amount < 0
The only di
vantage is the missing RNo...
Dieter|||One more approach is to use sum() over() OLAP function, but I don't
think it is available in SS2005 yet (it sure would work in Oracle 9i
and higher). Anyway, try someting like this (untested):
select * from(
select ..., sum() over(partition by rno, abs(amount) order by amount)
rolling_total
FROM table1) t
where rolling_total>0
It is amazing how powerful and useful are OLAP functions, once you get
used to them!
Voucher (Int) , RNo (Varchar(20) , Amount (int)
this is a data example
Voucher , RNo , Amount
--
1 , R1 , 100
2 , R1 , -100
3 , R1 , 100
4 , R1 , 50
5 , R1 , 25
6 , R2 , 30
7, R2 , 20
--
now i need to select rows for all amounts in same RNo that does not have
corresponsing amount in opposite sign, like that
Result needed
Voucher , RNo , Amount
--
3, R1 , 100
4, R1 , 50
5, R1 , 25
6 , R2 , 30
7, R1 , 20
--
the first 2 rows have same RNo='R1' and they have same value but opposite
sign 100 , -100 so they will make each other disappear
the third row with voucher=3 then does not find corresponding -100 for same
RNo because it disappeared in first comparison.
i hope im clear but i can't figure out a way to select this retult rather
than using a cursor ! any help
Thank you
BassamDo:
SELECT MAX( voucher ), RNo, SUM( Amount )
FROM tbl
GROUP BY RNo, ABS( Amount ) ;
Anith|||Hi Anith,
This Query wont work for the following set of data
1 r1 100
2 r1 100
3 r1 -100
4 r1 50
5 r1 25
6 r2 30
7 r2 20
Your Query fetches voucher No 3 with values as +100 , this row is not there
in the table at all.
the right result would be to fetch voucher 1 with amount 100.
The following Query will work out for all cases
select * from vouch
where amt > 0 and voucher not in
(
select
min(v2.voucher)
from
vouch v1
inner join vouch v2 on
v1.rno = v2.rno and
abs(v1.amt) = v2.amt and
v1.voucher > v2.voucher
where v1.amt < 0
)
- Sha Anand
"Anith Sen" wrote:
> Do:
> SELECT MAX( voucher ), RNo, SUM( Amount )
> FROM tbl
> GROUP BY RNo, ABS( Amount ) ;
> --
> Anith
>
>|||>> Your Query fetches voucher No 3 with values as +100 , this row is not
Based on the OP's narrative, it is not clear if the row with voucher 1 or
voucher 2 is the "corresponding" row for the one with voucher 3. In any
case, considering any row with a positive amount value, the query can be
changed to:
SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
..
Anith|||Anith,
> SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
> ...
how about this data?
1 r1 100
2 r1 100
3 r1 -100
4 r1 100
5 r1 100
what is the correct output?|||Consider this set of data:
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 21 , 'R1' ,-100 UNION ALL
SELECT 31 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
go
your query returns:
select * from table1
where amount > 0 and voucher not in
(
select
min(v2.voucher)
from
table1 v1
inner join table1 v2 on
v1.rno = v2.rno and
abs(v1.amount) = v2.amount and
v1.voucher > v2.voucher
where v1.amount < 0
)
Voucher RNo Amount
-- -- --
3 R1 100
4 R1 50
5 R1 25
6 R2 30
7 R2 20
31 R1 100
61 R2 30
62 R2 30
(8 row(s) affected)
I think it should return only 1 row for the amount of 100|||Argh...
; WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM tbl )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
Anith|||Consider this data:
delete from Table1;
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 101 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 12 , 'R1' ,-100 UNION ALL
SELECT 13 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
I ran this:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
and got this:
Voucher RNo Amount
-- -- --
5 R1 25
13 R1 100
101 R1 100
6 R2 30
7 R2 20
61 R2 30
62 R2 30
(7 row(s) affected)
Note that 50 is missing and 100 is twice, there should be 100 only
once.
I tweaked your query as follows:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY Amount
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
and got the results which I think are correct:
Voucher RNo Amount
-- -- --
7 R2 20
5 R1 25
6 R2 30
61 R2 30
62 R2 30
4 R1 50
101 R1 100
(7 row(s) affected)
What do you think?|||Alexander Kuznetsov wrote:
> WITH cte AS (
> SELECT Voucher, RNo, Amount,
> RANK() OVER ( PARTITION BY Amount
> ORDER BY RNo, voucher ) AS "rank"
> FROM table1 )
> SELECT Voucher, RNo, Amount
> FROM cte c1
> WHERE ( SELECT COUNT(*) FROM cte c2
> WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
This is another version using row_number (it's easier to understand for me):
WITH cte AS
(
SELECT
Voucher, RNo, Amount,
ROW_NUMBER() OVER ( PARTITION BY RNo, Amount
ORDER BY voucher ) AS rn
FROM table1
)
SELECT c1.Voucher, c1.RNo, c1.Amount
FROM cte c1
WHERE NOT EXISTS
(
SELECT * FROM cte c2
WHERE c2.RNo = c1.RNo AND c2.amount = -c1.amount AND c2.rn = c1.rn
);
Btw, if MS implemented EXCEPT ALL, this would be so simple:
select RNo, amount from table1 where amount >= 0
except all
select RNo, -amount from table1 where amount < 0
The only di
vantage is the missing RNo...Dieter|||One more approach is to use sum() over() OLAP function, but I don't
think it is available in SS2005 yet (it sure would work in Oracle 9i
and higher). Anyway, try someting like this (untested):
select * from(
select ..., sum() over(partition by rno, abs(amount) order by amount)
rolling_total
FROM table1) t
where rolling_total>0
It is amazing how powerful and useful are OLAP functions, once you get
used to them!
Can a recursive query do this?
I am wondering if there is some type of recursive query to return the values I want from the following database.
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb
Here is a quick schema:
Table: PRODUCT
--------
PRODUCTID PK
PRODUCTNAME nVarChar(30)
Table: ASSEMBLY
--------
PRODUCTID PK (FK to PRODUCT.PRODUCTID)
COMPONENTID PK (FK to PRODUCT.PRODUCTID)
QTY INT
I can write a query that takes the PRODUCTID, and returns all
PRODUCT
=======
PRODUCTID PRODUCTNAME
--- ----
1 Cage Assembly - Solid Sides
2 Cage Assembly - Split Back
3 Cage Assembly - Split Sides
4 Cage Assembly - Split Top/Bottom
5 Cage Assembly - Split Back and Sides
6 Cage Assembly - Split Back and Top/Bottom
7 Cage Assembly - Split Back and Sides and Top/Bottom
8 33S - Aluminum Divider
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
12 Connector Socket
13 Connector Screws
ASSEMBLY
=========
PRODUCTID COMPONENT QTY
--- --- --
1 9 8
1 10 4
1 11 1
2 1 1
2 8 1
3 1 1
3 8 1
4 1 1
4 8 1
5 1 1
5 8 2
6 1 1
6 8 2
7 1 1
7 8 3
11 12 8
11 13 8
I need a query that will give me all parts for each PRODUCT.
Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"
The results would be:
PRODUCTID PRODUCTNAME
--- ----
2 Cage Assembly - Split Back
1 Cage Assemble - Solid Back
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
8 33S - Aluminum Divider
12 Connector Socket
13 Connector Screws
Is it possible to write such a query or stored procedure?http://www.dbforums.com/t1080526.html|||in a specific case, yes, if you know in advance how many levels down the hierarchy of assemblies/parts you need to go, you would write a left outer join query with as many joins as the maximum number levels you need to traverse to find all component parts for the given part
in the general case, where this number of levels is not known in advance, no, you can't write a query for this
however, you could write a stored proc, but note that the stored proc would be running a query inside a loop and building up its results in a temp table|||Here is a solution that uses a UDF. I thought it was quite slick.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=152361|||yeah, that's what i suggested -- a query inside a loop that builds a temp table
:) :) :)
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb
Here is a quick schema:
Table: PRODUCT
--------
PRODUCTID PK
PRODUCTNAME nVarChar(30)
Table: ASSEMBLY
--------
PRODUCTID PK (FK to PRODUCT.PRODUCTID)
COMPONENTID PK (FK to PRODUCT.PRODUCTID)
QTY INT
I can write a query that takes the PRODUCTID, and returns all
PRODUCT
=======
PRODUCTID PRODUCTNAME
--- ----
1 Cage Assembly - Solid Sides
2 Cage Assembly - Split Back
3 Cage Assembly - Split Sides
4 Cage Assembly - Split Top/Bottom
5 Cage Assembly - Split Back and Sides
6 Cage Assembly - Split Back and Top/Bottom
7 Cage Assembly - Split Back and Sides and Top/Bottom
8 33S - Aluminum Divider
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
12 Connector Socket
13 Connector Screws
ASSEMBLY
=========
PRODUCTID COMPONENT QTY
--- --- --
1 9 8
1 10 4
1 11 1
2 1 1
2 8 1
3 1 1
3 8 1
4 1 1
4 8 1
5 1 1
5 8 2
6 1 1
6 8 2
7 1 1
7 8 3
11 12 8
11 13 8
I need a query that will give me all parts for each PRODUCT.
Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"
The results would be:
PRODUCTID PRODUCTNAME
--- ----
2 Cage Assembly - Split Back
1 Cage Assemble - Solid Back
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
8 33S - Aluminum Divider
12 Connector Socket
13 Connector Screws
Is it possible to write such a query or stored procedure?http://www.dbforums.com/t1080526.html|||in a specific case, yes, if you know in advance how many levels down the hierarchy of assemblies/parts you need to go, you would write a left outer join query with as many joins as the maximum number levels you need to traverse to find all component parts for the given part
in the general case, where this number of levels is not known in advance, no, you can't write a query for this
however, you could write a stored proc, but note that the stored proc would be running a query inside a loop and building up its results in a temp table|||Here is a solution that uses a UDF. I thought it was quite slick.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=152361|||yeah, that's what i suggested -- a query inside a loop that builds a temp table
:) :) :)
Tuesday, February 14, 2012
Can a dataset in reporting services return multiple datatables
Hi.
I am trying to access data from a stored procedure that returns data in form of multiple data tables. But when i drop this stored procedure in my rdl report, it just shows me the first data table returned by stored procedure.
I want to know that is there any way that I can view all the data tables returned by my stored procedure, or this is not possible in reporting services 2005.
This is not supported by SSRS 2005. You may be able make this work programatically i.e. build your own component to process multipe result sets.Sunday, February 12, 2012
Can @@ROWCOUNT return NULL?
SQL Server 2000 SP3.
Is it possible for the @.@.ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @.@.ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.
Before doing any further troubleshooting, I would like to rule out the
possibility that @.@.ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.
Any related information would be appreciated.
--
remove a 9 to reply by emailDimitri Furman (dfurman@.cloud99.net) writes:
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT
> to a local variable. That information eventually is passed back to the
> client via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot
> be reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL
> Server 7.0 this could happen in the context of a DML query on a table
> with triggers. This is not the case here - the only DML queries are
> INSERTs into table variables, all other queries in the SP are SELECTs.
I have never heard of a case where @.@.rowcount can return NULL. Books
Online gives one hint when @.@.rowcount is not good: when more than two
milliard rows can be affected. In this case, you should try
rowcount_big(). Could this apply to you?
If not, I would recommend that you start troubleshooting. If it is not
repeatable, it will certainly be difficult...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try removing
SET NOCOUNT ON
from the stored procedure.
GeoSynch
"Dimitri Furman" <dfurman@.cloud99.net> wrote in message
news:Xns96C19F85E9F56dfurmancloud99@.127.0.0.1...
> SQL Server 2000 SP3.
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT to
> a local variable. That information eventually is passed back to the client
> via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot be
> reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL Server
> 7.0 this could happen in the context of a DML query on a table with
> triggers. This is not the case here - the only DML queries are INSERTs into
> table variables, all other queries in the SP are SELECTs.
> Any related information would be appreciated.
> --
> remove a 9 to reply by email|||GeoSynch (SpamSlayed@.Casablanca.com) writes:
> Try removing
> SET NOCOUNT ON
> from the stored procedure.
@.@.rowcount should always return a value even if NOCOUNT is on. This
option controls whether rowcount information is passed to the client.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Can @@ROWCOUNT = 0 even when a row is inserted into a table?
Does @.@.ROWCOUNT always return an accurate acount of rows affected by last query OR can it be equal to zero when some rows have been affected?
The @.@.ROWCOUNT function returns the number of rows affected by the last statement, no matter what type (insert/update/delete/select) of the statement is. It just like if you execute a statement in Query Analyzer with the NOCOUNT option off, a message will come with result indicates how many rows are affected.|||And to be clear, the availability of @.@.ROWCOUNT is not affected by the SET NOCOUNT setting.Don
Subscribe to:
Posts (Atom)