SQL Server Performance

store proc

Discussion in 'General Developer Questions' started by Reddy, Aug 30, 2005.

  1. Reddy New Member

    I have tables
    Skill(skillID,skillName)
    Topic(TopicID,code,Descr).
    I am using the following select statement
    -------------------------------------------------------------------------------------
    select skillname,code,descr from skill s inner join topic t on s.skillid=t.skillid
    here is the result
    skillname code descr
    Computers --------A001A--Access Basic
    Computers --------A001B--Access Intermediate
    Communications-----B008---Business Writing
    Communications-----B009---Communications Skills
    Human Development----C003---Coping With Change
    Human Development----C004---Customer Service
    Human Development----C008---Time Management
    ---------------------------------------------------------------------------
    I want to display this result in my App but I dont want to repeat the skillname as in the result. It shud be like only 1 skillname, under that code & descr for tht skillname. There is a chance for skillname field to grow.
    How can i perform this automatically from database side,do i need to write any store proc, if so how?

    Thanks!
    "He laughs best who laughs last"

  2. ranjitjain New Member

    Do you want result to be like this:

    Computers --------A001A--Access Basic
    ? --------A001B--Access Intermediate
    Communications -----B008---Business Writing
    ? -----B009---Communications Skills
    Human Development----C003---Coping With Change
    ? ----C004---Customer Service
    ? ----C008---Time Management

    then you need to do it on app side as its presentation stuff.
    Else what do you want to show replacing ?.
    Or Else its quite possible to show computers with all skills appended in a row
  3. dineshasanka Moderator

    Rather than doing it at the database side it is better if you can do this from the client side as it is just a presentation of data.
  4. Madhivanan Moderator

    If you show this in Reports(Crystal Reports) you can use the option "Suppress If Duplicated"

    Madhivanan

    Failing to plan is Planning to fail
  5. Reddy New Member

    Ranjit
    That is what exactly i need.Replacing column shud be empty.
    Yeah I know tht its clearly a App developer job to do at client side but here my develoeprs are asking me to do that from a store proc.As I am not gud at store procs I thought this forum cud help me out.
    So so u mean tht its not possible from a store proc. Can any one help me for that.I appreciate ur help.

    Thanks!
    "He laughs best who laughs last"

  6. Adriaan New Member

    This would be one way of doing it:
    CREATE TABLE #T
    (MyID INT IDENTITY(1,1), SkillName VARCHAR(100), Code VARCHAR(10), Description VARCHAR(100))


    --Insert the data ordering by the skill name, so the identity values are 'grouped'.


    INSERT INTO #T (SkillName, Code, Description)
    select skillname,code,descr from skill s inner join topic t on s.skillid=t.skillid
    ORDER BY SkillName, Code


    UPDATE #T
    SET SkillName = ''
    WHERE EXISTS
    (SELECT MyID FROM #T AS T1
    WHERE T1.SkillName = #T.SkillName
    AND T1.MyID < #T.MyID)


    SELECT SkillName, Code, Description
    FROM #T
    ORDER MY MyID
    And if you need to suppress repeating values that are not ordered as nicely as that, use this:
    UPDATE #T
    SET SkillName = ''
    WHERE EXISTS
    (SELECT MyID FROM #T AS T1
    WHERE T1.SkillName = #T.SkillName
    AND T1.MyID < #T.MyID
    AND NOT EXISTS
    (SELECT MyID FROM #T AS T2
    WHERE T2.SkillName <> #T.SkillName AND T2.SkillName <> ''
    AND T1.MyID > #T.MyID AND T2.MyID > #T.MyID
    AND T2.MyID < T1.MyID))

    EDIT: The criteria in the nested correlated subquery were off.
  7. Reddy New Member

    Adrian
    Thanks a lot.
    I am able to create SP in this way as according to U, when exec the SP it says
    11 rows are effected
    8 rows are effected .
    How can I see the result as I needed,can u extend ur support for me.
    --------------------------------------------------------------------------------
    CREATE PROC test
    AS
    CREATE TABLE #T
    (
    MyID INT IDENTITY(1,1),
    SkillName VARCHAR(50),
    topicCode VARCHAR(50),
    topicDescription VARCHAR(50)
    )
    --Insert the data ordering by the skill name, so the identity values are 'grouped'.
    INSERT INTO #T
    (
    SkillName,topicCode,topicDescription
    )
    select skillname,topiccode,topicdescription from skill s inner join topic t on s.skillid=t.skillid
    ORDER BY SkillName,TopicCode
    UPDATE #T
    SET SkillName = ''
    WHERE EXISTS(SELECT MyID FROM #T AS T1 WHERE T1.SkillName = #T.SkillName AND T1.MyID < #T.MyID
    AND NOT EXISTS(SELECT MyID FROM #T AS T2 WHERE T2.SkillName <> #T.SkillName AND T2.SkillName <> ''
    AND T1.MyID > #T.MyID AND T2.MyID > T1.MyID))
    GO
    ----------------------------------------------------------------------------------------------





    Thanks!
    "He laughs best who laughs last"

  8. Madhivanan Moderator

    At the end use Select Statement
    After Last Go, write this

    Select * from #t

    Madhivanan

    Failing to plan is Planning to fail
  9. Adriaan New Member

    I put in an alternative UPDATE statement, in case the column where you want to hide the repeating values is not the one on which you're sorting.

    So you can use either UPDATE statement, but in any case end with the SELECT.
  10. Reddy New Member

    Thanks Adriaan and Madhavanan
    Its a gr8 job. I am really learning a lot from u guys.

    Thanks!
    "He laughs best who laughs last"

  11. Reddy New Member

    SP is working fine but I want to know whats happening inside, Can any pls bare with me and explain the where clause in the SP.

    Thanks!
    "He laughs best who laughs last"

  12. Adriaan New Member

    The first update query checks if there is another row (EXISTS) in the same table (SELECT from same table with an alias) that has the same SkillName but a lower MyID value: if so, it blanks out SkillName on the row with the higher MyID value.

    The second update query does the same check as the first, but in addition it checks that there is no other row in between that has a different value (ignoring blanks).
  13. Reddy New Member

    Thanks a lot Adriaan.

    Thanks!
    "He laughs best who laughs last"

  14. Reddy New Member

    This SP is working fine and serving my need but when it is used in the application it fires an error like "Invalid object #T).What can I do for that.
    I understand that the error is due to temporary table but why cant I use it in my application.

    Thanks!
    "He laughs best who laughs last"

  15. ranjitjain New Member

    Hi Reddy,
    Have you tried select * from #t after calling the SP then it will fire an error.
    You can only refer to that object only inside SP.
    Once in your application SP execution gets over you can't refer back to that temp object as it gets unreferenced once the SP execution gets over.
  16. jay_cee_25 New Member

    i have three tables named TacDatabaseHist, LocalterminalID and tmpTransPerTerminal<br />TacDatabaseHist(TranSource,TranType,TranDate,TranTime,Msgtype,TraceNo,PAN,BIN,Amount,MerchantID,TerminalID,ResponseCde,Visa1Description,NII,BIN2,TranDesc)<br />LocalTerminalID(SerialNo,TerminalID,TranDate,MerchantName,Addres1,Address2,Address3)<br />tmpTransPerTerminal(N3000,N2001,N1001,N501,N251,N101,N1,N0 Total)<br /><br />A brief history of this procedure. This procedure is made to generate reports for the POS terminals that you will see in department stores when you dont have any cash.<br />The procedure's objective is to get the total of all the POS terminal that have completed a transaction. Then it will group it by how many transaction a terminal have completed. For Example if there are 5 terminals that have 150 transactions and 10 Terminals have 550 transactions. The Report should display something like this:<br /><br />N3000<br />N2001<br />N1001<br />N501 - 10<br />N251<br />N101 - 5<br />N1<br />N0<br />TOTAL - 15<br /><br />The problem is, the procedure does not pass the if and else statement.The result only display the TOTAL.<br /><br />Here is a copy of the stored proc I am studying right now, I'm not the developer of tis stored proc but was assigned to repair the program. but it seems i need a lot help from you guys.tnx a lot!<br /><br />CREATE PROCEDURE SP_TransPerTerminal<br /><br />@pMonthFromnchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@pMonthTonchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@pErrorinteger OUTPUT<br /><br />AS<br /><br />DECLARE<br />@vSerialNonchar (10),<br />@vSumnumeric(9),<br />@vTOTALnumeric(9)<br /><br />BEGIN<br />set @pError = 0<br />DELETE FROM tmpTransPerTerminal<br /><br />INSERT INTO tmpTransPerTerminal(N3000,N2001,N1001,N501,N251,N101,N1,N0,TOTAL)<br />VALUES (0,0,0,0,0,0,0,0,0)<br /><br />UPDATE tmpTransPerTerminal <br />SET TOTAL =(SELECT COUNT(TacDatabaseHist.TerminalID) FROM TacDatabaseHist INNER JOIN LocalTerminalID <br /> ON TacDatabaseHist.TerminalID= LocalTerminalID.TerminalID<br /> WHERE left(TacDatabaseHist.Trandate,6) BETWEEN @pMonthFrom AND @pMonthTo)<br /><br />DECLARE curTransVolViaPOSSerial CURSOR FOR<br />SELECT DISTINCT SerialNo<br />FROM LocalTerminalID<br />where serialno &lt;&gt; '' and serialno &lt;&gt;'00000000'<br /><br />OPEN curTransVolViaPOSSerial<br /><br />FETCH NEXT FROM curTransVolViaPOSSerial INTO @vSerialNo<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /><br /><br /> SET @vSum = (SELECT COUNT(TacDatabaseHist.TerminalID) FROM TacDatabaseHist INNER JOIN LocalTerminalID <br /> ON TacDatabaseHist.TerminalID= LocalTerminalID.TerminalID<br /> WHERE left(TacDatabaseHist.Trandate,6) BETWEEN @pMonthFrom AND @pMonthTo AND LocalTerminalID.SerialNo = @vSerialNo)<br /><br />IF @vSum &gt; 3000 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N3000 = N3000 + 1<br />END<br />ELSE<br />IF @vSum BETWEEN 2001 AND 3000 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N2001 = N2001 + 1 <br />END<br />ELSE<br />IF @vSum BETWEEN 1001 AND 2000 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N1001 = N1001 + 1<br />END<br />ELSE<br />IF @vSum BETWEEN 501 AND 1000 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N501 = N501 + 1<br />END<br />ELSE<br />IF @vSum BETWEEN 251 AND 500 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N251 = N251+1<br />END<br />ELSE<br />IF @vSum BETWEEN 101 AND 250 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N101 = N101+1<br />END<br />ELSE<br />IF @vSum BETWEEN 1 AND 100 <br />BEGIN<br />UPDATE tmpTransPerTerminal <br />SET N1 = N1+1<br />END<br /><br /> FETCH NEXT FROM curTransVolViaPOSSerial INTO @vSerialNo<br /><br />END<br /><br />CLOSE curTransVolViaPOSSerial<br />DEALLOCATE curTransVolViaPOSSerial<br /><br /><br />END<br /><br /><br /><br />thanks a lot!
  17. Madhivanan Moderator

    Jay_cee_25,

    I already advised you to post your question as a new topic
    Dont ask question at the topic which has many replies

    Madhivanan

    Failing to plan is Planning to fail
  18. FrankKalis Moderator

    Yes, you'll have much better chances to get a good and quick solution to your problem, when you open a new thread instead of hijacking one. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page