Calling variable inside T-SQL statement

Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)

I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author: XXXXXXXXXXXXX

-- Create date: 10/4/2007

-- Description: This proc is designed to pull all CSA

-- part sales from XXXXXX business system and upload them

-- into the local XXXXXXXX Database for commission reporting

-- =============================================

CREATEproc usp_CSAPartsSalesUpdate@.date1int, @.date2int

As

INSERTINTO CSAPartsSales( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount)

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmtFromOpenQuery(<CompanyName>,'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''+ @.date1+''' And (PMINVHST.HInvDate)<='''+ @.date2+''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')

In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.

This is the error message I get when I try to create the proc:

Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17

Incorrect syntax near '+'.

~~~ Thanks All~~~

You need to cast your variable as a varchar, ie:

>=''' + CAST(@.date1 as varchar(8)) + ''' AND ( ...etc

|||

Thanks for the reply Sswanner1 I'll try casting my variables tomorrow when I get into work and see if that corrects the problem.

|||

I tried casting my variables this morning as you suggested but it still returns the same error message

((PMINVHST.HInvDate)>='''+Cast(@.date1asvarchar(8))+''' And (PMINVHST.HInvDate)<='''+Cast(@.date2asvarchar(8))+''') AND

Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17

Incorrect syntax near '+'.

|||

If PMINVHST.HInvDate is also an integer, you've got too many single quotes in there. Ultimately, you want it to look like this:

((PMINVHST.HInvDate)>=20070810, not ((PMINVHST.HInvDate)>='20070810'

|||

PMINVHST.HInvDate is stored as text in the unix system

If I take the exact same sql statement and declare the variables and then set the values and then print the results the query looks exactly like I need it to. I can take the results that print to the screen copy them into a query edititor and run the query and I get the results I'm looking for. It has something to do with the way I'm trying to call it...I can't put my finger on it =(

|||

If I run this SP and then copy and paste the results to query editor it returns exactly what I need.

Declare @.commandasnvarchar(4000)

Declare @.date1asvarchar(8)

Declare @.date2asvarchar(8)

Set @.date1= 20070901

Set @.date2= 20070930

Set @.command='INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(XXXXXXXX, ''Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''''+ @.date1+''''' and (PMINVHST.HInvDate)<='''''+ @.date2+''''') AND (Trim([CPBASC_All].[SalesRoute])<>'''''''' And Trim([CPBASC_All].[SalesRoute])<>''''000'''')'')'

Print @.command

Print Results:

INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(XXXXXXXXX, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''20070901'' and (PMINVHST.HInvDate)<=''20070930'') AND (Trim([CPBASC_All].[SalesRoute])<>'''' And Trim([CPBASC_All].[SalesRoute])<>''000'')')

|||

I just copied and pasted your code and ran the query and got the same printed results. One thing I noticed is that it's putting 2 single quotes before and after each of your dates. Is this what you are wanting? Or are you wanting a regular quote? Or just a single, single quote?

'20070901'

''20070901''

"20070901"

|||

For everyone reading this if you ever run into a problem trying to use OpenQuery in an SP be aware that OpenQuery does not play nice. If you're problem is similar please try running your SP using this method. I battled this for a while and this was the only way I could make it work. OpenQuery does not like taking expressions and parameters directly.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author:

-- Create date: 10/4/2007

-- Description: This proc is designed to pull all CSA

-- part sales from XXXXX business system and upload them

-- into the local XXXXXX Database for commission reporting

-- =============================================

ALTERproc [dbo].[usp_CSAPartsSalesUpdate]

(

@.date1varchar(8),

@.date2varchar(8)

)

As

Declare @.commandasnvarchar(4000)

Set @.command='INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(LinkedServerName, ''Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''''+ @.date1+''''' and (PMINVHST.HInvDate)<='''''+ @.date2+''''') AND (Trim([CPBASC_All].[SalesRoute])<>'''''''' And Trim([CPBASC_All].[SalesRoute])<>''''000'''')'')'

Exec(@.command)

|||

Thanks Sswanner1 for all your efforts!!