Database

Re: Abbreviatting large text fields with an ellipsis

6-Feb-08 2:25

I am currently optimising an internal intranet site, and I have spotted a possible gain - moving the abbreviation of a text field from the RowDataBound event of a GridView into the SQL stored proc that retrieves the data.

My question is - can anyone suggest the best way to choose an appropriate abbreviation point so that the ellipsis is added only after a break in the text?

i.e.

This is a...

rather than

This is a te...

As I am looking for performance gains, I'll put up with ugly abbreviation if I have to, but it would be nice if there were a [relatively] quick, simple solution to keep it looking pretty...

Colin Angus Mackay6-Feb-08 2:57

Colin Angus Mackay

6-Feb-08 2:57

You could create a function to do this by using the string functions already provided by SQL Server[^].

Upcoming FREE developer events:
* Developer Day Scotland

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

My website |

Re: Abbreviatting large text fields with an ellipsis

Neophyte306-Feb-08 3:08

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

pmarfleet6-Feb-08 0:47

6-Feb-08 0:47

You will have to build the INSERT statement in a varchar variable and execute it dynamically using the EXECUTE command.

Paul Marfleet

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

jithbiz00336-Feb-08 17:39

jithbiz0033

6-Feb-08 17:39

Hi Paul,

Thanks for you suggestion. It works well.

Thanks
Jith

jithbiz00336-Feb-08 23:23

jithbiz0033

6-Feb-08 23:23

Hi Mr. Paul

I have one more doubt. If I want to get a value using SELECT Command in EXECUTE while using Stored Procedure. How to get it

I tried
Statement 1 :
@GetValue = Exec('Select Id from table_'+@Category+' where name = @Name')
Statement 2 :
Exec('Select @GetValue = Id from table_'+@Category+' where name = @Name')

But both are not working,

Thanks
Jith

pmarfleet7-Feb-08 2:24