Database

8-Nov-08 4:35

For creating full text indexes see: CREATE FULLTEXT INDEX (Transact-SQL)[^]

But as said before, if your query need is simple use LIKE.

Mika

The need to optimize rises from a bad design.

My articles[^]

Cross Join

Meysam Mahfouzi5-Nov-08 19:59

5-Nov-08 19:59

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?

Re: Cross Join

Wendelius6-Nov-08 7:53

insert_update Stored procedure and return Identity value

6-Nov-08 7:53

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[^]

wajans5-Nov-08 18:35

wajans

5-Nov-08 18:35

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
/* 
   Logic Comments: INSERTING OR UPDATING INTO THE TABLE ezlMailJobs
   EXEC AddEditUserMsgDetails null,505,'waa','waa@v-empower.com','Test',816,
   '<html><body>Hi <!FN><br>please ignore</br></body></html>'
   
*/
 
  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

Re: insert_update Stored procedure and return Identity value

Ashfield5-Nov-08 21:11

Ashfield

5-Nov-08 21:11

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

Re: insert_update Stored procedure and return Identity value

wajans6-Nov-08 1:12

wajans

6-Nov-08 1:12

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
/* Written By: Wajid Ahmed Ansari
   Date: 05-11-2008  
   Logic Comments: INSERTING OR UPDATING INTO THE TABLE ezlMailJobs
   EXEC AddEditUserMsgDetails null,505,'waa','waa@v-empower.com','Test',816,'<html><body>Hi <!FN><br>please ignore</br></body></html>'
   EXEC AddEditUserMsgDetails NULL,505,'Khadri','khadr.syed@v-empower.com','Testing Procedure',NULL,NULL
   EXEC AddEditUserMsgDetails 4,505,'Khadri','khadr.syed@v-empower.com','Testing Procedure',816,NULL
   SELECT * FROM ezlMailJobs
   sp_help ezlMailJobs
*/
   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

Re: insert_update Stored procedure and return Identity value

Ben Fair7-Nov-08 2:17

Ben Fair

7-Nov-08 2:17

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)

Re: insert_update Stored procedure and return Identity value

wajans7-Nov-08 2:55

wajans

7-Nov-08 2:55

Thanks a lot. Its working now.

Atomic Sql Statements (Transaction)

Meysam Mahfouzi5-Nov-08 4:41

5-Nov-08 4:41

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

J4amieC5-Nov-08 4:46

J4amieC

5-Nov-08 4:46

In your version if the second insert fails, then an orphaned title has been added without its associated author.

Meysam Mahfouzi5-Nov-08 4:50

5-Nov-08 4:50

You are right, I just figured it out Smile | :)

Thank you

Meysam Mahfouzi5-Nov-08 4:48

5-Nov-08 4:48

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.

Wendelius5-Nov-08 8:04

5-Nov-08 8:04

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[^]

PIEBALDconsult5-Nov-08 13:33

PIEBALDconsult

5-Nov-08 13:33

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.

Wendelius6-Nov-08 7:39

6-Nov-08 7:39

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[^]

PIEBALDconsult6-Nov-08 11:27

PIEBALDconsult

6-Nov-08 11:27

I suppose two phase commit would be another reason to avoid stored procedures and just write the stuff in code. Big Grin | :-D

Wendelius7-Nov-08 6:36

sql server 2005 query xml datatype fields

7-Nov-08 6:36

Might be

The need to optimize rises from a bad design.

My articles[^]

arkiboys5-Nov-08 2:55

arkiboys

5-Nov-08 2:55

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

Wendelius5-Nov-08 10:33

5-Nov-08 10:33

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[^]

arkiboys5-Nov-08 10:54

arkiboys

5-Nov-08 10:54

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)

Wendelius5-Nov-08 11:13

5-Nov-08 11:13

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[^]

arkiboys6-Nov-08 1:50

arkiboys

6-Nov-08 1:50

Ok,
Thank you

Wendelius6-Nov-08 7:06