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.
Hello,
I'd recommend updating to the latest version of CodeSmith and .netTiers as well as checking out the following three forum posts in which this issue was resolved:
community.codesmithtools.com/.../19044.aspx
community.codesmithtools.com/.../10547.aspx
community.codesmithtools.com/.../26171.aspx
Thanks
-Blake Niemyjski
Blake Niemyjski CodeSmith Tools, LLC. Software Development Engineer Blog: http://windowscoding.com/blogs/blake/ .NetTiers team | Visit http://www.nettiers.com
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,
....
Please note that @jcls parameter is now supplied a default value.