|
Hi,
Check If following statement works for you.
SELECT CASE WHEN BITCOL = 1 THEN 'CHECKED' ELSE 'UNCHECKED' END
FROM TABLENAME
Regards,
Sulaman
|
|
|
|
|
Could anyone out there assist me with the data-replication problem that i have here.
i have a database which is in a different continent and like 5 clients have to replicate data to it from different countries.
My problem is that i need to allow the subscribers to be able to be able to publish to the main server but it is giving me problems that are related to IP
Anyone with a tutorial that could help?
|
|
|
|
|
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...
|
|
|
|
|
|
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...
|
|
|
|
|
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!
|
|
|
|
|
CASE
WHEN LEN(description) > 71 THEN
SUBSTRING(description,0,CHARINDEX(' ',description,70))+'...'
ELSE '' END AS tooltip,
Got it - thanks again.
|
|
|
|
|
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) 
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 
|
|
|
|
|
Does not this work:
INSERT INTO #OutPut
EXEC h_DailyDividend
@TickerTable = @TickerTableName,
@DateTable = @DateTableName,
@Units = @Units
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
COALESCE may give you better performance - probably only an issue if you have very large tables...
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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).
|
|
|
|
|
Thank's All For Attention It works with Isnull function
Kareem Elhosseny
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Hi Paul,
Thanks for you suggestion. It works well.
Thanks
Jith
|
|
|
|
|
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
|
|
|
|
|
Have a look at this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
