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

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
0
DCUnited
Asked:
DCUnited
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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