Posted By

niaher on 09/23/10

Tagged

Versions (?)

Last Edited at 09/23/10 11:02am

Statistics

Viewed 4794 times

Favorited by 1 user(s)

Related snippets

Print all row values from any table using SQL

/ Published in: SQL

Here is the SQL code to print a row of from any table. All you have to do is modify the @tableName and @whereClause variables. Works only in MS SQL 2005+

Highlighted TextExpand

DECLARE @tableName varchar(100)
DECLARE @whereClause varchar(100)

SET @tableName = 'Customers'
SET @whereClause = 'WHERE id = 999'

DECLARE @table TABLE(id int identity(1, 1), name varchar(100))
INSERT INTO @table (name)
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@tableName)

DECLARE @i int
DECLARE @rowCount int
SELECT @i = MIN(id), @rowCount = MAX(id) FROM @table

DECLARE @rowString varchar(MAX)
SET @rowString = ''

WHILE (@i <= @rowCount)
BEGIN
DECLARE @columnName varchar(100)
SELECT @columnName = name FROM @table WHERE id = @i

DECLARE @columnValueTable TABLE(name varchar(100))
INSERT INTO @columnValueTable
EXEC ('SELECT ' + @columnName + ' FROM ' + @tableName + ' ' + @whereClause)

DECLARE @columnValue varchar(100)	
SELECT @columnValue = name FROM @columnValueTable

IF (@columnValue IS NULL) SET @columnValue = 'NULL'

SET @rowString = @rowString + @columnName + ': ' + @columnValue + CHAR(10)

SET @i = @i + 1
END

-- You can also use SELECT instead of PRINT.
PRINT @rowString

URL: http://www.lessonist.com/lessons/id/594/print-all-row-values-from-any-table-using-sql

Report this snippet Tweet