SQL declare variable for a long and repetitive select

Question

I want to declare a variable and do the following :

Select     
cast (SUM(case when @Dummy is not null then 1 else 0 end)*1.0/443321 as decimal(10,5)) @Dummy    
FROM hellotable
WHERE @Dummy in ("100 different field names in hellotable")

However this is not working... Any help is appreciated.

basically i can copy and paste the same select over 100 times with different field names, but i dont think this is a good way to do this. — Kric, Jun 18 '13 at 21:13

score 0 · Answer 1 · answered Jun 18 '13 at 22:42

0

Try doing that by Dynamic SQL..

DECLARE @dummy sysname
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = 'SELECT  CAST (SUM(CASE WHEN '+ @Dummy +N' IS NOT NULL THEN 1
                       ELSE 0
                  END) * 1.0 / 443321 AS DECIMAL(10, 5)) '+ @Dummy +N'     
                FROM    hellotable
                WHERE   '+ @Dummy +N'  IN ( '' )'
EXEC (@SQL)

answered Jun 18 '13 at 22:42

Talasila

161
6

Thanks for helping. but there is the error :Must declare the scalar variable "@SQL". – Kric Jun 18 '13 at 22:55

score 0 · Answer 2 · answered Jun 19 '13 at 11:35

Try using temp tables to populate these with data and then user inner join with your main table instead of using IN in WHERE clause.

CREATE TABLE #TempTable (FieldFromHelloTable nvarchar(50))

INSERT INTO #TempTable
VALUES ('value1'),('value2'),('value3'),('value4'),('value5')

SELECT * --this is oversimplified to ilustrate the point and you can modify this further
FROM HelloTable H
inner join #TempTable Tmp on H.SomeField = Tmp.FieldFromHelloTable

SQL declare variable for a long and repetitive select

2 Answers2