SQL Server 2008 stored procedure Declare variables SET errors

I have a code that runs fine but trying to make into a stored procedure (SQL Server 2008) and getting errors.
I need to have it to where the end user will enter a start date (parameter) and then return data from the 1st of the month to the end of the month (based on the @StartDt)

if object_id( N'tempdb..#RPT', N'U' ) is not null drop table #RPT;
CREATE TABLE #RPT (
[Member_Number] [VARCHAR](10) NULL
 ,[Card_Number] [VARCHAR](20) NULL
 ,[Portfolio] [VARCHAR](8) NULL
 ,[CardType] [VARCHAR](7) NULL
 ,[Chrg_Off_Date] [DATE] NULL
 ,[Chrg_Off_Type] [VARCHAR](4) NULL
 ,[SHS_Date] [DATE] NULL
) ON [PRIMARY]

GO

Declare @StartDt date ,@BegCurrMonth date ,@EndCurrMonth date ,@SQL1 VARCHAR(MAX) ,@SQL2 VARCHAR(MAX)
set @StartDt = '02/01/2018'
set @BegCurrMonth = convert(varchar(10),DATEADD(mm,datediff(mm,0,@StartDt),0),101)
set @EndCurrMonth = convert(varchar(10),DATEADD(dd,-1,dateadd(mm,datediff(mm,0,@StartDt)+1,0)),101)

set @SQL1 = '
Select *
from  OPENQUERY([PROD],''
Select
''''N/A'''' as Member_Number
,a.LN_NO as Card_Number
,''''CreditCard'''' as Portfolio
,CASE
           WHEN M1.INV_CLASS_CODE IN (''''FN'''',''''RF'''',''''SF'''',''''SN'''') THEN ''''Plat''''
        WHEN M1.INV_CLASS_CODE IN (''''FM'''',''''RM'''',''''SM'''',''''SQ'''') THEN ''''Gold''''
            WHEN M1.INV_CLASS_CODE IN(''''WP'''',''''UM'''',''''WY'''',''''WZ'''') THEN ''''Black''''
        ELSE ''''Other''''
      END as CardType
,a.PROC_STOP_CHG_DATE as Chrg_Off_Date
,''''N/A'''' as Chrg_Off_Type
,cast(NULL as Date) as SHS_Date
from EIW_D_MIDE.CARD_ALERT_CS a
LEFT JOIN EIW_D_MIDE.CARD_MASTER1_CS m1 on a.ln_no = m1.ln_no
Where a.DONT_PROCESS_CD = ''''G''''
and cast(a.PROC_STOP_CHG_DATE as date format ''''mm-dd-yyyy'''') between '''''+CONVERT(varchar(10),@BegCurrMonth,101)+''''' and ''''+CONVERT(varchar(10),@EndCurrMonth,101)+'''''
order by a.LN_NO,a.PROC_STOP_CHG_DATE
'')'

INSERT INTO #RPT exec (@sql1)

set @SQL2 = '
Select…………………………….

I've tried different ways and get different errors
Including:
CREATE PROCEDURE [dbo].[usp_CC_Chrg_Offs]
@StartDt

Declare @BegCurrMonth date ,@EndCurrMonth date ,@SQL1 VARCHAR(MAX) ,@SQL2 VARCHAR(MAX)
set @BegCurrMonth = convert(varchar(10),DATEADD(mm,datediff(mm,0,@StartDt),0),101)
set @EndCurrMonth = convert(varchar(10),DATEADD(dd,-1,dateadd(mm,datediff(mm,0,@StartDt)+1,0)),101)

Everything I try either returns the error: Must declare the scalar variables Or  SET options have incorrect settings: 'ANSI_PADDING'
EE.sql
DCUnitedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Snarf0001Commented:
Can you post the full query of something you're attempting?  I see the raw query, but don't see the full "create proc" script.
Off the top, the little snippet you included doesn't have the data type of the parameter included, but not sure if that's just a typo when copying the code in here:

CREATE PROCEDURE [dbo].[usp_CC_Chrg_Offs]
@StartDt DATE
0
Snarf0001Commented:
Also, take the "GO" statement out.
Not necessary, and in the context of a stored proc would actually break the code.  Most likely what's going wrong.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Yes please, lets have a look at the whole script from 'CREATE' to 'END'

Also noticed : cast(a.PROC_STOP_CHG_DATE as date format ''''mm-dd-yyyy'''')  

Which will cause problems. s/b cast(a.PROC_STOP_CHG_DATE as date)

And use style code 120 in CONVERT(varchar(10),@BegCurrMonth,120)+''''' and '''''+CONVERT(varchar(10),@EndCurrMonth,120)

(also was missing a single quote in   +''''' and '''''+   after the and)
0
_agx_Commented:
(No points....)

In addition to the syntax errors mentioned by others, dynamic SQL is notoriously hard to debug (not to mention EXEC can be insecure).  Looking at the bit of code posted, I don't see a reason for using it. Are you sure you really need dynamic SQL here?

Hard to say more without the full procedure, but ... if you really do need dynamic sql, start simpler. Create a basic create procedure shell, nothing fancy just the proc definition.  Get it to compile first, then start adding a few lines of sql to it until it breaks.

CREATE PROCEDURE [dbo].[usp_CC_Chrg_Offs]
@StartDt DATE
AS
BEGIN
--- more sql will eventually go here
END
GO
0
DCUnitedAuthor Commented:
I can't believe it was so simple. Kept changing everything except that. Took out the Go, was able to make a stored proc and it ran perfectly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.