SQL Server stored procedure in embedded SQL with host variables

Question

In MicroFocus Cobol I am creating a stored procedure with using host variables. SQL code is this:

 CREATE PROCEDURE dbo.LLPSY_PK1_BPS_PROC_INDEX  
 AS 
 BEGIN 
     IF EXISTS (SELECT * FROM sysobjects 
                WHERE id = object_id(N'LLPSY_PK1_BPS_INDEX ') 
                  AND OBJECTPROPERTY(id, N'IsTable') = 1) 
        DROP TABLE LLPSY_PK1_BPS_INDEX 

     IF EXISTS (SELECT * FROM sysobjects 
                WHERE id = object_id(N'LLPSY_PK1_BPS_INDEX_1    ') 
                  AND OBJECTPROPERTY(id, N'IsTable') = 1) 
        DROP TABLE LLPSY_PK1_BPS_INDEX_1    

     IF EXISTS (SELECT * FROM sysobjects 
                WHERE id = object_id(N'LLPSY_PK1_BPS_INDEX_2    ') 
                  AND OBJECTPROPERTY(id, N'IsTable') = 1) 
        DROP TABLE LLPSY_PK1_BPS_INDEX_2    

     SELECT 
         PAYMENT_REF = CASE C.ACNT_TYPE WHEN 1 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 0 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 2 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 4 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))END ,A.ACCNT_CODE AS ACCOUNT,BANKNO=CASE WHEN E.BANK_ACNT_NUM IS NULL THEN''ELSE E.BANK_ACNT_NUM END,AMOUNT=CASE WHEN A.AMOUNT>0 THEN A.AMOUNT ELSE A.AMOUNT*-1 END,A.D_C AS AMOUNT_DC,OTHER_AMOUNT=CASE WHEN A.OTHER_AMT>0 THEN A.OTHER_AMT ELSE A.OTHER_AMT*-1 END,A.D_C AS OTHER_AMOUNT_DC,CAST(A.ACCNT_CODE AS NCHAR(15))+CAST(A.PERIOD AS NCHAR(07))+CONVERT(CHAR(16),A.TRANS_DATETIME,112)AS LEDGER_KEY,CAST(A.JRNAL_NO AS NUMERIC(09))AS JN,CAST(A.JRNAL_LINE AS NUMERIC(09))AS TN,'A'AS BUDGET,' 'AS LOCAL_CURR,' 'AS COUNT,0 AS COUNTS,CAST(' 'AS NCHAR(80))AS TNS,C.DESCR AS ACCOUNT_NAME,A.DESCRIPTN AS DESCRIPTION,A.CONV_CODE,A.TREFERENCE AS TRANS_REF,' 'AS MATCHED,C.ACNT_TYPE AS AT,' 'AS DOUBLES,' 'AS SPLIT,ADD_1=E.A,' 'AS ADD_2,' 'AS ADD_3,PAYMENT_ADD=' ' INTO LLPSY_PK1_BPS_INDEX_1    FROM PK1_A_SALFLDG AS A INNER JOIN PK1_ACNT AS C ON A.ACCNT_CODE=C.ACNT_CODE LEFT JOIN LLPSY_PK1_BNK_BKA_VIEW   AS E ON A.ACCNT_CODE=E.ACCOUNT AND E.A<>'M'  OR  A.ACCNT_CODE=E.ACCOUNT AND E.A= 'M'  AND A.ANAL_T9   =E.BDS       LEFT JOIN PK1_A_SALFLDG_LAD  AS M ON A.ACCNT_CODE=M.ACCNT_CODE AND A.JRNAL_NO=M.JRNAL_NO AND A.JRNAL_LINE=M.JRNAL_LINE WHERE(( A.ACCNT_CODE BETWEEN ? and ? AND C.ACNT_TYPE IN(1)))AND ALLOCATION NOT IN ('A','P','Y','C','R','2','W') UNION SELECT PAYMENT_REF=CASE C.ACNT_TYPE WHEN 1 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 0 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 2 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 4 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))END ,A.ACCNT_CODE AS ACCOUNT,BANKNO=CASE WHEN E.BANK_ACNT_NUM IS NULL THEN''ELSE E.BANK_ACNT_NUM END,AMOUNT=CASE WHEN A.AMOUNT>0 THEN A.AMOUNT ELSE A.AMOUNT*-1 END,A.D_C AS AMOUNT_DC,OTHER_AMOUNT=CASE WHEN A.OTHER_AMT>0 THEN A.OTHER_AMT ELSE A.OTHER_AMT*-1 END,A.D_C AS OTHER_AMOUNT_DC,CAST(A.ACCNT_CODE AS NCHAR(15))+CAST(A.PERIOD AS NCHAR(07))+CONVERT(CHAR(16),A.TRANS_DATETIME,112)AS LEDGER_KEY,CAST(A.JRNAL_NO AS NUMERIC(09))AS JN,CAST(A.JRNAL_LINE AS NUMERIC(09))AS TN,'B'AS BUDGET,' 'AS LOCAL_CURR,' 'AS COUNT,0 AS COUNTS,CAST(' 'AS NCHAR(80))AS TNS,C.DESCR AS ACCOUNT_NAME,A.DESCRIPTN AS DESCRIPTION,A.CONV_CODE,A.TREFERENCE AS TRANS_REF,' 'AS MATCHED,C.ACNT_TYPE AS AT,' 'AS DOUBLES,' 'AS SPLIT,ADD_1=E.A,' 'AS ADD_2,' 'AS ADD_3,PAYMENT_ADD=' '                               FROM PK1_B_SALFLDG AS A INNER JOIN PK1_ACNT AS C ON A.ACCNT_CODE=C.ACNT_CODE LEFT JOIN LLPSY_PK1_BNK_BKA_VIEW   AS E ON A.ACCNT_CODE=E.ACCOUNT AND E.A<>'M'  OR  A.ACCNT_CODE=E.ACCOUNT AND E.A= 'M'  AND A.ANAL_T9   =E.BDS       LEFT JOIN PK1_A_SALFLDG_LAD  AS M ON A.ACCNT_CODE=M.ACCNT_CODE AND A.JRNAL_NO=M.JRNAL_NO AND A.JRNAL_LINE=M.JRNAL_LINE WHERE(( A.ACCNT_CODE BETWEEN ? and ? AND C.ACNT_TYPE IN(1)))AND ALLOCATION NOT IN ('A','P','Y','C','R','2','W')SELECT PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY=IDENTITY(int,1,1),DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3,PAYMENT_ADD INTO LLPSY_PK1_BPS_INDEX_2     FROM LLPSY_PK1_BPS_INDEX_1   SELECT PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3 INTO dbo.LLPSY_PK1_BPS_INDEX  FROM LLPSY_PK1_BPS_INDEX_2    WHERE ISNUMERIC(LTRIM(PAYMENT_REF))=1   SET IDENTITY_INSERT LLPSY_PK1_BPS_INDEX  ON  INSERT INTO LLPSY_PK1_BPS_INDEX  (PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3) SELECT dbo.LLPSY_Remove_AB_String(PAYMENT_REF), ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3 FROM LLPSY_PK1_BPS_INDEX_2    WHERE dbo.LLPSY_Remove_AB_String(PAYMENT_REF)<>'' AND  dbo.LLPSY_Remove_AB_String(PAYMENT_REF)<>PAYMENT_REF
  SET IDENTITY_INSERT LLPSY_PK1_BPS_INDEX  ON  INSERT INTO LLPSY_PK1_BPS_INDEX  (PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3) SELECT dbo.LLPSY_Remove_AB_String(PAYMENT_ADD)AS PAYMENT_REF, ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3 FROM LLPSY_PK1_BPS_INDEX_2    WHERE PAYMENT_ADD IS NOT NULL AND PAYMENT_ADD<>'' AND dbo.LLPSY_Remove_AB_String(PAYMENT_ADD)<>'' CREATE INDEX PAYMENT_REF_IND ON LLPSY_PK1_BPS_INDEX  (PAYMENT_REF)  CREATE INDEX BANKNO_IND ON LLPSY_PK1_BPS_INDEX  (BANKNO)  CREATE INDEX LEDGER_KEY_IND ON LLPSY_PK1_BPS_INDEX  (LEDGER_KEY)  CREATE INDEX JN_IND ON LLPSY_PK1_BPS_INDEX  (JN)  CREATE INDEX TN_IND ON LLPSY_PK1_BPS_INDEX  (TN)  CREATE NONCLUSTERED INDEX  REC_KEY_IND ON LLPSY_PK1_BPS_INDEX  ([REC_KEY]) 
DROP TABLE LLPSY_PK1_BPS_INDEX_1    
DROP TABLE LLPSY_PK1_BPS_INDEX_2     END

It ends with this error:

1. SQLCODE -156
2. SQLSTATE 37000
3. Incorrect syntax near the keyword 'PROCEDURE'

When I run this SQL in SQL Manager it's works without problem. When I am tracing it in SQL Profiler I got this:

declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P1 nvarchar(1000),@P2 nvarchar(1000),@P3 nvarchar(1000),@P4 nvarchar(1000)',N'CREATE PROCEDURE dbo.LLPSY_PK1_BPS_PROC_INDEX  AS BEGIN 
 IF EXISTS (select * from sysobjects where id = object_id(N''LLPSY_PK1_BPS_INDEX '') and OBJECTPROPERTY(id, N''IsTable'') = 1) DROP TABLE LLPSY_PK1_BPS_INDEX 
 IF EXISTS (select * from sysobjects where id = object_id(N''LLPSY_PK1_BPS_INDEX_1    '') and OBJECTPROPERTY(id, N''IsTable'') = 1) DROP TABLE LLPSY_PK1_BPS_INDEX_1    
 IF EXISTS (select * from sysobjects where id = object_id(N''LLPSY_PK1_BPS_INDEX_2    '') and OBJECTPROPERTY(id, N''IsTable'') = 1) DROP TABLE LLPSY_PK1_BPS_INDEX_2    
SELECT PAYMENT_REF=CASE C.ACNT_TYPE WHEN 1 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 0 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 2 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 4 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))END ,A.ACCNT_CODE AS ACCOUNT,BANKNO=CASE WHEN E.BANK_ACNT_NUM IS NULL THEN''''ELSE E.BANK_ACNT_NUM END,AMOUNT=CASE WHEN A.AMOUNT>0 THEN A.AMOUNT ELSE A.AMOUNT*-1 END,A.D_C AS AMOUNT_DC,OTHER_AMOUNT=CASE WHEN A.OTHER_AMT>0 THEN A.OTHER_AMT ELSE A.OTHER_AMT*-1 END,A.D_C AS OTHER_AMOUNT_DC,CAST(A.ACCNT_CODE AS NCHAR(15))+CAST(A.PERIOD AS NCHAR(07))+CONVERT(CHAR(16),A.TRANS_DATETIME,112)AS LEDGER_KEY,CAST(A.JRNAL_NO AS NUMERIC(09))AS JN,CAST(A.JRNAL_LINE AS NUMERIC(09))AS TN,''A''AS BUDGET,'' ''AS LOCAL_CURR,'' ''AS COUNT,0 AS COUNTS,CAST('' ''AS NCHAR(80))AS TNS,C.DESCR AS ACCOUNT_NAME,A.DESCRIPTN AS DESCRIPTION,A.CONV_CODE,A.TREFERENCE AS TRANS_REF,'' ''AS MATCHED,C.ACNT_TYPE AS AT,'' ''AS DOUBLES,'' ''AS SPLIT,ADD_1=E.A,'' ''AS ADD_2,'' ''AS ADD_3,PAYMENT_ADD='' '' INTO LLPSY_PK1_BPS_INDEX_1    FROM PK1_A_SALFLDG AS A INNER JOIN PK1_ACNT AS C ON A.ACCNT_CODE=C.ACNT_CODE LEFT JOIN LLPSY_PK1_BNK_BKA_VIEW   AS E ON A.ACCNT_CODE=E.ACCOUNT AND E.A<>''M''  OR  A.ACCNT_CODE=E.ACCOUNT AND E.A= ''M''  AND A.ANAL_T9   =E.BDS       LEFT JOIN PK1_A_SALFLDG_LAD  AS M ON A.ACCNT_CODE=M.ACCNT_CODE AND A.JRNAL_NO=M.JRNAL_NO AND A.JRNAL_LINE=M.JRNAL_LINE WHERE(( A.ACCNT_CODE BETWEEN @P1 and @P2 AND C.ACNT_TYPE IN(1)))AND ALLOCATION NOT IN (''A'',''P'',''Y'',''C'',''R'',''2'',''W'') UNION SELECT PAYMENT_REF=CASE C.ACNT_TYPE WHEN 1 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 0 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 2 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))WHEN 4 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))END ,A.ACCNT_CODE AS ACCOUNT,BANKNO=CASE WHEN E.BANK_ACNT_NUM IS NULL THEN''''ELSE E.BANK_ACNT_NUM END,AMOUNT=CASE WHEN A.AMOUNT>0 THEN A.AMOUNT ELSE A.AMOUNT*-1 END,A.D_C AS AMOUNT_DC,OTHER_AMOUNT=CASE WHEN A.OTHER_AMT>0 THEN A.OTHER_AMT ELSE A.OTHER_AMT*-1 END,A.D_C AS OTHER_AMOUNT_DC,CAST(A.ACCNT_CODE AS NCHAR(15))+CAST(A.PERIOD AS NCHAR(07))+CONVERT(CHAR(16),A.TRANS_DATETIME,112)AS LEDGER_KEY,CAST(A.JRNAL_NO AS NUMERIC(09))AS JN,CAST(A.JRNAL_LINE AS NUMERIC(09))AS TN,''B''AS BUDGET,'' ''AS LOCAL_CURR,'' ''AS COUNT,0 AS COUNTS,CAST('' ''AS NCHAR(80))AS TNS,C.DESCR AS ACCOUNT_NAME,A.DESCRIPTN AS DESCRIPTION,A.CONV_CODE,A.TREFERENCE AS TRANS_REF,'' ''AS MATCHED,C.ACNT_TYPE AS AT,'' ''AS DOUBLES,'' ''AS SPLIT,ADD_1=E.A,'' ''AS ADD_2,'' ''AS ADD_3,PAYMENT_ADD='' ''                               FROM PK1_B_SALFLDG AS A INNER JOIN PK1_ACNT AS C ON A.ACCNT_CODE=C.ACNT_CODE LEFT JOIN LLPSY_PK1_BNK_BKA_VIEW   AS E ON A.ACCNT_CODE=E.ACCOUNT AND E.A<>''M''  OR  A.ACCNT_CODE=E.ACCOUNT AND E.A= ''M''  AND A.ANAL_T9   =E.BDS       LEFT JOIN PK1_A_SALFLDG_LAD  AS M ON A.ACCNT_CODE=M.ACCNT_CODE AND A.JRNAL_NO=M.JRNAL_NO AND A.JRNAL_LINE=M.JRNAL_LINE WHERE(( A.ACCNT_CODE BETWEEN @P3 and @P4 AND C.ACNT_TYPE IN(1)))AND ALLOCATION NOT IN (''A'',''P'',''Y'',''C'',''R'',''2'',''W'')SELECT PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY=IDENTITY(int,1,1),DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3,PAYMENT_ADD INTO LLPSY_PK1_BPS_INDEX_2     FROM LLPSY_PK1_BPS_INDEX_1   SELECT PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3 INTO dbo.LLPSY_PK1_BPS_INDEX  FROM LLPSY_PK1_BPS_INDEX_2    WHERE ISNUMERIC(LTRIM(PAYMENT_REF))=1   SET IDENTITY_INSERT LLPSY_PK1_BPS_INDEX  ON  INSERT INTO LLPSY_PK1_BPS_INDEX  (PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3) SELECT dbo.LLPSY_Remove_AB_String(PAYMENT_REF), ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3 FROM LLPSY_PK1_BPS_INDEX_2    WHERE dbo.LLPSY_Remove_AB_String(PAYMENT_REF)<>'''' AND  dbo.LLPSY_Remove_AB_String(PAYMENT_REF)<>PAYMENT_REF
  SET IDENTITY_INSERT LLPSY_PK1_BPS_INDEX  ON  INSERT INTO LLPSY_PK1_BPS_INDEX  (PAYMENT_REF,ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3) SELECT dbo.LLPSY_Remove_AB_String(PAYMENT_ADD)AS PAYMENT_REF, ACCOUNT,BANKNO,AMOUNT,AMOUNT_DC,OTHER_AMOUNT,OTHER_AMOUNT_DC,LEDGER_KEY,JN,TN,BUDGET,LOCAL_CURR,COUNT,COUNTS,TNS,ACCOUNT_NAME,DESCRIPTION,CONV_CODE,TRANS_REF,MATCHED,AT,REC_KEY,DOUBLES,SPLIT,ADD_1,ADD_2,ADD_3 FROM LLPSY_PK1_BPS_INDEX_2    WHERE PAYMENT_ADD IS NOT NULL AND PAYMENT_ADD<>'''' AND dbo.LLPSY_Remove_AB_String(PAYMENT_ADD)<>'''' CREATE INDEX PAYMENT_REF_IND ON LLPSY_PK1_BPS_INDEX  (PAYMENT_REF)  CREATE INDEX BANKNO_IND ON LLPSY_PK1_BPS_INDEX  (BANKNO)  CREATE INDEX LEDGER_KEY_IND ON LLPSY_PK1_BPS_INDEX  (LEDGER_KEY)  CREATE INDEX JN_IND ON LLPSY_PK1_BPS_INDEX  (JN)  CREATE INDEX TN_IND ON LLPSY_PK1_BPS_INDEX  (TN)  CREATE NONCLUSTERED INDEX  REC_KEY_IND ON LLPSY_PK1_BPS_INDEX  ([REC_KEY]) 
DROP TABLE LLPSY_PK1_BPS_INDEX_1    
DROP TABLE LLPSY_PK1_BPS_INDEX_2     END',1
select @p1

And running this code from SQL Profiler causes same error as I have in embedded SQL. Please what is wrong here?

Answer 1

Not an answer but far too long as a comment. You have a lot of over-complicated code that would greatly benefit from relatively simple changes. You have:

SELECT PAYMENT_REF=CASE C.ACNT_TYPE 
WHEN 1 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int), CAST(30 AS int)) AS NCHAR(30))
WHEN 0 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))
WHEN 2 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30))
WHEN 4 THEN  CAST(SUBSTRING(A.TREFERENCE,CAST(01 AS int),CAST(30 AS int)) AS NCHAR(30)) END ,

• You can simply cast the case expression to the desired type. You don't need to cast each condition.
• For anyone that comes after you, put your boolean expressions IN ORDER.
• numeric literals will be implicitly cast to an appropriate datatype. Values like 1 and 30 will be interpreted as integers. There is no need to cast them.
• You use the same formula in each case. So why do you overcomplicate things by writing the same thing 4 times.
• substring(1, 30) is the same as left(30). Again, why the complication?

So this single formula can be condensed and simplified into:

select PAYMENT_REF = cast(case when C.ACNT_TYPE in (0, 1, 2, 4) 
    then left(A.TREFERENCE, 30) else NULL end as NCHAR(30)),

You can leave out the ELSE part if desired. I prefer including it since it indicates to anyone reading the code that the writer did, in fact, think about what happens when there is no match during evaluation. And simple formatting makes code vastly easier to read and therefore to understand.

Lastly, the destruction and creation of permanent tables in the database is a security issue and an efficiency issue. It far better to allow a qualified dba to determine the placement, organization, and indexing of this table then the embed it in application logic. You did not consider permissions to these tables - which also has significant implications. There is no clustered index AFAIK - that is often a problem.

It appears the the better approach to your goal is to simply create your stored procedure once. You create with TWO (not the confusing four you have now) parameters. Your dba will create the single table that is the goal of your logic - the other 2 are just temporary storage. In rough, abbreviated, and incomplete pseudo-code:

create procedure dbo.LLPSY_PK1_BPS_PROC_INDEX (@ACCT_START int, @ACCT_END int) as 
begin 
    insert ... 
    select ... 
    where ( A.ACCNT_CODE BETWEEN @ACCT_START and @ACCT_END) ... 
    ...
end;

There are more issues that should be addressed - but you have to start somewhere. The use of UNION (not UNION ALL), the multiple uses of [select ... into ...], the building of a procedure that is dependent on and specific to application logic at a particular point in time - all of these things are creating a future debugging and maintenance problem. And one last comment - your code assumes a particular schema for most statements but one very important one (the create procedure statement). Either assume everywhere or assume no where. Better not to assume at all without a good reason.