Site Search:
Sign in | Join | Help
4Penny.net

SQL Server (T-SQL)

Comments and notes on SQL Server 2000, 2005, and T-SQL

August 2008 - Posts

  • for XML PATH

    One of the coolest things to come with SQL 2005 is the new 'for XML PATH' syntax. There are a lot of things that can be done with it, I'm going to demonstrate something moderately complex here. The task is to generate an XML file that has nested nodes. In other words, we want the following structure:

    <Receivable documentNumber="CREDT000000000010" documentDate="08/26/2008">
  <Type>Credit Adjustment</Type>
  <Amount>300.00000</Amount>
  <Items>
    <Item type="Statement" itemNumber="346.001">
      <AppliedAmount>55.00000</AppliedAmount>
      <UnappliedAmount>0</UnappliedAmount>
    </Item>
    <Item type="Statement" itemNumber="346.002">
      <AppliedAmount>11.00000</AppliedAmount>
      <UnappliedAmount>0</UnappliedAmount>
    </Item>
  </Items>
</Receivable>

    Note the nested 'Items' node. We achieve this using a nested SQL Select statement inside the main query, like this:

     select 
   'Credit Adjustment' as Type,
   ortrxamt as Amount,
   (
    select 
      case when apply.aptodcnm is null then 'Case' else 'Statement' end as <a href="mailto:'@type'">'@type'</a>,
      rtrim(isnull(apply.aptodcnm,'')) as <a href="mailto:'@itemNumber'">'@itemNumber'</a>,
      isnull(apply.apfrmaplyamt,0) as 'AppliedAmount',
      0 as 'UnappliedAmount'
     from RM20201 apply
     where  apply.Apfrdcnm = rm.docnumbr and rm.rmdtypal = apply.apfrdcty
     for xml path('Item') , type
   ) as 'Items'
  from RM20101 rm
  where rm.dex_row_id = 306
  for xml  path('Receivable')

    The 'type' clause in the nested select tells the code to output XML

    Posted Aug 26 2008, 06:22 PM by Steve Gray
    [Edit Tags]

  • Combined INSERT, UPDATE and SELECT statements 

    -- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT * 
    FROM   sysobjects 
    WHERE  name = N'f_4P_columnType')
 DROP FUNCTION f_4P_columnType
GO


 


CREATE FUNCTION f_4P_columnType 
 (@intColumnType as int,@intLength int, @intPrecision int , @intscale int)


 


-- select dbo.f_4P_columnType(56,0,0,0)


 


RETURNS varchar(20)


 


AS
BEGIN
declare @out varchar(20)


 


select @out = 
 case when @intColumnType = 56 then 'int'
  when @intColumnType = 48 then 'tinyint'
  when @intColumnType = 36 then 'uniqueidentifier'
  when @intColumnType = 52 then 'smallint'
  when @intColumnType = 60 then 'money'
  when @intColumnType = 45 then 'bit'
  when @intColumnType = 62 then 'float'



  when @intColumnType = 35 then 'text'
  when @intColumnType = 61 then 'datetime'
  when @intColumnType = 189 then 'timestamp'
  when @intColumnType = 239 then 'nvar(' + convert(varchar(4),@intLength) + ')'
  when @intColumnType = 231 then 'nvarchar(' + convert(varchar(4),@intLength) + ')'
  when @intColumnType = 173 then 'binary(' + convert(varchar(4),@intLength) + ')'
  when @intColumnType in (175,47) then 'char(' + convert(varchar(4),@intLength) + ')'
  when @intColumnType = 167 then 'varchar(' + convert(varchar(4),@intLength) + ')'
  when @intColumnType in (108,63) then 'numeric(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
  when @intColumnType in (106) then 'numeric(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
  else 'dunno - ' + convert(varchar(4),@intColumnType)
  end 


 


 return @out



 



END
GO
-- =============================================
-- v 1.5 
-- =============================================
IF EXISTS (SELECT * 
    FROM   sysobjects 
    WHERE  name = N'f_4P_columnTypeVB')
 DROP FUNCTION f_4P_columnTypeVB
GO



CREATE FUNCTION f_4P_columnTypeVB 
 (@intColumnType as int)



-- select dbo.f_4P_columnTypeVB(56)



RETURNS varchar(20)



AS
BEGIN
declare @out varchar(20)



select @out = 
 case when @intColumnType in(48,52, 56) then 'system.int64'
  when @intColumnType = 36 then 'GUID'
  when @intColumnType in(108,60,63,106) then 'double'
  when @intColumnType = 45 then 'boolean'
  when @intColumnType in (35,47,167, 175,231) then 'string'
  when @intColumnType = 61 then 'date'
  when @intColumnType = 104 then 'int16' --bit
  --when @intColumnType = 173 then 'binary(' + convert(varchar(4),@intLength) + ')'
  else 'dunno - ' + convert(varchar(4),@intColumnType)
  end 



 return @out


 


END
GO
-- =============================================
-- 
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_select' 
    AND    type = 'P')
    DROP PROCEDURE sp_select
GO



CREATE PROCEDURE sp_select 
-- sp_select 'groups'



@vchrTableName varchar(50)


 


AS



set nocount on
 
declare @vchrProcName varchar(100)
declare @vchrFieldName varchar(100)
declare @vchrFieldType varchar(100)
declare @vchrFieldTypeVB varchar(100)
declare @vchrParamName varchar(100)
declare @intColstat int
declare @vchrParamList varchar(1000)
declare @vchrSelectList varchar(1000)



select
 @vchrParamList = '',
 @vchrSelectList = '',
-- @vchrParamListVB = '',
-- @vchrParamListHelper = '',
-- @output = '',
 @vchrProcName = '_4P_' + upper(@vchrTableName) + '_SEL',
 @vchrTableName = upper(@vchrTableName)


 


print 'IF EXISTS (SELECT name '
print '    FROM   sysobjects '
print '    WHERE  name = N' + char(39) + @vchrTableName + char(39)
print '        AND type = ' + char(39) + 'P' + char(39) + ')'
print '    DROP PROCEDURE ' + @vchrProcName
print 'GO'
PRINT ''
print 'CREATE PROCEDURE ' + @vchrProcName
print ''



declare curFields CURSOR for
select syscolumns.name,
  dbo.f_4P_columnType (xusertype,length, xprec , xscale),
  dbo.f_4P_columnTypeVB (xusertype),
  char(64) + syscolumns.name,
  syscolumns.colstat
 from sysobjects
  left join syscolumns on syscolumns.id = sysobjects.id
 where sysobjects.name = @vchrTableName



-- sp_select 'groups'



DECLARE @vchrWhere varchar(1000)
set @vchrWhere = ''



OPEN curFields
FETCH NEXT FROM curFields INTO @vchrfieldName, @vchrFieldType, @vchrFieldTypeVB,@vchrparamName, @intColstat
WHILE @@FETCH_STATUS = 0
BEGIN
 --build a where clause
 if @intColStat = 1 begin
  set @vchrWhere = @vchrWhere  + '        ' + @vchrFieldName + ' = ' + @vchrparamName+ char(13)
  
  --build a param list (builds @name varchar(20) )
  set @vchrParamList = @vchrParamList + char(13) + @vchrparamName + ' ' + @vchrFieldType + ','
 end



 --build the 'set' list
 set @vchrSelectList = @vchrSelectList + char(13)  + space(8) + @vchrFieldName + ','



 FETCH NEXT FROM curFields INTO @vchrfieldName, @vchrFieldType,@vchrFieldTypeVB, @vchrparamName, @intColstat


 


END
CLOSE curFields
DEALLOCATE curFields



--remove the last char from the list
set @vchrParamList = left(@vchrParamList, len(@vchrParamList)-1)



--remove the first and last char from the list
set @vchrSelectList = stuff(@vchrSelectList,1,1,'')
set @vchrSelectList = left(@vchrSelectList, len(@vchrSelectList)-1)



print @vchrParamList
print ''
PRINT 'AS'
print ''
Print 'SELECT'
print @vchrSelectList
print '    FROM ' + @vchrTableName + ' WITH (NOLOCK)'



print '    WHERE'
print @vchrWhere
PRINT 'GO'
PRINT 'GRANT EXEC ON ' + @vchrProcName + ' TO PUBLIC'
-- sp_select 'groups'



go



grant exec on sp_select to public


go 
-- =============================================
-- 
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_insert' 
    AND    type = 'P')
    DROP PROCEDURE sp_insert
GO


CREATE PROCEDURE sp_insert 
--  sp_insert 'sop10101'


@vchrTableName varchar(50)



AS


 


declare @fieldName varchar(500)
declare @paramName varchar(255)
declare @fieldType varchar(50)
declare @fieldTypeVB varchar(50)
declare @intTableNameLen int
declare @vchrProcName varchar(50)
declare @vchrAlias varchar(2)
declare @vchrFieldList as varchar(8000)
declare @vchrFieldList2 as varchar(8000)
Declare @vchrParamList as varchar(8000)
Declare @vchrIntoList as varchar(8000)
declare @paramListHelper varchar(8000)
declare @paramListVB varchar(8000)



select @vchrTableName = upper(@vchrTableName)


select
    @vchrProcName = 'FP_' + @vchrTableName + '_INS',
    @vchrAlias = case when left(@vchrTableName,1) = '_' then substring(@vchrTableName,2,1) else left(@vchrTableName,1) end + '.',
    @intTableNameLen = len(@vchrTableName),
    @vchrFieldList = '',
    @vchrFieldList2 = '',
    @vchrParamList = '',
    @vchrIntoList = '',
    @paramListVB = '',
    @paramListVB = '',
    @paramListHelper = ''
    


select
    @vchrProcName = replace(@vchrProcName,'__','_')


print '-- ============================================='
print '-- '
print '-- ============================================='
print '-- 1/1/1900 created'
print 'IF EXISTS (SELECT name' 
print '    FROM   sysobjects '
print '    WHERE  name = N' + '''' + @vchrProcName + ''''
print '    AND    type = ''P'') '
print '    DROP PROCEDURE ' +  @vchrProcName 
print 'GO'
print 'CREATE PROCEDURE ' + @vchrProcName 
print ''


 


declare curFields CURSOR for
select syscolumns.name,
        dbo.f_4P_columnType(xusertype,length,xprec,xscale) as fieldType,
        dbo.f_4P_columnTypeVB (xusertype),
        char(64) + syscolumns.name
    from sysobjects
        left join syscolumns on syscolumns.id = sysobjects.id
    where sysobjects.name = @vchrTableName
        and colstat <> 1



OPEN curFields



FETCH NEXT FROM curFields INTO @fieldName, @fieldType, @fieldTypeVB, @paramName
WHILE @@FETCH_STATUS = 0
BEGIN
    if @fieldType <> 'text' begin
        set @vchrFieldList = @vchrFieldList + @fieldName + '  '  + ', '
        set @vchrFieldList2 = @vchrFieldList2 + @vchrAlias + @fieldName + ', '
        set @vchrParamList = @vchrParamList + char(64) + @fieldName + ' ' + @fieldtype + ',' + char(13) + char(10)
        set @vchrIntoList = @vchrIntoList + char(64) + @fieldName + ' , '


        --build a VB param list
        set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @fieldName + ' As ' + @fieldTypeVB  
    
        --build a sqlhelper param list
        set @paramListHelper = @paramListHelper + ', _ ' + char(13) + space(16) + 'New SqlParameter("' + @paramName + '", ' + @fieldName + ')'
    end
    FETCH NEXT FROM curFields INTO @fieldName, @fieldType,@fieldTypeVB, @paramName
END
CLOSE curFields
DEALLOCATE curFields



set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
set @vchrFieldList2 = left(@vchrFieldList2, len(@vchrFieldList2) - 1)
set @vchrParamList = left (@vchrParamList,len(@vchrParamList)-3)
set @vchrIntoList = left (@vchrIntoList,len(@vchrIntoList)-1)
set @paramListVB = stuff(@paramListVB,1,1,'')
--  sp_insert 'XPOTrans_HIST'



print @vchrParamList
print ''
print 'AS'
PRINT ''
 
print  'INSERT INTO ' + @vchrTableName + ' (' + @vchrFieldList + ')'
print  '    SELECT    ' + space(@intTableNameLen) + @vchrFieldList2 
print  '        FROM '



PRINT ''
PRINT ''
print 'INSERT INTO  ' + @vchrTableName + ' (' + @vchrFieldList + ')'
print space(len(@vchrProcName)) + 'values(' + @vchrIntoList + ')'
PRINT ''
PRINT 'GO'
print '' 
print 'grant exec on ' + @vchrProcName + ' to public'
print ''
print 'Public Sub ' + @vchrTableName + '_INS(' + @paramListVB + ') '
print '    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'



go


 


 


-- =============================================
-- 
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'sp_update' 
       AND    type = 'P')
    DROP PROCEDURE sp_update
GO


CREATE PROCEDURE sp_update 
-- sp_update 'sop10101'


@tablename varchar(50)



AS


set nocount on
    
declare @output varchar(8000)
declare @fieldTypeVB varchar(50)
declare @fieldName varchar(500)
declare @fieldType varchar(50)
declare @paramName varchar(255)
declare @paramList varchar(8000)
declare @paramListVB varchar(8000)
declare @paramListHelper varchar(8000)
declare @vchrProcName varchar(255),
    @vchrSetList varchar(8000),
    @intColStat int


set @output = ''


select
    @paramList = '',
    @vchrSetList = '',
    @paramListVB = '',
    @paramListHelper = '',
    @output = '',
    @vchrProcName = 'FP_' + upper(@tablename) + '_UPD',
    @tableName = upper(@tableName)



print 'IF EXISTS (SELECT name '
print '    FROM   sysobjects '
print '    WHERE  name = N' + char(39) + @tablename + char(39)
print '        AND type = ' + char(39) + 'P' + char(39) +