|
I've got a query like this:
WITH CTE1 AS
(
SELECT * FROM TABLE WHERE A = @A
),
CTE2 AS
(
SELECT * FROM TABLE WHERE B = @B
)
SELECT * FROM
CTE1
CROSS JOIN
CTE2
The above query works fine for me and always returns cross join result of the two queries. The problem happens when one of those CTEs doesn't return any row and therefore the CROSS JOIN returns nothing as well. When only one of those CTEs return something, my expectation is to return the result of that CTE cross joined with null values. Do you know how I can achieve this purpose?
|
|
|
|
|
Since cross join doesn't have any 'OUTER' option, the result is always all combinations from all sources. If any of the sources have 0 rows the size of the result set is 0.
I think you could use simple outer joins and unions to get desired rows. Something like:
SELECT *
FROM Table A LEFT OUTER JOIN Table B ON 1=1
WHERE ...
UNION
SELECT *
FROM Table A RIGHT OUTER JOIN Table B ON 1=1
WHERE ...
However, I would re-check the design of the data model since normally the need to cross join is very rare and may indicate a design problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi All
I am writting an stored procedure to get the recently inserted records primary key as follows:
ALTER PROCEDURE [DBO].[AddEditUserMsgDetails]
(
@iJobID [INT]=NULL,
@iUserID [INT]=NULL,
@vFromName [VARCHAR](50)=NULL,
@vFromAddress [VARCHAR](50)=NULL,
@vSubject [VARCHAR](50)=NULL,
@iListID [INT]=NULL,
@vActualText [VARCHAR](100)=NULL
)
AS
BEGIN
IF @iJobId IS NULL OR @iJobId=0
BEGIN
INSERT INTO ezlMailJobs (iUserID,vFromName,vFromAddress,vSubject,iListID,vActualText)
VALUES (@iUserID,@vFromName,@vFromAddress,@vSubject,@iListID,@vActualText)
set @iJobID= @@Identity
select @iJobID
END
IF @iJobId IS NOT NULL
BEGIN
UPDATE ezlMailJobs SET iUserID=@iUserID,vFromName=@vFromName,vFromAddress=@vFromAddress,
vSubject=@vSubject,iListID=@iListID,vActualText=@vActualText
WHERE iJobId=@iJobId
END
IF @@ERROR=0
BEGIN
COMMIT TRAN
SELECT 1
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT -1
END
As in the above stored procedure when ijobid is null or zero, It should execute insert statement and return recently inserted records ijobid else it should execute update statement. But as now it is executing insert statement and returning some garbage value. one more thing I have declared ijobid as identity.
I am unable to figure out the problem. Please help me.
Thanks
WAA
|
|
|
|
|
If the jobid is null or 0 you are executing an insert AND an update
IF @iJobId IS NULL OR @iJobId=0
BEGIN
..do insert
<big>set @iJobID= @@Identity</big>
select @iJobID
END
IF @iJobId IS NOT NULL ------ YOU SET IT 2 LINES ABOVE
BEGIN
...do update etc
Also you have commit/rollback, but I don't see a begin tran?
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for your reply
I found the solution, want to share with the needful.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [DBO].[AddEditUserMsgDetails]
(
@iJobID [NUMERIC]=NULL,
@iUserID [NUMERIC]=NULL,
@vFromName [VARCHAR](50)=NULL,
@vFromAddress [VARCHAR](50)=NULL,
@vSubject [VARCHAR](50)=NULL,
@iListID [NUMERIC]=NULL,
@vActualText [TEXT]=NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Return [NUMERIC]
SET @Return=NULL
BEGIN TRAN
IF @iJobId IS NULL OR @iJobId=0
BEGIN
INSERT INTO table1 (iUserID,vFromName,vFromAddress,vSubject,iListID,vActualText)
VALUES (@iUserID,@vFromName,@vFromAddress,@vSubject,@iListID,@vActualText)
SET @Return= SCOPE_IDENTITY()
SELECT @Return AS iJobId
END
IF @iJobId IS NOT NULL
BEGIN
UPDATE table1 SET iUserID=@iUserID,vFromName=@vFromName,vFromAddress=@vFromAddress,
vSubject=@vSubject,iListID=@iListID,vActualText=@vActualText
WHERE iJobId=@iJobId
SELECT 0
END
IF @@ERROR=0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT -1
END
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
|
|
|
The best way to do this, in my opinion, is to use the SCOPE_IDENTITY() function. This function will return the most recent identity value created in the current scope. Returning the value for the current scope is important because if you have triggers firing that perform inserts the @@IDENTITY variable will return the last identity value created, even if it was from a different table because of a trigger. You should use:
set @iJobID = SCOPE_IDENTITY()
after the insert.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks a lot. Its working now.
|
|
|
|
|
I read an article here[^] describing how to perform SQL operations in an atomic manner using transactions. I just do not understand what the usage of a transaction is in the following example (I didn't ask my question below that article because the author has not answered asked questions).
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
AS
BEGIN TRAN
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
INSERT titleauthor(au_id, title_id)
VALUES (@au_id, @title_id)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRAN
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1
My question is, if the the first insert statement failed, why do we need to roll back it? No insertion to database happened, therefore we do not need to rollback anything! If I'm right, what's the problem with the following sproc:
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
@title VARCHAR(20), @title_type CHAR(12))
AS
INSERT titles(title_id, title, type)
VALUES (@title_id, @title, @title_type)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
INSERT titleauthor(au_id, title_id)
VALUES (@au_id, @title_id)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
RETURN 0
ERR_HANDLER:
RETURN 1
|
|
|
|
|
In your version if the second insert fails, then an orphaned title has been added without its associated author.
|
|
|
|
|
You are right, I just figured it out 
Thank you
_
|
|
|
|
|
I guess I found my answer, it's useful when the first statement succeeds and the second one doesn't. At that time we need to rollback the first successful statement.
|
|
|
|
|
There are several considerations in both examples. Some of them are:
1. Transaction usage and scope: In the first example transaction is started and ended inside stored proc. Personally I don't feel this is a good way to handle transactions. Consider what would happen if the real world situation involves call to two stored procs. If both of them autonomously end transaction, there's no quarantee that the result is correct. This is why I always start and end transactions at calling side.
2. Why rollback if first statement fails: Even though the insert itself fails, transaction is started and DML is executed. This has resulted to resource usage. The only way to free those resources is to roll back the transaction (since there's no point to commit a failure).
So basically I would prefer the second version of the stored proc, but I also would ensure that it's executed inside a transaction. This would make it possible to have larger scope transactions and also to retry failed operations inside a transaction which is a very elemental requirement in certain system types.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Re: your point 1
The only time I've written stored procedures was when I was using ad hoc ODBC statements (about ten years ago) and had no other way to do transactions.
Currently, with ADO.net, I can do transactions in my program so I don't use stored procedures at all.
However, a properly designed system of stored procedures should provide everything the application needs, including transactioning.
The application shouldn't need to know about the details of the schema and what the stored procedures are doing, and therefore should be able to just execute the stored procedures without thought to whether or not to use transactions.
|
|
|
|
|
I think there are several factors that affect the choice how transactions should be used.
In my example I used two separate procedures that are executed in a sequence. If the execution as a whole should have ACID properties, the must be wrapped inside a common transaction. Basically this gives two options:
1. create a new stored procedure combining both calls thus leading to single call to backend
2. use transaction with a scope over two different SQL calls
In point 1, transaction problem is solved (in a simple case) but also the backend now knows (at least partly) the business logic (order of operations and the sequence). This may or may not be acceptable. The problem becomes more difficult if the procedures are not located in the same backend
In point 2, we would need a higher level transaction manager (such as classes in System.Transactions namespace or even self made mechanism). This adds extra 'overhead' to the programming but also it's possible to have more resources inside the same transactions. If this is needed, is again depending on the requirements of the system.
One good link that came in mind: http://blogs.msdn.com/diegumzone/archive/2006/08/14/699219.aspx[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I suppose two phase commit would be another reason to avoid stored procedures and just write the stuff in code. 
|
|
|
|
|
Might be
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Table tblMyTable has a field myXMLField with datatype xml
Question:
I would like to pull out the values from each record and then group them so that only shows the distict values.
How is this done please?
Thanks
This is what I have at present but it is not correct
select
stringList.value('(/value)')
from
tblMyTable
The records inside the xml field in the table is similar to:
record1 shows:
<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value1</value>
</stringList>
record2 shows:
NULL
record3 shows:
<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value5</value>
<value>value2</value>
</stringList>
record4 shows :
<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value4</value>
<value>value1</value>
<value>value5</value>
<value>value2</value>
</stringList>
RESULT:
value1
value2
value4
value5
|
|
|
|
|
I think there's some problems with the namespace. If you give it a name then query method works fine. For example:
DECLARE @x xml
SET @x = '<stringList xmlns<big>:A</big>="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>'
SELECT @x.query('data(/stringList/value)'
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
That does not solve the issue.
I have modified the sql as follows. The error says Syntax error near 'stringList'
Any thoughts please?
DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)
INSERT @Sample
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'
SELECT *
FROM @Sample
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"/value') AS t(c)
|
|
|
|
|
That wasn't exactly my point.
Enclose field named xml in brackets (is that correct word for [ and ]?) like:
CROSS APPLY s.[xml].nodes...
Then if you run the query you have, you'll get empty results. But if you have an alias (like A) for the namespace, the query runs fine:
...xmlns:A="http:...
Also you don't have to use namespace in the xquery.
Here's the full code:
DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)
INSERT @Sample
SELECT '<stringList xmlns:A="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList xmlns:A="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList xmlns:A="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'
SELECT *
FROM @Sample
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.[xml].nodes('/stringList/value') AS t(c)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
No problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i created a job to transfer data in one table to another table, it's working perfectly, but the problem is when data is transfering because of primary key vales it gives an error(primary key duplication), how can i insert data without getting an error
one possible thing is dalete every thing in the second table and then insert every thing in the first table to the 2nd.but i don't think that this is a good solution , i'm expecting a better solution.
|
|
|
|
|
If you want to update table2 from the matchuing records in table 1 and then insert any new ones
Update table2
set col1 = a.col1,.....
from table2 b, table1 a
where b.primarykeycol = a.primarykeycol
insert into table2
select col1,col2.... from table1 a
where not exists (select 1 from table2 b where b.primarykeycol = a.primarykeycol)
If you don't want to update table2 ignore the update.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanx, but there is another problem, the two tables are in two servers ,assume-> table1 is in server A (sql 2000) and the table 2 in server B (sql 2005), so i want to do the transfer from server A to server B . i tried it with
insert into table_B (eid,ename,eaddr)
select r.eid,r.ename,r.eaddr
from [server A ip]\[sql instance]\[db]\table_A r
but get an error at the srver ip
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
