• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

stored procedure with dynamic

HI All,

could you please help me to create the dynamic stored procedure i already written it but seems like need to improve,plz see the screen in attachment.

I have to create dynamic stored procedure with some criteria
Validation
1.if none of the fields provided it will not search anything
2.(First Name should not be the only search criteriaits) it will not search if only provided firstname,
if user is providing first name then he should also provide the last name.then it will search
3.last name should be at least 3 character,other wise raise issue to user
4.its should be search with other parameter like medicare id ,medicaid id and date of birth
5.here also some issue with date which i used like ,i know its wrong.

Please help me to make a perfect stored procedure


----------------------------------------------------------------------------------

CREATE PROCEDURE SGetPatientInfo
                              @in_vcLastName                  varchar(30),
                        @in_vcFirstName                  varchar(17),
                        @in_dtDOB                        DateTime,
                        @in_nMedicaidID                  numeric,                        
                        @in_nMPID                          numeric,      
                        @in_nMedicare                     numeric

      
AS
BEGIN

DECLARE @sSqlStart Varchar(1000),
            @sSqlEnd Varchar(100)       

SET NOCOUNT ON

      Set @sSqlStart=''      
      Set @sSqlEnd=''

      IF @in_vcLastName = ''
            Begin
                  Set @in_vcLastName = '%'
            End

      IF in_vcFirstName = ''
            Begin
                  Set in_vcFirstName = '%'
            End

      IF @in_dtDOB = ''
            Begin
                  Set @in_dtDOB = '%'
            End

      IF @in_nMedicaidID = ''
            Begin
                  Set @in_nMedicaidID = '%'
            End
      
      IF @in_nMPID = ''
            Begin
                  Set @in_nMPID = '%'
            End

      IF @in_nMedicare = ''
            Begin
                  Set @in_nMedicare = '%'
            End

      Else
            Begin
                  Set in_vcFirstName = in_vcFirstName + '%'
            End            

            Set @sSqlStart=      'SELECT
                        Last_Name      AS      LastName,
                        First_Name        AS      FirstName,
                            Patient_ID      AS      PatientID,
                            Birth_Date      AS      BirthDate,
                        SSN              AS      SSN,
                        Gender              AS      Gender,
                        Age              AS      Age,

                            
                  FROM
                        Patient PT

                  INNER JOIN PatientSecIds PS

                  ON
                  PT.Patient_Key = PS.Patient_Key

                  WHERE
                        PT.Last_Name like ' + '''' + @in_vcLastName  + '''' + '
                        AND PT.First_Name like ' + '''' + @in_vcFirstName  + '''' + '
                        AND PT.Birth_Date like ' + '''' + @in_dtDOB + '''' + '
                        AND PS.Medicadeid like ' + '''' + @in_nMedicaidID  + '''' + '
                        AND PS.MPID like ' + '''' + @in_nMPID + '''' + '
                        AND PS.MediCare like ' + '''' + @in_nMedicare + '''' + '

                              
        
                  Begin
                        Set @sSqlEnd = 'Order By First_Name Asc'
                  End

      EXEC (@sSqlStart + @sSqlEnd)

SET NOCOUNT OFF

END
Doc1.docx
0
nicemanish
Asked:
nicemanish
  • 2
1 Solution
 
nicemanishAuthor Commented:
Hi Admin,

could anyone reply?
0
 
AnujCommented:
can you check this code?

ALTER PROCEDURE SGetPatientInfo  
                              @in_vcLastName                  varchar(30),
                        @in_vcFirstName                  varchar(17),
                        @in_dtDOB                        DateTime,
                        @in_nMedicaidID                  numeric,                        
                        @in_nMPID                          numeric,      
                        @in_nMedicare                     numeric

      
AS
BEGIN
--	if none of the fields provided it will not search anything
IF (@in_vcLastName IS NULL OR @in_vcLastName = '')
  AND ( @in_vcFirstName IS NULL OR @in_vcFirstName = '')
  AND (@in_dtDOB IS NULL)  
  AND (@in_nMedicaidID IS NULL) 
  AND (@in_nMPID IS NULL)
  AND (@in_nMedicare IS NULL)
BEGIN 
	RAISERROR ('No parameters provided, please provide first name with Last name or DB or MedicaidID or Mpid or Medicare',16,1)
	RETURN
END

--(First Name should not be the only search criteriaits) it will not search if only provided firstname,
--if user is providing first name then he should also provide the last name.then it will search

IF ( @in_vcFirstName IS NOT NULL AND @in_vcFirstName <> '') AND (@in_vcLastName IS NULL OR @in_vcLastName = '')
BEGIN 
	RAISERROR ('First Name should not be the only search criteriaits, please provide Last Name with 3 character at least',16,1)
	RETURN
END

-- last name should be at least 3 character,other wise raise issue to user
IF ( @in_vcFirstName IS NOT NULL AND @in_vcFirstName <> '') AND LEN(@in_vcLastName) < 2
BEGIN 
	RAISERROR ('Last name should be at least 3 character',16,1)
	RETURN
END

SET NOCOUNT ON 

      DECLARE @SQL VARCHAR(MAX),
			  @WHERE VARCHAR(MAX) = ''

			IF @in_vcLastName <> '' AND @in_vcLastName IS NOT NULL
			BEGIN
				SET @WHERE = @WHERE + 'AND PT.Last_Name like '+'''%'+@in_vcLastName+'%'''
			END

			IF @in_vcFirstName <> '' AND @in_vcFirstName IS NOT NULL
			BEGIN
				SET @WHERE = @WHERE + 'AND PT.First_Name like '+'''%'+@in_vcFirstName+'%'''
			END

			IF @in_dtDOB IS NOT NULL
			BEGIN
				SET @WHERE = @WHERE + 'AND PT.Birth_Date = ' + ''''+CONVERT(VARCHAR,@in_dtDOB,101)+''''
			END

			IF @in_nMedicaidID IS NOT NULL
			BEGIN
				SET @WHERE = @WHERE + 'AND PS.Medicadeid = '+ CONVERT(VARCHAR,@in_nMedicaidID)
			END

			IF @in_nMPID IS NOT NULL
			BEGIN
				SET @WHERE = @WHERE + 'AND PS.MPID = '+ CONVERT(VARCHAR,@in_nMPID)
			END

			IF @in_nMedicare IS NOT NULL
			BEGIN
				SET @WHERE = @WHERE + 'AND PS.MediCare = '+ CONVERT(VARCHAR,@in_nMedicare)
			END

            Set @SQL=      'SELECT Last_Name AS LastName,
							First_Name AS FirstName,
                            Patient_ID AS PatientID,
                            Birth_Date AS BirthDate,
							SSN, Gender, Age
							FROM Patient PT
							INNER JOIN PatientSecIds PS 
							ON PT.Patient_Key = PS.Patient_Key
							WHERE 1 = 1 '
			SET @SQL = @SQL + @WHERE + ' Order By First_Name Asc'
                  

			PRINT @SQL

SET NOCOUNT OFF 

END

Open in new window

0
 
nicemanishAuthor Commented:
You Really help me ... i have done my proc with some other changes.

you are Excellent
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now