0

I want to calculate the median of a column, assign that to a dynamic variable, and use that in further calculations. This is what I've written:

declare @sqlcmd nvarchar(max)
declare @tablename nvarchar(max)
set @tablename = 'table1'

-- calculate median
declare @median float

set @sqlcmd =
'SELECT
((SELECT MAX(field1) FROM
(SELECT TOP 50 PERCENT field1 FROM ' + @tablename + ' Where field1 is not null        
ORDER BY field1) AS BottomHalf)
+
(SELECT MIN(field1) FROM
 (SELECT TOP 50 PERCENT field1FROM ' + @tablename + ' Where field1 is not null 
ORDER BY field1 DESC) AS TopHalf)
 ) / 2'
 execute sp_executesql @sqlcmd

 set @median = ???

-- calculate value2 as value 1 minus median
'update ' + @tablename +
' set value2 = value 1 - ' + @median
execute sp_executesql @sqlcmd

The result of my experimentation is either:

  • "Error converting data type nvarchar to float."
  • Or if I remove the second calculation, it simply pops up the result to the result window.

Using SQL Server 2012. Thank you in advance!

Shannon Cox
  • 1
  • 2

3 Answers3

0

Just convert your @median to VARCHAR when your are updating

SET @sqlcmd = 'update ' + @tablename +
              ' set value2 = value 1 - ' + CONVERT(VARCHAR(100), @median)
EXEC(@sqlcmd )
sqluser
  • 5,145
  • 7
  • 32
  • 48
  • Thanks. Now it is error free and the median value has been assigned to @median, but it did not set value2 as value - median and remains null... – Shannon Cox Apr 02 '15 at 15:59
0

Try to avoid dynamic SQL statements (concatenating a string of SQL and then executing it). That may be causing the "nvarchar to float" error. Declare your variables, then set them in select statements: 

DECLARE @max float
DECLARE @min float

SELECT @max = MAX(field1) FROM table1
SELECT @min = Min(field1) FROM table1

—- use @max and @min in other statements

Take a look at this question for ideas on how to get median in SQL Server.

You also may want to look at this question on how to get a result of dynamic SQL into a variable.

Community
  • 1
  • 1
JonH
  • 231
  • 4
  • 12
  • The reason I'm using the dynamic statement with@tablename is that I need to run this script on dozens of tables, so having one location to input the table name would greatly increase efficiency. On the link, it's the second answer that I am using to calculate the median and it works well. Just trying to figure out how to get the result of that select/calculation into a formula. Thanks – Shannon Cox Apr 02 '15 at 16:01
  • Thanks for clarifying Shannon Cox; the answer has been edited for this...see the second linked question/answer. – JonH Apr 06 '15 at 16:43
0

In the end I went with the strategy of passing the output of a select query into an update statement using a temporary table with the guidance of this article: https://smehrozalam.wordpress.com/2009/10/14/t-sql-using-result-of-a-dynamic-sql-query-in-a-variable-or-table/.

The result looks something like this:

declare @sqlcmd nvarchar(max)
declare @tablename nvarchar(max)

--insert tablename here
set @tablename = 'table1'

-- create temporary table to store median results to pass to equation
create table temptable (calc_Result(12,10))

-- calculate medians
insert into temptable exec
(N'SELECT
((SELECT MAX(field1) FROM
   (SELECT TOP 50 PERCENT field1 FROM results_' + @tablename + ' Where     field1 is not null ORDER BY field1) AS BottomHalf)
+
(SELECT MIN(field1) FROM
(SELECT TOP 50 PERCENT field1 FROM results_' + @tablename + ' Where field1 is not null ORDER BY field1 DESC) AS TopHalf)
) / 2 AS Field1_Median)')

-- calculate Field1 - Median
set @sqlcmd = 
'update results_' + @tablename +
' set Field1_Minus_Median = Field1 - (select Feild1_Median from temptable )'
execute sp_executesql @sqlcmd

Thank you for all the help!

Shannon Cox
  • 1
  • 2