Blogs - sp

DEVSHED Blog

As always, I welcome your comments!

sp_NEW

Dec 10

Written by: Steve Gray
12/10/2010 12:48 PM

This is a helper routine for the Data Access scripts. It creates the ‘New’ signature for VB classes

Refer to the documentation here

Code Snippet
 -- =============================================
-- 
-- =============================================
-- updated 12/10/2010
 
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_NEW' 
    AND    type = 'P')
    DROP PROCEDURE sp_NEW
GO
 
CREATE PROCEDURE sp_NEW 
 
@ProcName varchar(255),
@Table varchar(255),
@DataClass varchar(255)
 
AS
 
 
declare @RC int,
    @vchrLine varchar(500) 
declare @vchrFieldTypeVB varchar(50)
declare @vchrFieldTypeDB varchar(50)
declare @vchrVBFieldName varchar(255)
declare @vchrFieldType varchar(50)
declare @vchrParamName varchar(255)
declare @vchrParamListVB varchar(8000)
declare @vchrParamListVB2 varchar(8000)
 
 
select
    @vchrParamListVB = '',
    @vchrParamListVB2 = ''
declare curFields CURSOR for
    select p.name as paramName,
            dbo.f_4P_columnTypevb (p.xtype) as fieldTypeVB
        from sysobjects o
            join syscolumns p on p.id = o.id
        where o.name = @ProcName
 
OPEN curFields
WHILE 1=1 
BEGIN
    FETCH NEXT FROM curFields INTO @vchrParamName, @vchrFieldTypeVB
    if @@fetch_status <> 0 begin
        break
    end 
 
    --get rid of the '@'
    set @vchrParamName = replace(@vchrParamName,'@','')
 
    --build a VB param list
    set @vchrParamListVB = @vchrParamListVB  + 'ByVal ' + @vchrParamName 
        + ' As ' + @vchrFieldTypeVB + ', ' 
    set @vchrParamListVB2 = @vchrParamListVB2  + @vchrParamName + ', ' 
 
END
CLOSE curFields
DEALLOCATE curFields
 
  print'    Sub New()'
 
print '    End Sub'
print ''
 
  print'    Sub New(' + @vchrParamListVB + 'ByVal db As String)'
print '        Using oDR As SqlClient.SqlDataReader = ' 
    + @DataClass + @ProcName + '(' + @vchrParamListVB2 + 'db).getReader'
 
  print'            If oDR.Read Then'
 
DECLARE @vchrField varchar(255)
 
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR 
    SELECT  c.Name ,
            dbo.f_4P_columnTypeVB(c.xtype)
        from sysobjects o (nolock)
            join syscolumns c (nolock) on o.id = c.id    
        where o.[name] = @Table
            and o.xtype = 'U'
        order by c.colorder
 
OPEN curName
WHILE 1=1 
BEGIN
    FETCH NEXT FROM curName INTO @vchrField,@vchrFieldType
    if @@fetch_status <> 0 begin
        break
    end 
 
    --write the field declaration
    print '                ME.' + @vchrField + ' = oDR("' + @vchrField + '")'
 
END
CLOSE curName
DEALLOCATE curName 
 
Print '            End If'
Print '        End Using'
Print '    End Sub'
 
go
 
grant exec on sp_NEW to public
 

Trackback Print

Tags:

Categories:

Location: Blogs Parent Separator

DEVSHED Blogs Child Separator

SQL

0 comment(s) so far...

As always, I welcome your comments!