iterative query

any body know how i can transform a recursive query to iterative one?
if possible i need a simple example witth little explanation

Thanks alot.Please provide a sample of what you have now so that it would be easier to help you.|||

ok thanks before any thing,

it is for learnning purposes, i really have no example, but i have heard about it, so if possible if you can provide a simple scenario. with the conversion

thanks alot.

Issue with SQL Parameter when used with % character.

Hi all,

is there a known issue when using a SQL Paramater in a SQL string that uses a '%' character for wildcard string searches?

For example, if I used the following...

SQLCeParameter pKeyword = new SQLCeParameter("@.Keyword", SQLDbType.Char);
pKeyword.value = "Some Text";

string strSQL = "SELECT * FROM tbl_Keywords WHERE (Keyword = @.Keyword%)";

This just never returns and results. But if I run the query in a query analyser window replacing the @.Keyword% with "Some Text", I get results.

I guessing that the placing of the character '%' in the string is the cause maybe...

try putting the % in the string...

pKeyword.value = "Some Text%";

|||

for wildcard searches, you need the LIKE statement. example:

SELECT * FROM someTable WHERE FieldName LIKE @.parameter + '%'

|||Sorry, my query does actually have the LIKE statemet, its just when I post the thread I wrote the query off the top of my head.|||

try this

string strSQL = "SELECT * FROM tbl_Keywords WHERE Keyword LIKE" + @.Keyword + "%";

|||

>>This just never returns and results. <<

What does this mean? It returns nothing? Or it takes forever?

Check the plan of the query using the Display Estimated Plan feature. You should see a very large hotspot in there somewhere that is taking large amounts of the plan (since you are only dealing with one table, it might not be as useful). Then run it without the wildcard and see how the plan looks.

My guess is that it is just taking forever. And is there lots of data? Is that column indexed? An index will work with a LIKE 'value%' criteria, so that shouldn't be a problem. It could be that your indexes aren't optimized too, but first things first :)

|||My Issue is that I no resuts are being returned. I doubt it is taking forever as I am doing a search on a table that has no more that 100 records.

Thanks|||

so have you tried my suggestion earlier?

if you copy and paste your query into query analyzer or SQL management console or something, and execute it - does it bring back results?

so do you get results now, or are you have a perf issue?

another way to increase the perf really is to use stored procedures, if you havent already - safer and faster than standard SQL Injection as you maybe doing ;-)

Issue with cast function

Hi

Can someone help me in the following:
An example is shown below, I had assign a date (format: dd/mm/yyyy) to @.vdate. Follow by a cast to vchar. The issue is that the date format is changed after the cast. I would like the date format to remain as dd/mm/yyyy after doing the cast. Hope someone can give some advices. Thanks alot.

Example:

declare @.vdate as datetime
set @.vdate = convert(datetime,'01/10/2005',103)

select @.vdate --> '10/01/2005' (date format required)

cast(varchar(20),@.vdate) --> 'Oct 1, 2005' (date format changed)Dates are not stored in any format. They are actually stored as numeric values, which then are displayed in a requested format.
Read up in Books Online on the topics of datetime datatypes and the CAST and CONVERT functions.

Issue about full-text index

Hi: all,

I have created a full text index on a column(column1) in a table(table1).

There is one record in the table1. And for example, the column1 stores "Documents".

Table1.Column1:"Documents"

When I run the query:

select * from Table1 where contains(Column1,'Documents')

There is 1 one row be selected.

But when I run the query:

select * from Table1 where contains(Column1,'cum')

There is no row be selected.

So the contains can only search the whole word,right?

Can I search a part of the whole word?

Just like the result of : select * from Table1 where Column1 like '%cum%'?

Thanks,

Tracy

You can search the part of the word but only with Prefixed wild card...

select * from Table1 where contains(Column1,'"Doc*"')|||

So sql server full-text index doesn't support to search any part of a word, right?