Optimizing use of SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Optimizing use of SELECT

Author

Topic

allanurban
Starting Member

21 Posts

Posted - 2012-08-08 : 13:05:56

Hello all

The below code gets a number @var1 from tabel1 based on @stat. It then uses that number to calculate the avg and stdev for var1 days from col1 in tabel1. It returns @var1, avg, stdev and also the last observation (col1 from tbl2).


	DECLARE @stat varchar(64)
	SET @stat = 'stat1'

	DECLARE @var1 smallint
	SELECT @var1 = col1 
	FROM tabel1
	WHERE stat = @stat

	SELECT TOP(@var1) col1
	INTO #tbl1
	FROM tabel1
	WHERE stat = stat1
	ORDER BY date DESC

	DECLARE @avg FLOAT, @stdev FLOAT
	SELECT @avg = AVG(col1)
	      ,@stdev = STDEV(col1) 
	FROM tbl1
	
	SELECT TOP(1) col1
	INTO #tbl2
	FROM #tbl1

	SELECT @var1 as name1
	      ,@avg as name2
	      ,@stdev as name3
	      ,col1 as name4
	FROM #tbl2

	DROP TABLE #tbl1, #tbl2

This works like it is supposed to, but it looks a bit messy with all those selects. Any ideas how to clean up the above while keeping it easy to read?

I tried the following, but got the error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


	SELECT @var1 as name1
	      ,@avg = AVG(tbl1.col1)
	      ,@stdev = STDEV(tbl1.col1) 
	      ,tbl2.col1 as name4
	FROM #tbl1, #tbl2

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 13:35:02

I couldn't quite follow the rules that you are trying to implement. So this probably won't help much. However, I have two comments:

a) You appear to be mixing scalar variables and data from tables. If you have a query such as the following, it will assign the col1 value from a single random row in tabel1 to @var1. That is probably not what you wanted.

	DECLARE @var1 smallint
	SELECT @var1 = col1 
	FROM tabel1

b) Do you have four tables - two base tables tabel1 and tbl1 and two temp tables #tbl1 and #tbl2? Not clear from the queries whether that is the case, or whether it is just typed incorrectly.

allanurban
Starting Member

21 Posts

Posted - 2012-08-08 : 14:16:05

@ a)
That is definately not what I wanted. But wont it pick the correct row where stat = @stat with:

WHERE stat = @stat

?

@ b)
It is a bit messy I am afraid. Yes I do have 4 tables. It is a typo. The first query should be

FROM tabel2

So the two base tables are tabel1 and tabel2 and the temps are as you mention, #tbl1 and #tbl2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources