November 7, 2011 at 1:11 pm
hi
i am converting function into sp,can i execute like this exec proc proc_name 'parm1' 'parm2' order by column.
if not how to achieve this utilities
Thanks
November 7, 2011 at 1:15 pm
I dont think so. What you can probably do is a pass in the field that you want to order by as a parameter to the sp and use that parameter to order by in the T-SQL inside the sp.
November 7, 2011 at 1:37 pm
You can't do that directly like that. You could insert the results of your proc to a table variable first though.
declare @MyTable table
(
ValueColumn varchar(20),
SortColumn int
)
insert @MyTable
exec proc_name 'parm1', 'parm2'
select * from @MyTable order by SortColumn
The other approach of passing in an extra parameter for the sort column will work too but then you need to use dynamic sql in your proc. There is nothing wrong with dynamic sql but the temp table approach seems simpler to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 1:52 pm
I agree with Sean. Table Variable approach is simpler.
November 7, 2011 at 2:02 pm
You could also pass in the parameter (sort column) into the actual procedure itself (similar to how the infamous sp_whom2 stired-procedure works. It uses a little dynamic SQL but would accomplish what you need.
Ex:
ALTER PROCEDURE dbo.MyProc (
@Param1 int,
@Param2 varchar(25)
) AS
DECLARE @SQL NVARCHAR(250)
SET @SQL = 'SELECT Col1, Col2, Col3 FROM pub.[err-msg] ORDER BY ' + @Param2 + ' DESC'
EXEC sp_executeSQL @SQL
/*
exec MyProc 1, 'Col3'
*/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 7, 2011 at 2:03 pm
Well Duh, Sorry Sean...I only read the first part of your post when I went off and replied. It's going to be a long week...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 7, 2011 at 2:08 pm
MyDoggieJessie (11/7/2011)
Well Duh, Sorry Sean...I only read the first part of your post when I went off and replied. It's going to be a long week...
LOL. No worries. I can't count the time I have done that myself. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 8:25 pm
daveriya (11/7/2011)
i am converting function into sp,can i execute like this exec proc proc_name 'parm1' 'parm2' order by column.
Given that the best solution is to insert into table variable and then sort, why convert the function to a procedure in the first place?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 2:21 am
SQL Kiwi (11/7/2011)
daveriya (11/7/2011)
i am converting function into sp,can i execute like this exec proc proc_name 'parm1' 'parm2' order by column.Given that the best solution is to insert into table variable and then sort, why convert the function to a procedure in the first place?
Or modify the stored procedure to have the Order By column on the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 3:05 am
GilaMonster (11/8/2011)
SQL Kiwi (11/7/2011)
daveriya (11/7/2011)
i am converting function into sp,can i execute like this exec proc proc_name 'parm1' 'parm2' order by column.Given that the best solution is to insert into table variable and then sort, why convert the function to a procedure in the first place?
Or modify the stored procedure to have the Order By column on the query?
Yes that would work; perhaps the reason they want to move from function to procedure is so they can use dynamic SQL to generate the right ORDER BY. Who knows.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 9:33 am
how can i used order by, my function is like
select * from function_name('dae','account_name') order by 1,5
now the content of function is
select order 2, book_id,table_name,account_id,account_name
from book,account
where book.id = account.id
union all
select order 1,column1,column2
from table1,table2
where-----
union all
select order3,column1,column2
from table1,table2
where-----
union all
select order4,column1,column2
from table1,table2
where-----
now i need to create sp,i can use union all like the same above statement,but what about order by
please help me
November 8, 2011 at 9:46 am
Do you always want it in the same order? If so, just add an order by to your query.
FWIW, You should order by column name and not ordinal position. When you do a union the column names from the first select will be the names of the columns.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2011 at 10:34 am
Sean
Is there any advantage to order by columnname over ordial position ?
November 8, 2011 at 11:24 am
no.i want to use order by 1,5,do i need to include it in every select statement. i dotn know how to use it,coz every select stm has order by ,and i wanto to display as order by 1,5
November 8, 2011 at 11:27 am
pparlapalli (11/8/2011)
SeanIs there any advantage to order by columnname over ordial position ?
It is certainly easier to read and if you change the column order at some point your order by either has to be changed or it is no longer correct.
Here is an example of how ordinal position might drive you nuts:
select Std_Set_Cost, Carry_Wgt, Ord_Plcy_Code, UP_DscMku_Code, Cnt_Solvent, Prod_Methd, Hide_Item, Mod_Time, Rec_Stat, Item_No, Rec_Id, MSDS_OnCD, Decription, Form_Number, List_Price, Unit_Cost, UM, Item_Class, Value_Class, Advance_Navison, MetricUM, Like_Number, Calc_MPV, Loss_Fact, Plan_No, Dept_No, Form_Weight, Tax_Code1, Tax_Code2, Tax_Code3, Tax_Code4, Pack_Code, Mod_Date, HMIS_Code, Special_Code, Sub_Class, Group_Code, MSDS_Req, Item_In_Cat, Cat_Pge_No, Case_Qty
from TableWithSomeColumns
order by 14, 12, 11, 9, 8, 5, 22, 19
So what is the order by on that??? EEEWWWWWWW!!!!!!!!!!!!
From a code perspective there really is not advantage, but from the perspective of somebody who has to come along later and decipher code it is very obvious why ordinal position is not the best idea. Take the above example and change the select list order...
select Rec_Stat, Item_No, Rec_Id, MSDS_OnCD, Decription, Form_Number, List_Price, Unit_Cost, UM, Item_Class,
Tax_Code1, Tax_Code2, Tax_Code3, Tax_Code4, Pack_Code, Mod_Date, HMIS_Code, Special_Code, Sub_Class,
Std_Set_Cost, Carry_Wgt, Ord_Plcy_Code, UP_DscMku_Code, Cnt_Solvent, Prod_Methd, Hide_Item, Mod_Time, Group_Code, MSDS_Req, Item_In_Cat, Cat_Pge_No, Case_Qty,
Value_Class, Advance_Navison, MetricUM, Like_Number, Calc_MPV, Loss_Fact, Plan_No, Dept_No, Form_Weight
from TableWithSomeColumns
order by 14, 12, 11, 9, 8, 5, 22, 19
Oops, the ordinal position is totally useless now because they all changed. What were the business rules for sorting this data again?? See where this is going? This is certainly a somewhat extreme example but the time saved by using ordinal position is totally lost the first time you have to revisit the original query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply