CREATE PROCEDURE [dbo].[SearchNationalCode] @Type TINYINT , @Code VARCHAR(MAX),  @SystemName VARCHAR(Max) 
AS
 IF EXISTS(Select * From [tempdb].dbo.sysobjects Where Name = '##TmpSickInfo')
  DELETE from ##TmpSickInfo where SystemName = @SystemName
ELSE
  CREATE TABLE [dbo].[##TmpSickInfo] 
  (
    OrganizationCode VARCHAR(3) COLLATE Arabic_CI_AS NOT NULL,
	OrganizationName NVARCHAR(25) COLLATE Arabic_CI_AS NOT NULL,
	FirstName NVARCHAR(MAX) COLLATE Arabic_CI_AS NULL,
	LastName NVARCHAR(MAX) COLLATE Arabic_CI_AS NULL,
	PatientAge SMALLINT,
	ValidityDate VARCHAR(10) COLLATE Arabic_CI_AS NULL,
	TypeSex TINYINT,
	Msg NVARCHAR(MAX) COLLATE Arabic_CI_AS NULL,
    DistinctCodebook VARCHAR(18) COLLATE Arabic_CI_AS NULL,
	Codebook VARCHAR(MAX) COLLATE Arabic_CI_AS NULL,
	NationalCode VARCHAR(10) COLLATE Arabic_CI_AS NULL,
	Family NVARCHAR(50) COLLATE Arabic_CI_AS NOT NULL,
	AgreementNo VARCHAR(MAX) COLLATE Arabic_CI_AS NULL,
	Phone VARCHAR(MAX) COLLATE Arabic_CI_AS NULL,
	Mobile VARCHAR(11) COLLATE Arabic_CI_AS NULL,
	VeteransPercent INT,
	BirthDate VARCHAR(10) COLLATE Arabic_CI_AS NULL,
	SystemName VARCHAR(100) COLLATE Arabic_CI_AS NOT NULL
 )
 
 DECLARE @SqlCommand NVARCHAR(4000)
 DECLARE @Parameter  NVARCHAR(50)

 SET @SqlCommand = ' INSERT INTO ##TmpSickInfo(OrganizationCode, OrganizationName, FirstName,LastName,ValidityDate,PatientAge,TypeSex,Msg,
				   BirthDate,DistinctCodebook,Codebook,NationalCode,Family,AgreementNo,Phone, Mobile, VeteransPercent,SystemName )
				   SELECT Notebimeh.Sazman_Code, Sahmiyeh.Sa_Name,Notebimeh.Name, Notebimeh.Family, 
				   dbo.MiladiToShamsi(Notebimeh.VDATE) VDate, Notebimeh.Age, Notebimeh.TypeSex,Notebimeh.Mess, 
				   Dbo.MiladiToShamsi(BirthDate) BirthDate, 
				   CASE
					   WHEN LEN(Notebimeh.Note_Code)= 18 THEN LEFT(Note_Code,16)
					   ELSE Note_Code
				   END AS DistinctCodebook, Note_Code Codebook,
				   Meli_Card_No, 
				   Notebimeh.Name +'' '' + Notebimeh.Family FamilyName, Agreement_No,Tel,Mobile,VeteransPercent ,@systemName
				   FROM Sahmiyeh WITH (NOLOCK) INNER JOIN Notebimeh WITH (NOLOCK) 
				   ON Sahmiyeh.Sazman_Code = Notebimeh.Sazman_Code Where Active = 1'
				   IF @Type = 0 
				      SET @SqlCommand = @SqlCommand  + ' AND LEFT(Note_Code,16) = LEFT(''' + @Code + ''',16)'
				   ELSE IF @Type = 1
                      SET @SqlCommand = @SqlCommand  + ' AND Meli_Card_No = ''' + @Code + ''''
				   ELSE IF @Type = 2
				      SET @SqlCommand =  @SqlCommand + ' AND LEFT(Meli_Card_No,10) = LEFT(''' + @Code + ''',10)'
				   SET @SqlCommand = @SqlCommand + ' ORDER BY dbo.Notebimeh.Tarikh DESC'
 SET @Parameter = N'@SystemName VARCHAR(Max) '
 EXEC Sp_ExecuteSql  @SqlCommand, @Parameter, @SystemName = @SystemName
 PRINT @SqlCommand
 
 SELECT OrganizationCode, CAST(OrganizationName AS VARCHAR(25)) OrganizationName , CAST(Family AS NVARCHAR(50)) FamilyName,DistinctCodebook
 FROM ##TmpSickInfo
 WHERE SystemName = @SystemName
 GROUP BY OrganizationCode, OrganizationName, Family,DistinctCodebook
