|
That looks a real bottleneck: for N entries in the result list, you perform 2N-1 SELECT statements, and N-1 string concatenations, which must be as bad in SQL as they are in any OO language (remember the purpose of StringBuilder versus string in C#).
Are you sure you really need a comma separated list at the SQL level? If you were to perform a simple SELECT statement, you would get a result table that contains the data you want, one result element per row. At practically no cost.
Too often IMO people tend to find an SQL-only solution to a problem. This may well be one more example of that.
|
|
|
|
|
Hi Luc,
Thanks for your reply. Yes, thats what I realize, I would never do the same thing in my programming. (I am a C# programmer) But the problem is, I dont know the correct TSQL Syntax / operators that can let me get rid of this loop.
My intention to get data from stored procedure in the following structure
-------------------------
string product_name,
int affectedRows,
bool hasError,
int[] someIds
-----------------------
Now, everything was fine until I reached the need to get int[] someIds. I was lost, how I could do that. If I just needed to return SomeIds (: int[]) then, yes, I could simply return the result set, but as I needed to get the other string, bool, int etc type data so I created a temporary table in my procedure to store the int values (someIDs : int[]) then, I used the specified procedure to read back the int ids from the temporary table and serialize as comma separated string to return.
Would you please show me any technique to get such data structure from stored procedure ?
|
|
|
|
|
I wouldn't know how to do that, and that is due to the fact that I would never want that to begin with. Get a table result, and have the client code iterate over it. That is simple and inexpensive.
|
|
|
|
|
|
Hi Piebald,
Thank you sooooooooooooo much for the kind help. Yes, the COALESCE function is what solved my entire problem. In fact, the link you provided discussed the exactly same problem what I had to solve, so that page just did my task completely. I highly appreciate your help.
|
|
|
|
|
The difference between a conventional programming language like C# and a data oriented language like T-SQL is that T-SQL is "set" oriented. In other words, it has been designed to work with "sets" of data rather than a single piece of information. Your code seems to work with one piece of information at a time which defeats the purpose of using T-SQL.
Also I noted that you have temporary tables. Using Table Variables[^] may improve performance. Note that Table Variables have certain restrictions.
|
|
|
|
|
Hi Shameel,
Thank you very much for your suggestions. I got the problem solved by COALESCE function that was suggested by the previous reply. But The Table Variable over temporary variable is a nice thing that learned from your link.
Regards.
|
|
|
|
|
Oops, sorry, this is my first post and I couldn't see that the question was already answered. Please ignore.
I am not sure if I understand the question but here are the results of my testing...
-- Original query with bits added in so I get a result set
DECLARE @totalIds INT; SET @totalIds = 2
DECLARE @DeletedIDArr VARCHAR(50)
DROP table #deletedIds
SELECT * INTO #deletedIds FROM
(SELECT 1 AS Id UNION SELECT 2) a
DECLARE @Iterator INT
SET @Iterator = 0
WHILE (@Iterator < @totalIds)
BEGIN
IF(@Iterator = 0)
BEGIN
SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds
END
ELSE
BEGIN
SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50)))
FROM #deletedIds
WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds);
END
SET @Iterator = @Iterator + 1
END
SELECT @DeletedIDArr
-- My version
SET @DeletedIDArr=''
SELECT @DeletedIDArr = @DeletedIDArr+CAST(Id AS varchar(50))+',' FROM #deletedIds
SET @DeletedIDArr = LEFT(@DeletedIDArr,LEN(@DeletedIDArr)-1)
SELECT @DeletedIDArr
|
|
|
|
|
I have created a SP and using Oracle 11g.
I have declared a variable in a procedure as:
v_juriscode billingcycle.juriscode%TYPE; (an existing type)
.
.
.
I have a condition as
IF n_count > 1
THEN
v_juriscode := 'LODESTAR';
ELSE
IF n_count = 1
THEN
SELECT pm.dejuriscode
INTO v_juriscode
FROM pwrline.lsmdphysicalmeter pm
WHERE pm.decommdevid = V_COMMDEVID;
END IF;
END IF;
UPDATE DEBADGERPROGLOG SET JURISCODE=v_juriscode
WHERE UIDBADGERPROGLOG = V_UID;
When the condition c_count = 0 is encountered I have not set the value for juriscode (ie i hv not mentioned that condition).
I have noticed that when no value is set to the v_juriscode variable the table row is not updated, I wanted to know why this happens.
Thanks in advance.
Lionel Noronha
|
|
|
|
|
IF n_count > 1
THEN
v_juriscode := 'LODESTAR';
ELSE
IF n_count = 1
THEN
SELECT pm.dejuriscode
INTO v_juriscode
FROM pwrline.lsmdphysicalmeter pm
WHERE pm.decommdevid = V_COMMDEVID;
END IF;
END IF;
IF v_juriscode IS NOT NULL THEN
UPDATE DEBADGERPROGLOG SET JURISCODE=v_juriscode
WHERE UIDBADGERPROGLOG = V_UID;
END IF;
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
lionelcyril wrote: When the condition c_count = 0 is encountered I have not set the value for juriscode (ie i hv not mentioned that condition).
I have noticed that when no value is set to the v_juriscode variable the table row is not updated, I wanted to know why this happens.
Precluding the previous answer that statement is contradictory.
If the update runs, the where condition is satisfied and no error occurs then an update did happen. There is no alternative.
If an update occurs then a value will be updated. There is no such thing a "no value" unless you mean null (presumably you don't mean spaces.) If you didn't set a value then null is used.
If you don't want an update if there is no value then use the code from the other response.
If you want an update with a different value then set v_juriscode to a default value before you execute the rest of the code.
|
|
|
|
|
Hi guys,
I am looking for the best ways to create an api which will monitor the database table row version.
if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information:
(1) LogIn detail of the data server.
(2) Access database name with datatime.
(3) Affected table name, row version and row number as well.
I will be glad for your any suggestion / ides on that.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
I currently use
HOST_NAME() and
APP_NAME() in a trigger for insert, update and delete to monitor for anything that wasn't supposed to happen. You can then include any of the detail from INSERTED and DELETED tables as well as connection info.
HTH
|
|
|
|
|
Hi,
Thanks for the replay, well if we consider the scenario given below:
if any user:
1. logIn to the database -> open the table in design mode--> block the trigger.
2. Update a single column value.
3. Unblock the trigger - > Save the table.
I am thinking on how could I detect the changes from my C# application.
Thanks once again for your valuable input.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
I think your app would need to hold a copy of the table and constantly compare, and I doubt that's worth the trouble, so don't bother trying.
You can't protect against a malicious authorized user. There are also times when you legitimately want to correct some data and make it appear to have always been correct.
In my opinion, an app should never hold onto data for longer than it needs it; when it needs the data again it should query it again.
|
|
|
|
|
Doing a trace isn't the same as an intruder-protection, and those are both different from versioning a row. I'd suggest you create a fingerprint for the data.
What are you trying to achieve/prevent exactly?
Bastard Programmer from Hell 
|
|
|
|
|
Hi all! I would like to know the SQL that can grant select on a Database Role to another role.
I want members of one role to view and see members in another role so thet the first role can select members from the second role. I don't know how to do this. Please help.
|
|
|
|
|
|
Hi .
I have three entities that are related to each other . So these entities have relationships with each other but the problem here is that their relationships finally make a closed path . Would you please tell me what should i do in situations like this ?
I do not know even this is about normalization or not?
Entities :
Media ( MediaID , MediaName , ...,GroupID)
Articles (ArticleID , Title,... ,GroupID)
Groups (GroupID , GroupName ,... )
Article_Media ( MediaID, ArticleID, ...)
thanks
Give me the solution because in update we can not update the records
|
|
|
|
|
The only thing about an update that I can see failing whould be if you are updating one of the ID columns. Assuming that these are keys of some sort, you shouldn't be doing that anyway. If you can provide more information about what fails, that would be useful. Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Can you describe in some detail what Media and Articles are and how they relate to each other? Can you also describe what the Groups entity is about?
|
|
|
|
|
You need a group before you can create Media, Articles or a.. group? I hope that GroupID is nullable?
That's the only circular reference, the one in Groups. If the GroupID is nullable, you create one, then Media and Articles.
yousefshokati wrote: I do not know even this is about normalization or not?
It is. Those rules make you think about your design and gives you a neat way of getting some relevant structure in there. As it's now, it's merely a collection of field-names.
yousefshokati wrote: Give me the solution
The solution is easy, you need to rethink your design. About what each entity and each attribute are going to represent, and how they relate. Normalization is about getting that structure.
Good luck.
Bastard Programmer from Hell
|
|
|
|
|
Hi And tanks for your answer .
You say that When we design our database , we should design in a way that we do not have any closed path ?
Am I right?
But if we have 2 different relationship between 2 entities , then what should we do . Consider the following example :
We have MembersTable Which represents the members of teams and we have TeamsTable That represents the Team Entity Now We have to relations between these 2 . The First relation is "Plays For" which means that "Member Plays for team A for example " and the second is "Captain Of" which stands for "Members x is captain of team A" . Now if we update one of these FK s that are in tables for creating the relationships then we will get an error , won't be?
|
|
|
|
|
yousefshokati wrote: Hi And tanks for your answer
You're welcome
yousefshokati wrote: You say that When we design our database , we should design in a way that we do not have any closed path ?
Am I right?
I'm saying that you should normalize the design; that will eliminate all weird stuff from the design and give you something you can work with. I know that it's a bit of an investment in terms of time, but it repays itself rather quickly.
yousefshokati wrote: We have MembersTable Which represents the members of teams and we have TeamsTable That represents the Team Entity Now We have to relations between these 2 . The First relation is "Plays For" which means that "Member Plays for team A for example " and the second is "Captain Of" which stands for "Members x is captain of team A" . Now if we update one of these FK s that are in tables for creating the relationships then we will get an error , won't be?
Yes, you will. Normalization would state that you don't need the second entity.
MembersTable
MemberId
MemberName
IsCaptain
Best wishes for the year to come
Bastard Programmer from Hell
|
|
|
|
|
hello guys... I have this stored proc which gets some values from table but in addition, it calculates some values like %age of result. The question is: how can I send this value <b>WITH</b> a column name like <i>Percantage</i>. Here is the query for stored proc but I dont know what to add
<pre lang="SQL">
CREATE PROCEDURE [dbo].[GetStudents] AS
SELECT
s.StudentId, s.FirstName,s.SurName,
( ((s.ObtMarks) / (s.TotalMarks)) * 100) --this is the value I want to send with column name
FROM Students s
GROUP BY
s.StudentId, s.FirstName,s.SurName
</pre>
-- modified 28-Dec-11 7:30am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
