July 24, 2013 at 1:37 pm
Or should I really be using a function? That's what I was attempting at first, but got pretty stumped on making it dynamic. I'm trying to take the code to return a median value form here ( http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx ) and turn it into a callable SP/FN. Or if anyone has another suggestion, I'm open to whatever.
Thanks
USE [Utilities]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
alter PROC [dbo].[ReturnMedian]
@median sysname,
@tablenm sysname
AS
declare @sql nvarchar (max)
set @sql = 'WITH MEDCTE (ID, MED)
AS (SELECT
Id,
AVG( ' + QUOTENAME(@median) + ')
FROM
(
SELECT
Id,
' + QUOTENAME(@median) + ',
ROW_NUMBER() OVER (
PARTITION BY Id
ORDER BY ' + QUOTENAME(@median) + ') AS RowNum,
COUNT(*) OVER (
PARTITION BY Id) AS RowCnt
FROM ' + QUOTENAME(@tablenm) + '
) x
WHERE
RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP BY Id
SELECT MED FROM MEDCTE
'
--EXEC (@SQL)
--PRINT (@SQL)
--SELECT (@SQL)
--declare @tempmed table (
--TEMPMEDFLOAT float
--)
--insert into @tempmed exec sp_executesql @sql, N'@sql float', @sql
--RETURN SELECT TEMPMEDFLOAT FROM @tempmed
July 24, 2013 at 2:36 pm
You could use something like this.
DECLARE @median sysname,
@tablenm sysname
declare @sql nvarchar (max),
@med decimal(15,2)
set @sql = 'WITH MEDCTE (ID, MED)
AS (SELECT
Id,
AVG( ' + QUOTENAME(@median) + ')
FROM
(
SELECT
Id,
' + QUOTENAME(@median) + ',
ROW_NUMBER() OVER (
PARTITION BY Id
ORDER BY ' + QUOTENAME(@median) + ') AS RowNum,
COUNT(*) OVER (
PARTITION BY Id) AS RowCnt
FROM ' + QUOTENAME(@tablenm) + '
) x
WHERE
RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP BY Id
SELECT @MEDOUT = MED FROM MEDCTE
'
EXECUTE sp_executesql @sql, N'@MEDOUT decimal(15,2) OUTPUT', @MEDOUT = @med OUTPUT;
July 24, 2013 at 3:39 pm
Thank you so much Luis. I wish you guys had tip jars sometimes.
I got your code to work, and then, when the egg cleared from my face, realized I needed different median fetching syntax. So now It looks like this, and performs nicely:
USE [Utilities]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
alter PROC [dbo].[ReturnMedian]
@median sysname,
@tablenm sysname
AS
declare @sql nvarchar (max),
@med decimal(15,2)
set @sql = 'WITH MEDCTE AS (
SELECT
(
(SELECT MAX(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ') AS BottomHalf)
+
(SELECT MIN(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ' DESC) AS TopHalf)
) / 2 AS Med
)
SELECT MED FROM MEDCTE
'
print (@sql)
EXECUTE sp_executesql @sql, N'@MEDOUT decimal(15,2) OUTPUT', @MEDOUT = @med OUTPUT;
Note that if I use this part of your syntax "SELECT @MEDOUT = MED FROM MEDCTE" instead of "SELECT MED FROM MEDCTE", it just tells me that my command was completed successfully. Any idea why?
The original syntax I found was selecting TOP 50 PERCENT, but in a column with 19938 0s, and numbers from 1 - 2508, it kept returning 0. When I changed it to 100 PERCENT it returned 1254. I found that strange, but maybe that's a topic for a different, well, topic?
Thank you again
July 24, 2013 at 3:51 pm
Also, when I run it like this, it works too. Now I'm very confused. I've run it on a whole mess of tables and received correct results.
USE [Utilities]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
alter PROC [dbo].[ReturnMedian]
@median sysname,
@tablenm sysname
AS
declare @sql nvarchar (max),
@med decimal(15,2)
set @sql = 'WITH MEDCTE AS (
SELECT
(
(SELECT MAX(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ') AS BottomHalf)
+
(SELECT MIN(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ' DESC) AS TopHalf)
) / 2 AS Med
)
SELECT MED FROM MEDCTE
'
--print (@sql)
--EXECUTE sp_executesql @sql, N'@MEDOUT decimal(15,2) OUTPUT', @MEDOUT = @med OUTPUT;
EXEC (@SQL)
July 25, 2013 at 7:51 am
erikd (7/24/2013)
Also, when I run it like this, it works too. Now I'm very confused. I've run it on a whole mess of tables and received correct results.
USE [Utilities]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
alter PROC [dbo].[ReturnMedian]
@median sysname,
@tablenm sysname
AS
declare @sql nvarchar (max),
@med decimal(15,2)
set @sql = 'WITH MEDCTE AS (
SELECT
(
(SELECT MAX(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ') AS BottomHalf)
+
(SELECT MIN(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ' DESC) AS TopHalf)
) / 2 AS Med
)
SELECT MED FROM MEDCTE
'
--print (@sql)
--EXECUTE sp_executesql @sql, N'@MEDOUT decimal(15,2) OUTPUT', @MEDOUT = @med OUTPUT;
EXEC (@SQL)
Be careful here. Both this version and the version with sp_executesql are vulnerable to sql injection. You have two parameters which are basically just a string. You then directly execute the string you build up. It would be tricky because your code would be really tough to guess but it is possible.
Consider just how close this is. I know I have the code to help me figure out the values but...
declare @median sysname = '1)d))a)select 1 from MEDCTE;select * from sys.objects/*',
@tablenm sysname = '*/--'
declare @sql nvarchar (max),
@med decimal(15,2)
set @sql = 'WITH MEDCTE AS (
SELECT
(
(SELECT MAX(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ') AS BottomHalf)
+
(SELECT MIN(' + @median + ') FROM
(SELECT TOP 100 PERCENT ' + @median + ' FROM ' + @tablenm + ' ORDER BY ' + @median + ' DESC) AS TopHalf)
) / 2 AS Med
)
SELECT MED FROM MEDCTE
'
print (@sql)
This was a quick attempt to see if it could be done. The only thing missing is 4 end comments at the very end. Below is the value of your dynamic string with the parameters I set up above.
WITH MEDCTE AS (
SELECT
(
(SELECT MAX(1)d))a)select 1 from MEDCTE;select * from sys.objects;/*) FROM
(SELECT TOP 100 PERCENT 1)d))a)select 1 from MEDCTE;select * from sys.objects/* FROM */-- ORDER BY 1)d))a)select 1 from MEDCTE;select * from sys.objects/*) AS BottomHalf)
+
(SELECT MIN(1)d))a)select 1 from MEDCTE;select * from sys.objects/*) FROM
(SELECT TOP 100 PERCENT 1)d))a)select 1 from MEDCTE;select * from sys.objects/* FROM */-- ORDER BY 1)d))a)select 1 from MEDCTE;select * from sys.objects/* DESC) AS TopHalf)
) / 2 AS Med
)
SELECT MED FROM MEDCTE
Ok so this will generate 4 errors but look at how close this really is to working.
Add this to the last line and it will execute just fine.
*/*/*/*/
_______________________________________________________________
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/
July 25, 2013 at 8:21 am
That's interesting, Sean. I don't think about injection much since I'm the only person who uses most of the code I write, and none of it is really ending up on web facing machines.
The code that does get used for our client portal is set up by me and isn't visible to or passed anything from clients. They see a happy little button to click and then get a HTML formatted table to look at.
So, yeah, it's entirely possible that someone could hack our client portal and gain high level access to screen editing and then use some SQL code maliciously. But I think at that point we'd have far more to worry about.
I've read 'the curse and blessings of dynamic SQL' by Erland Sommarskog. A few times. I'll probably read it 5 or 6 more times before I mostly understand it. Are there any other similar articles you'd recommend that address injection?
July 25, 2013 at 8:56 am
erikd (7/25/2013)
That's interesting, Sean. I don't think about injection much since I'm the only person who uses most of the code I write, and none of it is really ending up on web facing machines.The code that does get used for our client portal is set up by me and isn't visible to or passed anything from clients. They see a happy little button to click and then get a HTML formatted table to look at.
So, yeah, it's entirely possible that someone could hack our client portal and gain high level access to screen editing and then use some SQL code maliciously. But I think at that point we'd have far more to worry about.
I've read 'the curse and blessings of dynamic SQL' by Erland Sommarskog. A few times. I'll probably read it 5 or 6 more times before I mostly understand it. Are there any other similar articles you'd recommend that address injection?
If you would have asked me for an article that explains the usage of dynamic sql that would be the one I would point you to.
As for the injection vulnerability it is something you absolutely need to understand. The current code MAY be safe enough but just knowing that is there will help you next time you code something with dynamic sql.
_______________________________________________________________
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/
July 25, 2013 at 9:23 am
I have to admit that I really didn't look at the actual query you were running until ChrisM pointed it out to me. There seems to be a bit of strange stuff going on here.
I don't see the need for a cte, I don't see the need for the outermost select, in reality it seems to me that you have 6 select statement doing the work of 1.
Why bother with top 100 percent and order by when you are getting ALL the rows anyway?
If I deciphered this code correctly is looks like you are getting the middle value of a column in a table?
Doesn't this do the same thing?
declare @median sysname = 'median',
@tablenm sysname = 'tablenm'
declare @sql nvarchar (max)
set @sql = 'select (MAX(' + @median + ') + MIN(' + @median + ')) / 2 as Med from ' + @tablenm
print (@sql)
_______________________________________________________________
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/
July 25, 2013 at 9:57 am
You're right. Here's how it happened:
My original query, which I picked the median-getting part up from here ( http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx ), and thought fit the needs of the SP, didn't. I was under the impression that we'd be collecting medians from a number of places, but we only need it from one column (ID was a placeholder for ~whatever~ was going to be the final unique identifier column).
'WITH MEDCTE (ID, MED)
AS (SELECT
Id,
AVG( ' + QUOTENAME(@median) + ')
FROM
(
SELECT
Id,
' + QUOTENAME(@median) + ',
ROW_NUMBER() OVER (
PARTITION BY Id
ORDER BY ' + QUOTENAME(@median) + ') AS RowNum,
COUNT(*) OVER (
PARTITION BY Id) AS RowCnt
FROM ' + QUOTENAME(@tablenm) + '
) x
WHERE
RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP BY Id
SELECT MED FROM MEDCTE
'
So I had that, and then when it didn't fit the frame, I did some more searching and ran across this: ( http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server/2026609#2026609 )
SELECT
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
+
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median
So I tried it out with some of my data, and found that it wasn't exactly working:
The original syntax I found was selecting TOP 50 PERCENT, but in a column with 19938 0s, and numbers from 1 - 2508, it kept returning 0. When I changed it to 100 PERCENT it returned 1254. I found that strange, but maybe that's a topic for a different, well, topic?
But I was still wrapping it in a CTE because when I was trying to get it to return a result, I was in tunnel vision mode, and thought it would be somehow easier to assign the result to a variable, or insert it somewhere, or SOMETHING.
This is pretty typical of what happens to me when something isn't working, and I get desperate and start throwing every trick at it. Usually if I come back a bit later and look things over again I'll spot the issue. Like, just get it to work, and make it work better later. Then there are times when my boss tells me it's a rush but takes 3 hours to reply to any of my emails and I end up making a spectacle of myself on here 🙂
Thank you again for your time (and Chris M., who just might be the coolest cookie in the jar).
July 25, 2013 at 10:34 am
Actually, there seems to be a problem with the median formula.
This should perform better and give the correct results. It's up to you to change it to your needs.
DECLARE @test-2TABLE(
myintint)
INSERT INTO @test-2 VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);
WITH CTE AS(
SELECT myint,
NTILE( 2) OVER( ORDER BY myint) tile
FROM @test-2
)
SELECT CASE WHEN SUM( CASE WHEN tile = 1 THEN 1 END) = SUM( CASE WHEN tile = 2 THEN 1 END)
THEN (MAX( CASE WHEN tile = 1 THEN myint END) + MIN( CASE WHEN tile = 2 THEN myint END))/2.0
ELSE MAX( CASE WHEN tile = 1 THEN myint END) END
FROM CTE
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply