table variable - 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

table variable

Author

Topic

AKP2008
Starting Member

45 Posts

Posted - 2009-01-22 : 04:44:00

Hi,

Is it possible to use table variables in a view.

Actually my requirement is
If my select statement returns 10 rows i want to get the output as 10 rows + another 10 rows with empty data. I have to do this using view only. Please suggest me whether it is possible or not.

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-22 : 04:59:12

Don't think a table variable is possible to use in a view, but you can probably do something like this:

SELECT Column1, Column2, ... FROM sometable WHERE...
UNION ALL
SELECT NULL, NULL
UNION ALL
SELECT NULL, NULL
UNION ALL
...

Looks rather silly to be honest but it will work and hardly cause any overhead at all.

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 06:45:01

[code]SELECT CASE WHEN d.Original = 1 THEN t.Col1 ELSE NULL END AS Col1,
CASE WHEN d.Original = 1 THEN t.Col2 ELSE NULL END AS Col2,
...
FROM Table1 AS t
CROSS JOIN (
SELECT 1 AS Original UNION ALL
SELECT 0
) AS d
WHERE ...
ORDER BY d.Original DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 08:36:04

[code]
SELECT Column1, Column2, ... FROM yourquery
UNION ALL
SELECT NULL,NULL,.... FROM yourquery
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources