need urgent help in T-SQL

-- TOP MEDIAN
BEGIN
DECLARE @.medvarcnt int
DECLARE @.medianValue float
DECLARE @.medianfield varchar(255)
DECLARE @.SQLSTR Nvarchar(800)
SET @.medianfield = 'Cluster_Top'

CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)

SET @.SQLSTR = ('INSERT #medianlist SELECT ' + @.medianfield + ' AS medianval FROM ' + @.result_table_name + ' ORDER BY

' + @.medianfield + ' DESC')
SET @.SQLSTR = CAST (@.SQLSTR AS NVARCHAR(800))
EXECUTE sp_executesql @.SQLSTR
SET @.medvarcnt = (SELECT COUNT(*) FROM #medianlist)

IF @.medvarcnt % 2 = 0
BEGIN
--even
line17 set @.medianValue = (SELECT SUM(medianval)/2 FROM #medianlist WHERE rid >=(@.medvarcnt/2) and rid <=

(@.medvarcnt/2)+1)
line19 set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
ELSE
BEGIN
--odd
set @.medianValue = (SELECT medianval FROM #medianlist where rid =(@.medvarcnt/2)+1)
set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
DROP TABLE #medianlist

END

[addedon]March 17, 2007, 7:31 pm[/addedon]i'm trying to create a median solution with stored procedure...and from the coding i post above, i encounter the error stating failure to change from varchar to float everytime i execute it...

i suspect its happen on line 17 and 18 ...T-SQL got it as varchar...and when i use it back as variable in line 19 (whereas it suppose to take it as a float value....) thus error occur....

make it simple...@.medianvalue should be an int (let's say 18) in line 17 and 18...but at line 19 ,system still take it as whole sentence in varchar...hope everyone can understand what i try to tell...

anyone expert can provide me with solution? thanx

Try this as a replacement for Line 19:

set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))

..and here's an explanation as to why you should do this:

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

You need to review the code sample that you provided for further occurrences of attempting to append an INT to an NVARCHAR - with a quick glance I spotted one more.

Chris

|||

yes, thanks for your solution, chris...it works perfectly right now.

i should be more observant next time.

|||

there's a problem again after undergone some testing...

i expect @.medianvalue to be equals to 5.50 (should be accurate until 2 decimal) but the result coming out is shown as 5.

is there anything wrong with my coding? i have try CAST the SUM value into FLOAT but it doesnt work...

For more information, data type of the field Top_Median that i'm going to update is FLOAT.

any help is very much appreciated.

|||

Could it be the datatype specified in this line that's causing the problem?
CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)
Chris

|||i've changed it to type float. This solve the problem , thanks again Chris.