SQL selects a row in a string variable without specifying columns

Question

I am new to SQL writing and really appreciate the help in solving this problem. :)

I am trying to select an entire line in a line, preferably separated by a space or a comma. I would like to do this in a general way, without knowing the features of the columns in the tables.

What I would like to do is:

DECLARE @MyStringVar NVARCHAR(MAX) = ''
@MyStringVar = SELECT * FROM MyTable WHERE ID = @ID AS STRING

But what I ended up with was the following:

DECLARE @MyStringVar = ''
DECLARE @SecificField1 INT
DECLARE @SpecificField2 NVARCHAR(255)
DECLARE @SpecificField3 NVARCHAR(1000)
...
SELECT @SpecificField1 = Field1, @SpecificField2 = Field2, @SpecificField3 = Field3 FROM MyTable WHERE ID = @ID
SELECT @StringBuilder = @StringBuilder + CONVERT(nvarchar(10), @Field1) + ' ' +  @Field2 + ' ' + @Field3

Ugh.: (

I saw some people posting material about the COALESCE function, but again, I did not see anyone using it without specific column names.

Also, I was thinking maybe there is a way to use column names by dynamically getting them through:

SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' 

It seems that it is not so difficult. :(

What I did now, but thanks in advance to everyone who can point me to the best solution. :)

EDIT: , , .:)

Answer 1

:

DECLARE @SQL nvarchar(MAX), @YourTable nvarchar(200)
SET @YourTable='YourTableName'
SELECT @SQL=
    STUFF(
             (
                  SELECT
                  ' + '','' + COALESCE(''''''''+CONVERT(NVARCHAR(MAX),['+c.COLUMN_NAME+']'+CASE WHEN c.DATA_TYPE='datetime' THEN ',121' ELSE '' END+')+'''''''',''null'')'
                      FROM INFORMATION_SCHEMA.COLUMNS c
                      WHERE c.TABLE_NAME = 'ap_voucher'
                      ORDER BY ORDINAL_POSITION
                      FOR XML PATH('')
             ), 1, 9, ''
         )
SET @SQL = 'SELECT ' + @SQL + ' FROM '+@YourTable
exec (@SQL)

:

---------------------------------------------------------------------------
'030',null,'I','Zzz0',null,'1000021111          ','2000-03-01 00:00:00.000'
'001',null,'I','zzz0',null,'900099618           ','1999-12-03 00:00:00.000'
'001',null,'I','ET10',null,'KOU557              ','1999-11-01 00:00:00.000'
'001',null,'I','Mzzz',null,'017288              ','1999-11-12 00:00:00.000'

Answer 2

, , :

DECLARE @nSQL NVARCHAR(MAX)
SELECT @nSQL = COALESCE(
     @nSQL + ' + '','' + CAST(ISNULL([' + c.COLUMN_NAME + '], '''') AS NVARCHAR(MAX))', 
    'CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX))')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'TestTable'

SET @nSQL = 'SELECT ' + @nSQL + ' FROM YourTable'

EXECUTE sp_executesql @nSQL

.

, (). NULL 0. ,

Answer 3

: .

, , , .

SELECT @MyField1 = MyField1, @MyField2= MyField2...etc FROM MyTable

:

SELECT @MyResult = (SELECT @MyField1 + @MyField2 + MyField3...)

SELECT @MyResult

Answer 4

- , XML:

DECLARE @MyStringVar NVARCHAR(MAX) = ''
SET @MyStringVar = (SELECT * FROM MyTable WHERE ID = @ID FOR XML RAW)

SELECT @MyStringVar 

, , XML , SELECT @MyStringVar:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(@MyStringVar, '<row ', ''), '="', ' '), '" ', ', '), '"/>', '')

, , , - , , .

---

, , , . information_schema , .

Answer 5

, , KM.

• . , , , . , .

• / , . SQL Server , . , nvarchar (max) 2000 . 48K!

• . , , "", "" "". .

If you are using SQL Server 2008, I would recommend using SQL Server Change Tracking. If you are not using this, I would recommend purchasing a third-party audit tool. If this is also not an option, I would write the remote value to another table using a trigger. This, at least, forces you to ensure that the audit data schema is synchronized with the original schema.