Database

16-Apr-09 1:30

If I have the following code

<br />
Exec('Select Count(*) From table')<br />

how can I put the value of the execution into an integer type variable

Re: Output

scottgp16-Apr-09 2:33

scottgp

16-Apr-09 2:33

I honestly didn't know either, but a quick search found

http://www.sqlteam.com/article/return-recordsets-from-dynamic-queries-called-by-exec[^]
and
http://www.sommarskog.se/dynamic_sql.html[^].
The second article looks very complete, and it looks like you might want to check out sp_executesql.
Scott

Re: Output

Kevin Horgan16-Apr-09 21:01

MS SQL query syntax for full text search

16-Apr-09 21:01

Hi,

If you just what to store the COUNT value in a variable (@a) you could do this.

declare @a int
select @a = (SELECT COUNT(*) FROM table)

Now select the count value from the variable...
select @a

Vishnu Nath15-Apr-09 21:56

Vishnu Nath

15-Apr-09 21:56

Hii,

I have a table in a database called dictionary.
I have four columns namely id, word , keyword and meaning.
I have indexed word, keyword and meaning column in a catalog for full text searching of any word.
I want full text searching of any word in these two column and return meaning of that word found in word and keyword column.
I searched the table for the word using following query :

 string wordSearched=textBox1.Text;
"SELECT meaning FROM dictionary WHERE CONTAINS(word, keyword, '+"wordSearched"+')";

But am unable to get the full text search result.
Am i wrong in my syntax for querying full text search.
or may be can anyone help it out giving the proper syntax to query word and keyword column.
Thanks in Advance!

Aspiring Techie,
Vishnu Nath

Re: MS SQL query syntax for full text search

Rajesh Anuhya16-Apr-09 22:02

Rajesh Anuhya

16-Apr-09 22:02

Try This

Replace Contains Frown | :(

, with instr Smile | :)

Use Instr function

Rajesh B --> A Poor Workman Blames His Tools <--

Re: MS SQL query syntax for full text search

Member 77074615-Mar-10 6:39

Member 770746

15-Mar-10 6:39

Hello,

You can use asterisk.

SELECT meaning FROM dictionary WHERE CONTAINS(*, '+"wordSearched"+')

SELECT meaning FROM dictionary WHERE CONTAINS(word, '+"wordSearched"+') or CONTAINS(keyword, '+"wordSearched"+')

or containstable sample

select id, f.[rank], [Name], [Description]   from eshop_product inner join  containstable(eshop_product,[Description], 'ISABOUT(bike weight(.9), pedals weight(.3) )  ' ) f on eshop_product.id = f.[KEY]

or use karsa full text search manager

I hope this help you

Michal

DateTime Problem

jonhbt15-Apr-09 21:24

15-Apr-09 21:24

Hi I have this code:

  Declare @v_update_sql varchar(max)<br />
<br />
	Declare @d_Date datetime <br />
	Declare @v_target_table_name varchar(max)<br />
		Set  @v_target_table_name = 'IM_DOC_TRANSFORM'<br />
	<br />
      Set @d_Date = '2009-03-02 12:46:00.000'<br />
     Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where    Last_Documented_Date_And_Time = Convert(datetime,'+@d_Date+')'<br />
    Exec(@v_update_sql)<br />
<br />

The error that is arising is that of cannot convert to datetime from string.the variable @d_Date is a valid date but still it cannot convert it. That statement works went not in a string variable but in this format it doesn't. Can someone help me please? Thanks

Kevin Horgan15-Apr-09 21:43

15-Apr-09 21:43

Hi jonhbt,

Try this, you seem to be missing some qoutation makers on the CONVERT in your dynamic SQL which are necessary for a STRING to be concatenated.

Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'''+@d_Date+''')'

I hope this helps,

Kevin

jonhbt15-Apr-09 22:05

15-Apr-09 22:05

Hi Kevin,

Thanks for your help but the same happened. This is the error that I am Having.

Conversion failed when converting datetime from character string.

Kevin Horgan15-Apr-09 22:19

15-Apr-09 22:19

OK maybe this will help instead...

Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = CAST('''||@d_Date||''' AS DATETIME)'

If it does not work, do a "SELECT @v_update_sql" immediately after the "SET @v_update_sql..." statement and see what the dynamic SQL looks like with the date string included. You might see an obvious syntax error then perhaps.
Cheers,

Kevin

jonhbt15-Apr-09 22:43

15-Apr-09 22:43

thanks again Kevin but its not working either. Creating the select after is not showing the results since its stating an error near the |. thanks agin for your time

Kevin Horgan15-Apr-09 22:55

15-Apr-09 22:55

Hi again,

One thing I just noticed is you need a SPACE before the "WHERE clause" in the SQL example code you posted above. At the moment it dynamic SQL command looks like this.

Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORMWhere Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME)

Try adding a space as follows. I have also replaced the "||" with "+" if you would prefer this method of concatenation.

declare @v_update_sql varchar(1000)
declare @d_date char(23)
select @d_date = '2009-03-02 12:46:00.000'
declare @sql varchar(1000)
declare @v_target_table_name varchar(100)
Set @v_target_table_name = 'IM_DOC_TRANSFORM'
Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + ' Where Last_Documented_Date_And_Time = CAST('''+@d_Date+''' AS DATETIME)'

select @v_update_sql
execute (@v_update_sql)

The dynamic SQL should then look like this...

Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORM Where Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME)

See if that works for you.

Cheers,

Kevin

jonhbt15-Apr-09 23:11