Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2006
    Posts
    5

    Unanswered: newbie stored procedure question

    I have a query that select rows from the employees,salary_head and salary_group tables
    this is the query
    SELECT TOP 100 PERCENT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
    dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
    dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
    FROM dbo.employees INNER JOIN
    dbo.salary_head INNER JOIN
    dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
    dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
    dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
    ORDER BY dbo.grade_level.level_no DESC, dbo.salary_head.salary_group_id

    i also have a table called payrollers1 with the following fields
    payroll_id int auto
    period_id int
    employee_id
    level_id
    designation_id
    banks_id
    bankaccount_no
    salarygroup_id
    Amount
    I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table.
    As for the period_id i want the Stored procedure to look up the max payperiod_id value in the payperiod table.
    I am totally new to stored procedure and do not know how to write this code.
    Can somebody help me with this code.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    One thing I notice is that TOP 100 PERCENT is meaningless. Why is that in your query?

    I suspect that it's there because this query is really a view definition, and you had to put it there because of the ORDER BY clause in your view definition. If that's the case, you should remove both the TOP and ORDER BY clauses.

    The ORDER BY clause belongs in the query that targets the view, not in the view definition itself. You are not guaranteed that it will come back ordered from the view even with the clause there. Sql server rerserves the right to return data in any order it sees fit unless you explicitly put an order by clause in the query.

    Your code will break randomly if you use order by in a view.

  3. #3
    Join Date
    Oct 2006
    Posts
    5
    Thanks.Actually you are right this was copied from a view i created.
    Please ignore the top 100 percent and the order by clause
    All i want is a stored procedure code that will run the above query and insert all the rows into the payroller table.
    Regards,
    charles

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by jezemine
    One thing I notice is that TOP 100 PERCENT is meaningless. Why is that in your query?

    I suspect that it's there because this query is really a view definition, and you had to put it there because of the ORDER BY clause in your view definition. If that's the case, you should remove both the TOP and ORDER BY clauses.

    The ORDER BY clause belongs in the query that targets the view, not in the view definition itself. You are not guaranteed that it will come back ordered from the view even with the clause there. Sql server rerserves the right to return data in any order it sees fit unless you explicitly put an order by clause in the query.

    Your code will break randomly if you use order by in a view.
    You sure about this? I don't mean to sound like some know-it-all, but I've simply not experienced what you've described. What version / service pack are you running?

    TOP 100 PERCENT is meaningless, but also harmless.

    ORDER BY works just fine. I've used it thousands of times and, except that it's disallowed in certian subquery situations, it's never failed to order the results.

    I almost invariably begin my stored procedures by dropping in the SQL created by a NEW VIEW creation. It's a wonderful tool. Have you encountered difficulty in doing things that way? It's a bit verbose because it always uses fully extended names, but that's OK.

    In addition, when analysing Stored Procedure problems, I almost invariably (when possible) open a 2nd instance of Enterprise Manager and cut-n-past some query portion of the SP into a NEW VIEW (and replacing the declared fields in the WHERE clause with actual values). Sometimes it will replace empty field names with "Expr1, Expr2, etc" but otherwise it's never (in 4 years of intense SQL development) tripped me up. I don't usually cut-n-paste in the other direction (on existing queries) because it creates odd WHERE clauses if you have a lot of AND and OR clauses, but it provides a wonderful graphic view of the problem. When 10 or 20 tables are involved, its almost a necessity.

    I don't think I would ever critisize someone for using Enterprise Manager as a query building tool. Obviously; many queries require modification in the Stored Procedure - for example, for adding CASE statements.

    To the OP: Create a Favorite to the MSDN for "Transact SQL Reference", (click here) and look at the INSERT syntax. It is irreplacable. You can use a SELECT command to do a 1-step-insert of many rows. It should be a constant reference while you are developing. This is more elegant that creating a cursor and looping thru them 1 at a time - and far more efficient performance-wise (like 20 times faster).
    Last edited by vich; 10-12-06 at 03:08.

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    To answer your question more directly - I did a little cut-n-pasting. I think this will work.

    Code:
    INSERT payrollers1
    	(employee_id,
    	level_id,
    	designation_id,
    	salary_group_id,
    	amount)
    SELECT 
    	employee_id,
    	level_id,
    	designation_id,
    	salary_group_id,
    	amount
    FROM dbo.employees INNER JOIN
    	dbo.salary_head INNER JOIN
    	dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON 
    	dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
    	dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
    ORDER BY dbo.grade_level.level_no DESC, dbo.salary_head.salary_group_id

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by vich
    You sure about this? I don't mean to sound like some know-it-all, but I've simply not experienced what you've described. What version / service pack are you running?
    It may work in 2000 sometimes, or even most of the time. The point is you shouldn't rely on it. The query optimizer reserves the right to ignore the order by if it's in a view definition.

    Here's what the dev lead of the query optimizer team has to say about it:
    http://blogs.msdn.com/sqltips/archiv...20/441053.aspx

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by jezemine
    It may work in 2000 sometimes, or even most of the time. The point is you shouldn't rely on it. The query optimizer reserves the right to ignore the order by if it's in a view definition.

    Here's what the dev lead of the query optimizer team has to say about it:
    http://blogs.msdn.com/sqltips/archiv...20/441053.aspx
    Thanks for the link.

    I think this is the prime statment you must be refering to:
    "INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted "

    After re-reading this about 5 times; I believe it would still result in an ordered table. If the identity_column has a guarenteed sequence to match the ORDERED BY clause used when populating it, then by definition it would be populated correctly. However; it may be physically out of sequence (appearently).

    I don't know enough about the SQL Server internals to understand that ramification. Perhaps a clustered index could be impacted, or, in filling the gaps from prior deletes, the optimizer may place things physically out-of-sequence from the logical sequence?

    Anyway; anyone really wanting a sorted result from the inserted table would include an order-by clause.

    The other statement I thought you might be refering to was:
    "If you have a TOP in the same SELECT block as an ORDER BY, any TOP computation is performed with respect to that ORDER BY. For example, if there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows within a given sort. Note that this does not guarantee that subsequent operations will somehow retain the sort order of a previous operation. The query optimizer re-orders operations to find more efficient query plans."

    I think this is saying that if a sub-query has a sort, and the top query doesn't have a sort - then don't count on the final result being sorted. However; top-level sorts will always be respected.

    In addition; the "TOP 5" from his example, if it's part of a sub-query (or VIEW), would still work properly since they're in the same BLOCK.

    I suppose that, since a VIEW is really a sub-query, that would apply if the VIEW is being used in ANY complex query (ie: any other table is JOINED with the VIEW).

    What this amouts to is - there's no point is using "ORDERED BY" in a SUB QUERY except in conjunction with TOP.

    Thank you for the lesson - I have learned something.

    In practice; I have noticed that when a VIEW feeds CRYSTAL REPORTS, it can (in rare cases) be about 5 times slower than if you remove the sort and let CRYSTAL do the sorting. Wonder if that's somehow related....?
    Last edited by vich; 10-12-06 at 04:08.

  8. #8
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    i think we have not yet addressed the question raised in the original post!!
    In GOD we believe. Everything else we Test!

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by vich
    I don't think I would ever critisize someone for using Enterprise Manager as a query building tool. Obviously; many queries require modification in the Stored Procedure - for example, for adding CASE statements.
    I would.

    Quote Originally Posted by vich
    i think we have not yet addressed the question raised in the original post!!
    If the poster has not studied enough SQL to know about INSERT INTO... SELECT... The help he requires is beyond my patience.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by vich
    Thanks for the link.

    I think this is the prime statment you must be refering to:
    "INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted "
    no. the statement I was referring to is right at the top of the page:
    http://blogs.msdn.com/sqltips/archiv...20/441053.aspx

    "For example, a common workaround to make the results from querying a view ordered is to introduce TOP 100 PERCENT and ORDER BY in the view definition. But this however does not guarantee order in the actual results sent to the client since the query optimizer will re-order operations to find more efficient query plans."

    for the original poster: look in BOL for INSERT INTO ... SELECT syntax. that will do what you want.

  11. #11
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by Thrasymachus
    I would.
    Why?

    You got a problem with pictorial representation? You like typing column and table names?

    I'm not saying you don't have to tweek it, sometimes majorly, after the fact. Even those tweeks sometimes require subqueries that are far easier to develop pictorially.

    Sure; Query Analyzer will supply the column and query names.

    The simpler queries don't need tweeking. The very complex ones benifit greatly from pictroial representation. I have several queries with 35 tables, the SQL is a full page of text. They work very well. If I needed to tune them, I'd have a very nice starting point.

    Now; I would critisize a programmer if that's the only thing they knew. However if a senior developer who knows very well the full syntax and ramification of SQL scripting decides to utilize this wonderful tool, more power to him - and, John Henry, he'll beat you every time in development speed.

    To be my own Devil's Advocate:
    All but the most simple queries should be run through the Query Analyzer to view the execution plan. If it takes a bad path, and you expect it to be used often or it's just too inefficient to allow even occassional use.
    Last edited by vich; 10-12-06 at 14:25.

  12. #12
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by wash
    i think we have not yet addressed the question raised in the original post!!
    What about post number 5? On a silver platter. Was there more to the request?

  13. #13
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by jezemine
    no. the statement I was referring to is right at the top of the page:
    http://blogs.msdn.com/sqltips/archiv...20/441053.aspx

    "For example, a common workaround to make the results from querying a view ordered is to introduce TOP 100 PERCENT and ORDER BY in the view definition. But this however does not guarantee order in the actual results sent to the client since the query optimizer will re-order operations to find more efficient query plans."

    for the original poster: look in BOL for INSERT INTO ... SELECT syntax. that will do what you want.
    I would trust that since he went on to explicitly clarify that statement, and that the very first thing on his list was saying that TOP 100 PERCENT and ORDER BY will work within that block, that (in his introductory statement) he was refering to a higher block depending upon that sequence being iffy.

    For example:

    Code:
    SELECT subquery.CustomerName  FROM
      (SELECT TOP 100 PERCENT CustomerName 
       FROM mytable 
       ORDER BY SalesTotal) subquery
    The result may not be returned orderd by SalesTotal, but usually is. If you added a bunch of other tables, the optimizer may re-sort the final result.

    However:
    Code:
    SELECT subquery.CustomerName  FROM
      (SELECT TOP 5 CustomerName FROM mytable ORDER BY SalesTotal) subquery
    The top 5 customers will still be returned, even though they may be unsorted, because the ORDER BY still works within the subquery block.
    Last edited by vich; 10-12-06 at 16:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •