Welcome to the CodeSmith Community!

Re: Custom Stored Procedure Returns void

Support Forums

Feel free to ask any questions about CodeSmith here.

Custom Stored Procedure Returns void

Answered (Verified) This question is answered

We are using Codesmith 5.2 and Nettiers templates to generate a method from a custom stored procedure.

The stored procedure name is 'usp_Participant_SearchParticipantAddress'. There is a table by the name 'Participant' in our database.

The method gets generated correctly on the ParticipantProvider  but the return type of the method is void instead of dataset.

 

The stored procedure is:

CREATE PROCEDURE [dbo].[usp_Participant_SearchParticipantAddress]
    @jcls NVARCHAR(MAX),
    @lastName NVARCHAR(100) = NULL ,
    @middleName NVARCHAR(50) = NULL ,
    @firstName NVARCHAR(50) = NULL ,
    @participantSearch BIT = 1 ,
    @organizationSearch BIT = 1 ,
    @includeAlternateName BIT = 1 ,
    @isHistorical BIT = 0,
    @isNonCase BIT = 0,
    @StartDateOfBirth DATETIME = NULL ,
    @EndDateOfBirth DATETIME = NULL ,
    @DriverLicenseNumber NVARCHAR(250) = NULL ,
    @DriverLicenseStateID AS INT = NULL ,
    @CaseID NVARCHAR(30) = '' ,
    @AddressLine1 NVARCHAR(250) = NULL ,
    @AddressLine2 NVARCHAR(250) = NULL ,
    @City NVARCHAR(250) = NULL ,
    @AddressStateID INT = NULL ,
    @AddressTypeID INT = NULL ,
    @StateTypeID INT = NULL ,
    @ZipCode NVARCHAR(250) = NULL ,
    @EffectiveTo DATETIME = NULL ,
    @EffectiveFrom DATETIME = NULL ,
    @ParticipantTypeIDs NVARCHAR(MAX) = NULL,
    @soundex BIT = 0
AS 
    BEGIN
        SET NOCOUNT ON ;
        IF ( @lastName = '' ) 
            SET @lastName = NULL
        IF ( @middleName = '' ) 
            SET @middleName = NULL
        IF ( @firstName = '' ) 
            SET @firstName = NULL
 
 
        IF ( @AddressLine1 = '' ) 
            SET @AddressLine1 = NULL
        IF ( @AddressLine2 = '' ) 
            SET @AddressLine2 = NULL
        IF ( @City = '' ) 
            SET @City = NULL
        IF ( @DriverLicenseNumber = '' ) 
            SET @DriverLicenseNumber = NULL
        IF ( @ZipCode = '' ) 
            SET @ZipCode = NULL
 
        IF ( @soundex = 1 ) 
            BEGIN
                DECLARE @soundexLastName AS NVARCHAR(10)
                SET @soundexLastName = SOUNDEX(@lastName)
 
                DECLARE @soundexMiddleName AS NVARCHAR(10)
                SET @soundexMiddleName = SOUNDEX(@middleName)
 
                DECLARE @soundexFirstName AS NVARCHAR(10)
                SET @soundexFirstName = SOUNDEX(@soundexFirstName)
  
                DECLARE @Test AS BIT
  
   
                SELECT DISTINCT TOP 100
                        CaseID ,
                        CommonCaseID ,
                        JCLTypeID ,
                        jclDescription ,
                        CourtType ,
                        JurisdictionType ,
                        LocationType ,
                        ParticipantID ,
                        CommonPersonOrganizationID ,
                        participantRole ,
                        firstName ,
                        middleName ,
                        lastName ,
                        DateOfBirth,
                        DriverLicenseNumber,
                        DriverLicenseState ,
                        State ,
                        OrganizationName ,
                        soundexOrganizationName ,
                        addrLine1 ,
                        addrLine2 ,
                        city ,
                        zipCode ,
                        effectiveFrom ,
                        effectiveTo ,
                        suppress ,
                        AddressType ,
                        AddressTypeID ,
                        StateTypeID ,
                        StateIdNumber ,
                        DLNStateTypeID ,
                        isHistorical ,
                        isNonCase ,
                        ParticipantTypeID ,
                        internalParticipantRole ,
                        CaseNonPublicStatus ,
                        ParticipantNonPublicStatus ,
                        ParticipantNonPublicStatusID ,
                        CaseNonPublicStatusID ,
                        [Full Name] ,
                        MergeEntryCPO ,
                        MergeGroupCPO ,
                        isPrimary ,
                        isMerged ,
                        CommonCaseCategoryTypeID ,
                        active ,
                        ParticipantActive
                FROM    dbo.vw_SearchParticipantAddress T
                WHERE   JCLTypeID IN ( SELECT   *
                                       FROM     dbo.Split(@jcls, ',') ) AND
                                       (ISNULL(@ParticipantTypeIDs,'') = '' OR
          ParticipantTypeID IN ( SELECT *
          FROM dbo.Split(@ParticipantTypeIDs, ',')))
   /* search on participant name if present */
                        AND ( ( @participantSearch = 0
                                AND @organizationSearch = 0
                                AND @includeAlternateName = 0
                              )
                              OR ( @participantSearch = 1
                                   AND ( @soundexLastName IS NULL
                                         OR soundexLastName = @soundexLastName
                                       )
                                   AND ( @soundexFirstName IS NULL
                                         OR soundexFirstName = @soundexFirstName
                                       )
                                   AND ( @soundexMiddleName IS NULL
                                         OR soundexMiddleName = @soundexMiddleName
                                       )
                                 )
                              OR ( @organizationSearch = 1
                                   AND ( @soundexLastName IS NULL
                                         OR soundexOrganizationName = @soundexLastName
                                       )
                                 )
                              OR ( @includeAlternateName = 1
                                   AND ( @soundexLastName IS NULL
                                         OR T.soundexAlternateLastName = @soundexLastName
                                       )
                                   AND ( @soundexFirstName IS NULL
                                         OR T.soundexAlternateFirstName = @soundexFirstName
                                       )
                                   AND ( @soundexMiddleName IS NULL
                                         OR T.soundexAlternateMiddleName = @soundexMiddleName
                                       )
                                 )
                            )
                        AND (
       isHistorical = 0 OR
       isHistorical = @isHistorical
                            )
                        AND (
       isNonCase = 0 OR
       isNonCase = @isNonCase
       )
                        AND ( @StartDateOfBirth IS NULL
                              OR @EndDateOfBirth IS NULL
                              OR dbo.udf_EncryptedDateBetween(T.DateOfBirth,
                                                              @StartDateOfBirth,
                                                              @EndDateOfBirth) = 1
                            )
                        AND ( @DriverLicenseNumber IS NULL
                              OR T.DriverLicenseNumber = @DriverLicenseNumber
                            )
                        AND ( @DriverLicenseStateID IS NULL
                              OR T.DLNStateTypeID = @DriverLicenseStateID
                            )
                        AND ( @AddressLine1 IS NULL
                              OR T.addrLine1 = @AddressLine1
                            )
                        AND ( @AddressLine2 IS NULL
                              OR T.addrLine2 = @AddressLine2
                            )
                        AND ( @City IS NULL
                              OR T.City = @City
                            )
                        AND ( @AddressTypeID IS NULL
                              OR T.AddressTypeID = @AddressTypeID
                            )
                        AND ( @StateTypeID IS NULL
                              OR T.StateTypeID = @StateTypeID
                            )
                        AND ( @ZipCode IS NULL
                              OR T.zipCode = @ZipCode
                            )
                        AND ( @EffectiveFrom IS NULL
                              OR T.effectiveFrom = @EffectiveFrom
                            )
                        AND ( @EffectiveTo IS NULL
                              OR T.effectiveTo = @EffectiveTo
                            )
                        AND CaseID > @CaseID
                OPTION  ( FAST 100 )
  
            END
        ELSE 
            BEGIN
                SELECT   
  
  DISTINCT TOP 100
                        CaseID ,
                        CommonCaseID ,
                        JCLTypeID ,
                        jclDescription ,
                        CourtType ,
                        JurisdictionType ,
                        LocationType ,
                        ParticipantID ,
                        CommonPersonOrganizationID ,
                        participantRole ,
                        firstName ,
                        middleName ,
                        lastName ,
                        DateOfBirth,
                        DriverLicenseNumber,
                        DriverLicenseState ,
                        State ,
                        OrganizationName ,
                        soundexOrganizationName ,
                        addrLine1 ,
                        addrLine2 ,
                        city ,
                        zipCode ,
                        effectiveFrom ,
                        effectiveTo ,
                        suppress ,
                        AddressType ,
                        AddressTypeID ,
                        StateTypeID ,
                        StateIdNumber ,
                        DLNStateTypeID ,
                        isHistorical ,
                        isNonCase ,
                        ParticipantTypeID ,
                        internalParticipantRole ,
                        CaseNonPublicStatus ,
                        ParticipantNonPublicStatus ,
                        ParticipantNonPublicStatusID ,
                        CaseNonPublicStatusID ,
                        [Full Name] ,
                        MergeEntryCPO ,
                        MergeGroupCPO ,
                        isPrimary ,
                        isMerged ,
                        CommonCaseCategoryTypeID ,
                        active ,
                        ParticipantActive
                FROM    dbo.vw_SearchParticipantAddress T
                WHERE   JCLTypeID IN ( SELECT   *
                                       FROM     dbo.Split(@jcls, ',') ) AND
                                       (ISNULL(@ParticipantTypeIDs,'') = '' OR
          ParticipantTypeID IN ( SELECT *
          FROM dbo.Split(@ParticipantTypeIDs, ',')))
   /* search on participant name if present */
                        AND ( ( @participantSearch = 0
                                AND @organizationSearch = 0
                                AND @includeAlternateName = 0
                              )
                              OR ( @participantSearch = 1
                                   AND ( @LastName IS NULL
                                         OR LastName = @LastName
                                       )
                                   AND ( @FirstName IS NULL
                                         OR firstName = @FirstName
                                       )
                                   AND ( @MiddleName IS NULL
                                         OR MiddleName = @MiddleName
                                       )
                                 )
                              OR ( @organizationSearch = 1
                                   AND ( @LastName IS NULL
                                         OR OrganizationName = @LastName
                                       )
                                 )
                              OR ( @includeAlternateName = 1
                                   AND ( @LastName IS NULL
                                         OR AlternateLastName = @LastName
                                       )
                                   AND ( @FirstName IS NULL
                                         OR AlternateFirstName = @FirstName
                                       )
                                   AND ( @MiddleName IS NULL
                                         OR AlternateMiddleName = @MiddleName
                                       )
                                 )
                            )
                        AND (
       isHistorical = 0 OR
       isHistorical = @isHistorical
                            )
                        AND (
       isNonCase = 0 OR
       isNonCase = @isNonCase
       )
                        AND ( @StartDateOfBirth IS NULL
                              OR @EndDateOfBirth IS NULL
                              OR dbo.udf_EncryptedDateBetween(T.DateOfBirth,
                                                              @StartDateOfBirth,
                                                              @EndDateOfBirth) = 1
                            )
                        AND ( @DriverLicenseNumber IS NULL
                              OR T.DriverLicenseNumber = @DriverLicenseNumber
                            )
                        AND ( @DriverLicenseStateID IS NULL
                              OR T.DLNStateTypeID = @DriverLicenseStateID
                            )
                        AND ( @AddressLine1 IS NULL
                              OR T.addrLine1 = @AddressLine1
                            )
                        AND ( @AddressLine2 IS NULL
                              OR T.addrLine2 = @AddressLine2
                            )
                        AND ( @City IS NULL
                              OR T.City = @City
                            )
                        AND ( @AddressTypeID IS NULL
                              OR T.AddressTypeID = @AddressTypeID
                            )
                        AND ( @StateTypeID IS NULL
                              OR T.StateTypeID = @StateTypeID
                            )
                        AND ( @ZipCode IS NULL
                              OR T.zipCode = @ZipCode
                            )
                        AND ( @EffectiveFrom IS NULL
                              OR T.effectiveFrom = @EffectiveFrom
                            )
                        AND ( @EffectiveTo IS NULL
                              OR T.effectiveTo = @EffectiveTo
                            )
                        AND CaseID > @CaseID
                OPTION  ( FAST 100 )
            END
    END
 

The stored procedure should return a dataset because we are doing a select statement on a view that joins multiple tables. We are using SQL 2008
Enterprise Edition.

How can we get Nettiers to return a dataset?

Please help. Thanks in advance.

 

 

 

 

 

  • Post Points: 35
Verified Answer
All Replies
  • Thanks. We modified the stored procedure to have a default  value for the first parameter @jcls.

    CREATE PROCEDURE [dbo].[usp_Participant_SearchParticipantAddress]

       @jcls NVARCHAR(MAX),

       @lastName NVARCHAR(100) = NULL ,

       @middleName NVARCHAR(50) = NULL ,

       @firstName NVARCHAR(50) = NULL ,

       @participantSearch BIT = 1 ,

       @organizationSearch BIT = 1

    .....

    Now its generating method with return type of dataset.

    Thanks for your help.

  • Sorry - I pasted older version of code. The new version is:

    ALTER PROCEDURE [dbo].[usp_Participant_SearchParticipantAddress]

       @jcls NVARCHAR(MAX) = NULL,

       @lastName NVARCHAR(100) = NULL ,

       @middleName NVARCHAR(50) = NULL ,

    ....

    Please note that @jcls parameter is now supplied a default value.

Page 1 of 1 (4 items)