Return String for a select statement

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

Return String for a select statement

Author

Topic

baziukj
Starting Member

2 Posts

Posted - 2008-12-15 : 16:43:39

Ok, does anyone know how I would have my SQL query output as a formatted string instead of as a tables.

i.e.

SELECT ComputerName
FROM Devices
WHERE DateLastChanged > GETDATE() - 30

Would return a string that looks like:
'[comp1]', '[comp2]', '[comp3]', ...

And I do need the single quote ' around the computer names

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-12-15 : 21:10:01

http://www.sqlteam.com/item.asp?ItemID=11021

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-15 : 23:17:09

select '''['+ ComputerName +']''' FROM Devices
WHERE DateLastChanged > GETDATE() - 30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 23:28:16

you want it as a comma separated list. then use this


DECLARE @CompList varchar(8000)
SELECT @CompList=COALESCE('''['+@CompList +']''','') + ','
FROM Devices
WHERE DateLastChanged > GETDATE() - 30
SELECT LEFT(@CompList,LEN(@CompList)-1)

baziukj
Starting Member

2 Posts

Posted - 2008-12-16 : 15:46:16

Great! This got me goin'

Final results:

DECLARE @String varchar(8000)

SET @String = ''

IF @LowerDate = ''
BEGIN
SET @LowerDate = GETDATE()-30
END

SELECT
@String = COALESCE(@String, '') + CmpNme.ComputerName
FROM
(
SELECT DISTINCT
CASE
WHEN ComputerName = ''
THEN '''%' + SerialNumber + ''', '
ELSE '''' + ComputerName + ''', '
END AS [ComputerName]
FROM wdDevices
LEFT JOIN wdStatus
ON wdDevices.StatusID = wdStatus.StatusID
LEFT JOIN dbo.fnComputerDetails() AS fnComputerDetails
ON wdDevices.DeviceID = fnComputerDetails.DeviceID
LEFT JOIN wdModels
ON wdDevices.ModelID = wdModels.ModelID
LEFT JOIN Sites
ON wdDevices.SiteID = Sites.SiteID
WHERE
wdStatus.StandardInventory = 0
AND wdModels.DeviceTypeID IN (11, 12, 13)
AND (wdStatus.Status = @Status OR @Status = 'Select an item')
AND (Sites.SiteManagerName = @SiteManager OR @SiteManager = 'Select an item')
AND (wdDevices.StatusChangedDate > @LowerDate
AND (wdDevices.StatusChangedDate < @UpperDate OR @UpperDate = ''))
) AS CmpNme
ORDER BY
CmpNme.ComputerName

IF LEN (@String) > 0
BEGIN
SET @String = LEFT (@String, LEN(@String) -1)
END

SELECT [Result] = @String

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources