Database

Re: Abbreviatting large text fields with an ellipsis

6-Feb-08 3:08

Thanks for the suggestion - I'll give it a go.

I guess there's no definitive answer to whether it's ~~faster~~ more efficient to do the abbreviation on the SQL server as opposed to the web-server, what with hardware variables and the like?

My thinking was along the lines of avoiding looping algorithms - the c# code is currently doing a split, then adding words back until a limit is reached - in favour of a more set-based operation...

Maybe I should just grasp the nettle, and use a fixed length abbreviation...

Neophyte306-Feb-08 3:18

Re: Abbreviatting large text fields with an ellipsis

6-Feb-08 3:18

OK - halfway there...

SUBSTRING(description,0,CHARINDEX(' ',description,70))+'...' AS tooltip

Just need to wrap this in a case/if to exclude descriptions shorter than 70 characters...

Thanks for the nudge!

Neophyte306-Feb-08 3:22

Re: Abbreviatting large text fields with an ellipsis

6-Feb-08 3:22

CASE
WHEN LEN(description) > 71 THEN
SUBSTRING(description,0,CHARINDEX(' ',description,70))+'...'
ELSE '' END AS tooltip,

Got it - thanks again.

Mark Churchill6-Feb-08 16:36

Mark Churchill

6-Feb-08 16:36

You can also do this in a stylesheet if you don't care too much about the bandwidth. The benefits to this approach are that the text is trimmed at the appropriate position as the client lays it out.

To be honest I sincerely doubt there is any real gain to be made trimming the string on the database vs in the code behind... (unless we are talking massive amounts of text) Wink | ;)

Mark Churchill
Director
Dunn & Churchill
Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.

Nested Insert Into Statement

Sallu17496-Feb-08 2:08

Sallu1749

6-Feb-08 2:08

I am using the following statement in a SP.

EXECUTE (' INSERT INTO #OutPut
EXEC h_DailyDividend
@TickerTable = '+@TickerTableName+',
@DateTable = '+@DateTableName+',
@Units = '+@Units )

AND IN DailyDividend I am using the following statement.

EXECUTE ('INSERT INTO #TickerTable
EXEC h_SecMstr_SecMap_TQAExch_Info
@IDList = '+@TickerTable+',
@IsTable = 1,
@Type = 0,
@OutPutFormat = 0,
@VenType = 14 ')

And i am getting the following error.

Message: An INSERT EXEC statement cannot be nested.

Can any body help me out how to solve this problem.

Regards
Sulaman

Re: Nested Insert Into Statement

Colin Angus Mackay6-Feb-08 2:23

Colin Angus Mackay

6-Feb-08 2:23

I don't understand why you need to nest these in the first place?

And I don't understand why you feel the need to inject parameters back in. Does this not work:

EXEC h_DailyDividend 
@TickerTable = @TickerTableName, 
@DateTable = @DateTableName,
@Units = @Units

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Creating Many-to-Many Joins
* A simple challenge (solution)

My website |

Re: Nested Insert Into Statement

Sallu17496-Feb-08 17:03

Sallu1749

6-Feb-08 17:03

Actually the h_dailydividend procedure returns a result set which needs to be inserted in #Output table, thats why i write like this. I read somewhere that dynamic statement does not produce this error but even using dynamic string i am getting the same error Frown | :(

Re: Nested Insert Into Statement

Colin Angus Mackay6-Feb-08 21:53

Colin Angus Mackay

6-Feb-08 21:53

Does not this work:

INSERT INTO #OutPut
    EXEC h_DailyDividend 
        @TickerTable = @TickerTableName, 
        @DateTable = @DateTableName,
        @Units = @Units

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Creating Many-to-Many Joins
* A simple challenge (solution)

My website |

Concatenate two column from different table

kimo code6-Feb-08 0:51

kimo code

6-Feb-08 0:51

Hi All;
I have i 2 table named AcceptedMsg,AcceptedNickName
I Want to Concatenate two column(NickName From AcceptedNickName & Msgtext From AcceptedMsg) from the above table and using left outer join to concatenate with my criteria
but i meet a proplem when the first column is null i want the secound field "column " retrived as its value
but the result in this case null

here is my sql query statement
-------------------------------

select Allname = AcceptedNickName.nickname +':'+''+AcceptedMsg.msgtext from AcceptedMsg
left outer join AcceptedNickName
on AcceptedMsg.phoneno = AcceptedNickName.phoneno

and here is the result :
---------------------------
AllName
---------------
Null |
Kimo:Hi All |................... here meet the condition
Null |
---------------

But I want if sql cant meet the condition Retrived to me

AllName
---------------
are U there |
Kimo:Hi All |
thank you |
---------------
Can any one Help me.
Thanks For All

Kareem Elhosseny

pmarfleet6-Feb-08 1:46

6-Feb-08 1:46

Use the ISNULL function to return an empty string if AcceptedMsg.msgtext is NULL. This will ensure that the concatenation will still work.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Neophyte306-Feb-08 2:16

6-Feb-08 2:16

COALESCE may give you better performance - probably only an issue if you have very large tables...

Smile | :)

pmarfleet6-Feb-08 2:31

6-Feb-08 2:31

Neophyte30 wrote:
COALESCE may give you better performance - probably only an issue if you have very large tables...

On what evidence? Please state your source.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Neophyte306-Feb-08 2:56

6-Feb-08 2:56

I have no source - just something I was told [by a contractor DBA] a long time ago. May even have been server-specific (Sql Server 2000).

Sorry if I offended you.

pmarfleet6-Feb-08 3:36

6-Feb-08 3:36

I'm not offended. However, if you are going to critisize the opinions of others you need to know what you are talking about and be prepared to back up your opinions with hard facts. Simply stating 'I'm right, you're wrong' isn't good enough, especially if the opinions you spout aren't your own but are based on a half-heard conversion that occured some time in the past.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Neophyte306-Feb-08 3:43

6-Feb-08 3:43

I wasn't criticising, and certainly wasn't saying you were wrong. Hence the use of the word 'may'. Some more investigation turns up that the performance differences of ISNULL and COALESCE have been debated in the past, ISNULL being the faster/more efficient/whatever. However mention was made (http://sqljunkies.com/WebLog/amachanic/archive/2004/11.aspx[^])

of Standards Conformance, which may have been why I was told to use it (albeit for the wrong reason).

concat command in stored procedure

kimo code6-Feb-08 2:57

kimo code

6-Feb-08 2:57

Thank's All For Attention It works with Isnull function

Kareem Elhosseny

jithbiz00336-Feb-08 0:21

jithbiz0033

6-Feb-08 0:21

Hi Guys

I have a stored procedure which has the coding to insert values in tables with different suffix but one prefix. Eg table_fruits, table_vegetables, table_pulses etc., I am passing the suffix in the procedure. I dont know how to concat the suffix to insert the value in the specific table.

create procedure [dbo].[my_proc](
@category varchar(30),
@Name varchar(50))
As
Begin
insert into table_+@category+ value name=@Name;
End

I dont want to follow

if(@category = 'fruits')
insert into table_fruits values name=@Name;
else if(@category = 'vegetables') etc ., etc.,

Is there any way to do this. Kindly help me.

Thanks
Jith

Re: concat command in stored procedure

pmarfleet6-Feb-08 0:47