Case statement

Hi

I am having a few problem with a case statement.

I have a table with customer data in and I need to bring back all the rows
which have data missing from 2 different columns.

customer table
CustomerID Name SoftwareVersion
1001 TEST1 V5
1002 TEST2 V5
3 TEST3 V5
004 TEST4 V6
ect..

CustomerID Table
ID
1001
1002
1003
1004
ect...

I have a need customerid table and I need to list all the customers who are
not in this table or whos id does not match the new listings,
alos there is a softwareVersion Table list all the newest software so the same
will need to be done for this.

Thanks

Rich

You need to create the join batween the two table with CustomerID & ID, with not equal operator, yuor query may look like this

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

Gurpreet S. Gill

|||

Hi

I would like to use the case statement because where there is an error, I would like the cell to display

ERROR: 'then customerid'

Thanks

Rich

|||

I dont get you, what you want to do ? tell me in more detail.

|||

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

This won't work, you're just going to get every Customer joined to every customer except themselves.

You need to use an outer join something like this

SELECT A.CustomerID
FROM Customer A
LEFT JOIN CustomerID B ON A.CustomerID = B.[ID]
WHERE B.ID IS NULL

|||I assume you mean that the CustomerID table in your example above is really the result of a query you want to write. The problem is you haven't specified what "all the customers who are not in this table or whos id does not match the new listings" means. How do you know if a customer is not in that table or if they do not match the new listings, is that info in another table?|||

yes SnMSDN

he didnt make much clear what he wants, that why i did post that sample code. But he not responding, might he had solved the problem

sql

cascading parameters

Hi

I have three params p1 , p2 and p3.

All 3 are non queried with values Yes and NO .

if p1 is yes only i have to enable the remaining twp params otherwise disable them.

Can some one suggest as to how this can be achieved.

Thanks

You could:

1) Create a function that takes care of it using a switch statement and passes the correct information to the database.

2) Create a stored procedure that uses a case statement.

cascade delete ...

Hi!
I have a question about implementing cascade deletes in sql server 2000
database.
I have two tables:
Object (ObjectId, ObjectName, ObjectType, etc.)
Object_Association (ObjectParentId, ObjectId)
Both fields in the association table are foreign key related to the
ObjectId field in the first table. I would like the cascade delete on
both these relations.
So, if the tables had the following records:
1, horse, ..
2, cat, ..
3, cattle, ..
4, swine, ..
and
2, 1
3, 2
4, 2
and I delete the horse record in the object table, it should trigger
deleting the first record in the second table, that should trigger
delete of the cat record, ... and finally all the above records should
be deleted.
I get the following error when I try to set the cascade delete flags on
the foreign key relations in sql server 2000:
Introducing FOREIGN KEY constraint 'Object_Object_Association_FK2' on
table 'Object_Association' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
FOREIGN KEY constraints.
How do I achieve the complete cascade delete?
Thanks!kazoo wrote:
> Hi!
> I have a question about implementing cascade deletes in sql server
> 2000 database.
> I have two tables:
> Object (ObjectId, ObjectName, ObjectType, etc.)
> Object_Association (ObjectParentId, ObjectId)
> Both fields in the association table are foreign key related to the
> ObjectId field in the first table. I would like the cascade delete on
> both these relations.
> So, if the tables had the following records:
> 1, horse, ..
> 2, cat, ..
> 3, cattle, ..
> 4, swine, ..
> and
> 2, 1
> 3, 2
> 4, 2
> and I delete the horse record in the object table, it should trigger
> deleting the first record in the second table, that should trigger
> delete of the cat record, ... and finally all the above records should
> be deleted.
> I get the following error when I try to set the cascade delete flags
> on the foreign key relations in sql server 2000:
> Introducing FOREIGN KEY constraint 'Object_Object_Association_FK2' on
> table 'Object_Association' may cause cycles or multiple cascade paths.
> Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
> FOREIGN KEY constraints.
> How do I achieve the complete cascade delete?
> Thanks!
That's a good question. I'm guessing SQL Server cannot handle this type
of delete cascade functionality, probably because of the the
self-referencing table relationship - it won't know which delete cascade
to perform first. You should manage the delete from a stored procedure
instead. From BOL:
"The series of cascading referential actions triggered by a single
DELETE or UPDATE must form a tree containing no circular references. No
table can appear more than once in the list of all cascading referential
actions that result from the DELETE or UPDATE. The tree of cascading
referential actions must not have more than one path to any given table.
Any branch of the tree is terminated when it encounters a table for
which NO ACTION has been specified or is the default."
David Gugick - SQL Server MVP
Quest Software

Capturing ALL elements and attributes

Hi
I need to capture and store the data from the following xml file:
- <server name="xxx-abcd">
- <match matchno="1">
<fullkey>99</fullkey>
<key>99</key>
<type>99</type>
<value>29</value>
</match>
- <match matchno="2">
<fullkey>87</fullkey>
<key>87</key>
<type>87</type>
<value>46</value>
</match>
- <match matchno="3">
<fullkey>52</fullkey>
<key>52</key>
<type>52</type>
<value>-12</value>
</match>
Using the following schema I can insert everything except the first
line <server name="xxx-abcd">
which I would like to do for each row so that it inserts the following
into my table
e.g
name matchno fullkey key type value
xxx-abcd 1 99 99 99 29
xxx-abcd 2 87 87 87 46
xxx-abcd 3 52 52 52 12
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
- <xsd:element name="match" sql:relation="cpl">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element name="fullkey" type="xsd:integer" />
<xsd:element name="key" type="xsd:string" />
<xsd:element name="type" type="xsd:string" />
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="matchno" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
What changes do I need to make to my schema (above) to be able to do
this please?
Thanks in advance to anyone who replies!
Regards
ChrisAssume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
much easier way to achieve the same result with the XML data type .value()
and .nodes() methods.
<templeoakchris@.hotmail.com> wrote in message
news:1171569947.901469.306050@.j27g2000cwj.googlegroups.com...
> Hi
> I need to capture and store the data from the following xml file:
> - <server name="xxx-abcd">
> - <match matchno="1">
> <fullkey>99</fullkey>
> <key>99</key>
> <type>99</type>
> <value>29</value>
> </match>
> - <match matchno="2">
> <fullkey>87</fullkey>
> <key>87</key>
> <type>87</type>
> <value>46</value>
> </match>
> - <match matchno="3">
> <fullkey>52</fullkey>
> <key>52</key>
> <type>52</type>
> <value>-12</value>
> </match>
>
> Using the following schema I can insert everything except the first
> line <server name="xxx-abcd">
> which I would like to do for each row so that it inserts the following
> into my table
> e.g
> name matchno fullkey key type value
> xxx-abcd 1 99 99 99 29
> xxx-abcd 2 87 87 87 46
> xxx-abcd 3 52 52 52 12
>
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> - <xsd:element name="match" sql:relation="cpl">
> - <xsd:complexType>
> - <xsd:sequence>
> <xsd:element name="fullkey" type="xsd:integer" />
> <xsd:element name="key" type="xsd:string" />
> <xsd:element name="type" type="xsd:string" />
> <xsd:element name="value" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="matchno" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> What changes do I need to make to my schema (above) to be able to do
> this please?
> Thanks in advance to anyone who replies!
> Regards
> Chris
>|||On 15 Feb, 21:48, "Mike C#" <x...@.xyz.com> wrote:
> Assume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
> much easier way to achieve the same result with the XML data type .value()
> and .nodes() methods.
> <templeoakch...@.hotmail.com> wrote in message
> news:1171569947.901469.306050@.j27g2000cwj.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
Regards
Chris|||>
> Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
> Regards
> Chris
>
Sorry, it's been far too long since I did anything with SQLXML on 2000. I
did notice your XML is not well-formed (no closing </server> ). You might
want to look up the "sql:field" attribute at microsoft.com. You will
probably need to expand your XSD schema to include an outer "server" element
something like this (not tested):
...xsd:schema...
<xsd:element name="server">
<xsd:complexType>
<xsd:sequence>
...existing schema "match" element...
<xsd:attribute name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
You will probably need to play with the sql:relation and sql:field
attributes a bit as well.
Sorry couldn't be of more help.

Capturing ALL elements and attributes

Hi
I need to capture and store the data from the following xml file:
- <server name="xxx-abcd">
- <match matchno="1">
<fullkey>99</fullkey>
<key>99</key>
<type>99</type>
<value>29</value>
</match>
- <match matchno="2">
<fullkey>87</fullkey>
<key>87</key>
<type>87</type>
<value>46</value>
</match>
- <match matchno="3">
<fullkey>52</fullkey>
<key>52</key>
<type>52</type>
<value>-12</value>
</match>
Using the following schema I can insert everything except the first
line <server name="xxx-abcd">
which I would like to do for each row so that it inserts the following
into my table
e.g
name matchno fullkey key type value
xxx-abcd 1 99 99 99 29
xxx-abcd 2 87 87 87 46
xxx-abcd 3 52 52 52 12
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
- <xsd:element name="match" sql:relation="cpl">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element name="fullkey" type="xsd:integer" />
<xsd:element name="key" type="xsd:string" />
<xsd:element name="type" type="xsd:string" />
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="matchno" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
What changes do I need to make to my schema (above) to be able to do
this please?
Thanks in advance to anyone who replies!
Regards
Chris
Assume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
much easier way to achieve the same result with the XML data type .value()
and .nodes() methods.
<templeoakchris@.hotmail.com> wrote in message
news:1171569947.901469.306050@.j27g2000cwj.googlegr oups.com...
> Hi
> I need to capture and store the data from the following xml file:
> - <server name="xxx-abcd">
> - <match matchno="1">
> <fullkey>99</fullkey>
> <key>99</key>
> <type>99</type>
> <value>29</value>
> </match>
> - <match matchno="2">
> <fullkey>87</fullkey>
> <key>87</key>
> <type>87</type>
> <value>46</value>
> </match>
> - <match matchno="3">
> <fullkey>52</fullkey>
> <key>52</key>
> <type>52</type>
> <value>-12</value>
> </match>
>
> Using the following schema I can insert everything except the first
> line <server name="xxx-abcd">
> which I would like to do for each row so that it inserts the following
> into my table
> e.g
> name matchno fullkey key type value
> xxx-abcd 1 99 99 99 29
> xxx-abcd 2 87 87 87 46
> xxx-abcd 3 52 52 52 12
>
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> - <xsd:element name="match" sql:relation="cpl">
> - <xsd:complexType>
> - <xsd:sequence>
> <xsd:element name="fullkey" type="xsd:integer" />
> <xsd:element name="key" type="xsd:string" />
> <xsd:element name="type" type="xsd:string" />
> <xsd:element name="value" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="matchno" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> What changes do I need to make to my schema (above) to be able to do
> this please?
> Thanks in advance to anyone who replies!
> Regards
> Chris
>
|||On 15 Feb, 21:48, "Mike C#" <x...@.xyz.com> wrote:
> Assume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
> much easier way to achieve the same result with the XML data type .value()
> and .nodes() methods.
> <templeoakch...@.hotmail.com> wrote in message
> news:1171569947.901469.306050@.j27g2000cwj.googlegr oups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
Regards
Chris
|||>
> Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
> Regards
> Chris
>
Sorry, it's been far too long since I did anything with SQLXML on 2000. I
did notice your XML is not well-formed (no closing </server>). You might
want to look up the "sql:field" attribute at microsoft.com. You will
probably need to expand your XSD schema to include an outer "server" element
something like this (not tested):
...xsd:schema...
<xsd:element name="server">
<xsd:complexType>
<xsd:sequence>
...existing schema "match" element...
<xsd:attribute name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
You will probably need to play with the sql:relation and sql:field
attributes a bit as well.
Sorry couldn't be of more help.

Cant use cursor with SP

Hi

I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

DECLARE Cur CURSOR FAST_FORWARD
FOR SP_Something @.SomeValue

So is it possible to use cursor on SP ? And if it's possible so how ?

Thanks,

Inon.I don't think so...why not put the result set into a table variable?|||I don't think so...why not put the result set into a table variable?

This also don't work, I get an error when I try to INSERT the result from the inner SP to a table var... so is THIS option possible ??

I just want to add that I have several ways to make this work, but I'm trying to be efficient, this is why I want to use an inner SP and not just make the process in the same big SP, I can also make changes in the code (PHP) but I'm trying to make it work this way.

Thanks,

Inon.|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO

Hmmm... there goes the efficient part... :)

Inon.|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D

I agree, didn't mean to express discontent of your solution BTW...

You see, the DB is for a web page, that SP will be called many times.

Thanks for the help,

Inon.|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

If so, try making the SP a function, viz:

create function dbo.some_func (@.inp_value char(?))
returns varchar(100)
as
begin
declare @.temp_var varchar(100)

set @.temp_var = 'select * from ' + @.inp_value
return @.temp_var

end

then in your calling sp:

select @.mysql = some_func(@.parm)
execute(@.mysql)

HTH|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

Almost right, the inner SP returns only a string that will be concatenate to the query in the outer SP, meaning, the inner SP returns only a part of a query, not entire query.

I have a SP that returns a result, I want to use this result in another SP, I didn't want to run both queries because (and correct me if I'm wrong) I know that for optimal performance it's not recommended to run two separated queries on two table in one SP, because the optimizer will confuse with the best execution plans for each query...

Anyway, this is what I finally did, ran both in one SP... is it as bad as I think ?? And should I just make some process in the code and just run two separated SPs? I wanted to make it in one connection session since it will run many many times and by using one SP I cut traffic (for this option only of course) in 50%...

So what do you say is better? One SP with two queries or two SP's with two connections? (But each has its own optimal execution plan).

Thanks,

Inon.