Populate XML Using Table Data

Declare @XML XML

set @XML = ( Select * from pubs..authors Authors FOR XML AUTO, ELEMENTS )

Select @XML

Populate Table Variable Using XML

DECLARE  @tempTable TABLE ( userId INT, userName NVARCHAR(50), password NVARCHAR(50) )

DECLARE  @xml XML

SET @xml=' <row userId="67" userName="Kenny1" password="1234" /> <row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable

SELECT Tbl.Col.value('@userId', 'INT'),

Tbl.Col.value('@userName', 'NVARCHAR(50)'),

Tbl.Col.value('@password', 'NVARCHAR(50)')

FROM @xml.nodes('//row') Tbl(Col)



SELECT * FROM @tempTable

Insufficient result space to convert uniqueidentifier value to char

Got the above obscure error message whilst dynamically generating and then executing a case statement where I was casting the fields to varchar using:

'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar)',

The above code was working fine until the column that had been added was a GUID.

Solution:
Simple, specify the length of the varchar!  Now works fine..
'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar(40))',

Creating Dynamic Column List

Scenario:

Wish to query a table to see which rows have changed (basically an audit on the table) so the query involves a self join. The where clause needs to be created based on the columns available in the table - we can do this dynamically...

This solution was also modified to generate a Case statement using the columns in a table dynamically (solution 2)

Solution 1:

Nice piece of code... creates the WHERE clause of a dynamic query using the columns on the table

DECLARE @match_str nvarchar(max)

SET @match_str = '(' + CHAR(10) + '0=1' + CHAR(10)

-- selects our comparison column namesSELECT @match_str = @match_str + 'OR '
+ 'dd.[' + c.[name] + '] != ii.[' + c.[name] + '] OR ' + '(dd.[' + c.[name] + '] IS NULL AND ii.[' + c.[name] + '] IS NOT NULL) OR ' + '(dd.[' + c.[name] + '] IS NOT NULL AND ii.[' + c.[name] + '] IS NULL)' + CHAR(10)
FROM  sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.TradeEntry') AND c.[name] != 'TradeEntryId' AND c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion') AND c.system_type_id != 241 AND fi.[_identity] IS NULL AND
iu.[_identity] IS NULL

SET @match_str = @match_str + ')'

select '@match_str: provides column joins for comparisons to find changed fields later on'

select @match_str

 

Solution 2:

SELECT c.[name],column_id, 'when ''' + c.[name] + ''' then cast(a1.' + c.[name] + ' as varchar)', 'when ''' + c.[name] + ''' then cast(a2.' + c.[name] + ' as varchar)'
FROM  sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.TradeEntry') AND
c.[name] != 'TradeEntryId' AND
c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion', 'ProcessingDateTime') AND
c.system_type_id != 241 AND
fi.[_identity] IS NULL AND iu.[_identity] IS NULL

declare @caseStatement1 varchar(max)
select @caseStatement1 = coalesce(@caseStatement1 + ' ' + caseStatement1, 'case (colName) ' + caseStatement1)
from @cols

Retrieve a SQL Column based on its Ordinal Value

Scenario:

We have a columns ordinal position and want to retrieve the data based on this value. Not something we really want to do - but out of interest, how can it be done?

Solution:

/* find a column based on its ordinal value) -- handy keep me*/declare @col1 as varchar(128)
select @col1 = column_name from information_schema.columns where table_name = 'Trade'and ordinal_position = 2 --@position
select @col1

Note, to retrieve the column ordinal in the first place select column_id (ie the ordinal value) from sys.columns.. like this:
SELECT c.[name] , column_id
FROM sys.columns c
LEFT JOIN [develop].[internal_fields] fi ON fi.[field_name] = c.[name]
LEFT JOIN [meta].[audit_internal_udfs] iu ON iu.[column_name] = c.[name]
WHERE c.[object_id] = OBJECT_ID( 'dbo.Trade') AND c.[name] != 'TradeId' AND c.[name] NOT IN ( 'CreateDate', 'ChangeDate', 'RowVersion', 'ProcessingDateTime') AND c.system_type_id != 241 AND fi.[_identity] IS NULL AND iu.[_identity] IS NULL

Alternative to SQL INSERT Statements

Scenario: Typical insert statement such as the following example,,,

USE TestDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO

The clause INSERT INTO is repeated multiple times. Many times DBAs copy and paste it to save time. There is another alternative to this, use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference.

Solution:

USE TestDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

The effective result is same.

Limitations of Calculated Columns

Some limitations of calculated columns:

- You cannot reference a value in a row other than the current row (as opposed to Excel for example).
- You cannot reference a value in another list or library.

- You cannot reference lookup fields in a formula (see earlier blog)

- You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.