stored procedures
-
hi i want to be able to give the user the option to select either Top 10, Top 20 or Top 30 from a result set. In my page they select from a drop down list and it must produce the graph accordingly, I would like to make 'Top' figure to be a variable so i only have to do one stored procedure.Not one for each Top 10, Top 20 or Top 30. Is this possible? Or do i have to create a stored procedure for each and then just call the appropriate procedure in my code? Thanks
You can have a stored procedure which builds dynamic sql according to user input and executes it. There can be an easier and/or more convenient way but I'm not aware of.
#region signature my articles #endregion
-
You can have a stored procedure which builds dynamic sql according to user input and executes it. There can be an easier and/or more convenient way but I'm not aware of.
#region signature my articles #endregion
Giorgi Dalakishvili wrote:
There can be an easier and/or more convenient way but I'm not aware of.
I don't think there is. He can just have a parameter into the stored proc to specify how many top values he wants to retrieve. That would be my approach.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Giorgi Dalakishvili wrote:
There can be an easier and/or more convenient way but I'm not aware of.
I don't think there is. He can just have a parameter into the stored proc to specify how many top values he wants to retrieve. That would be my approach.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Paul Conrad wrote:
He can just have a parameter into the stored proc to specify how many top values he wants to retrieve. That would be my approach.
I also thought about that but top clause doesn't accepts variables, you can only specify integers
#region signature my articles #endregion
-
Paul Conrad wrote:
He can just have a parameter into the stored proc to specify how many top values he wants to retrieve. That would be my approach.
I also thought about that but top clause doesn't accepts variables, you can only specify integers
#region signature my articles #endregion
I forgot about
TOPnot allowing any variables."Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
I forgot about
TOPnot allowing any variables."Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Just found out that in sql server 2005 TOP supports variables so you can write a parametrized sproc.
#region signature my articles #endregion
-
Just found out that in sql server 2005 TOP supports variables so you can write a parametrized sproc.
#region signature my articles #endregion
That is interesting, because I tried and it complained that it wasn't valid.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
That is interesting, because I tried and it complained that it wasn't valid.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Did you put braces around the variable?
#region signature my articles #endregion
-
Did you put braces around the variable?
#region signature my articles #endregion
I just tried and it works! Wow, I learned something new today :laugh:
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
I just tried and it works! Wow, I learned something new today :laugh:
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Paul Conrad wrote:
Wow, I learned something new today
I also learned something new :)
#region signature my articles #endregion
-
hi i want to be able to give the user the option to select either Top 10, Top 20 or Top 30 from a result set. In my page they select from a drop down list and it must produce the graph accordingly, I would like to make 'Top' figure to be a variable so i only have to do one stored procedure.Not one for each Top 10, Top 20 or Top 30. Is this possible? Or do i have to create a stored procedure for each and then just call the appropriate procedure in my code? Thanks
-
Paul Conrad wrote:
Wow, I learned something new today
I also learned something new :)
#region signature my articles #endregion
Okay, so as far as I understand it, having variable SQL queries in a stored procedure (except the obvious WHERE clause exception) is generally a good way to screw up the execution plan... So, seeing your responses to the question, why didn't you just do the following? I'm using the Northwind Database as the test DB.
CREATE PROC TestIdead @number smallint AS if @number = 10 begin select top 10 * from Customers end else if @number = 20 begin select top 20 * from Customers end else if @number = 30 begin select top 30 * from Customers end
In hindsight, you could probably reduce the IF...ELSE stuff down to a CASE statement as well... Just wondering why you opt for dynamic SQL queries rather than a logic structure that would result in a faster executing stored procedure (because the queries are static and thus don't modify the execution plan)? I'm a bit new to SQL and stored procs, so please enlighten me. :) -
Okay, so as far as I understand it, having variable SQL queries in a stored procedure (except the obvious WHERE clause exception) is generally a good way to screw up the execution plan... So, seeing your responses to the question, why didn't you just do the following? I'm using the Northwind Database as the test DB.
CREATE PROC TestIdead @number smallint AS if @number = 10 begin select top 10 * from Customers end else if @number = 20 begin select top 20 * from Customers end else if @number = 30 begin select top 30 * from Customers end
In hindsight, you could probably reduce the IF...ELSE stuff down to a CASE statement as well... Just wondering why you opt for dynamic SQL queries rather than a logic structure that would result in a faster executing stored procedure (because the queries are static and thus don't modify the execution plan)? I'm a bit new to SQL and stored procs, so please enlighten me. :)You don't need all those IF...Else stuff. Here is a sproc which works for all possible values
CREATE PROC TestIdead @number smallint AS select top (@number) from Customers#region signature my articles #endregion
-
You don't need all those IF...Else stuff. Here is a sproc which works for all possible values
CREATE PROC TestIdead @number smallint AS select top (@number) from Customers#region signature my articles #endregion
hehe... Unfortunately, I can't test that as I'm running against an SQL2000 server... and my SQL needs to be backwards compatible so... No variables in TOP statement for me! Also, you didn't answer the question: Will the stored procedure I wrote run as fast or faster than the solution which uses the dynamic SQL query?