Using Variables in T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using Variables in T-SQL

Author

Topic

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 11:54:05

Can someone please tell me where I'm going wrong with this SP?
I'm trying to pull in two variables and use them in a query against a linked server.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 10/4/2007
-- Description: This proc is designed to pull all CSA
-- part sales from XXXX business system and upload them
-- into the local XXXXX Database for commission reporting
-- =============================================
ALTER proc [dbo].[usp_CSAPartsSalesUpdate]
(
@date1 varchar(8),
@date2 varchar(8)
)

As

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'')')

This is the error message I get:
Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 19
Incorrect syntax near '+'.

If I remove the single quote so that I do not escape in my openquery statement the error will go away but instead of pulling in the variable value it does a comparison of "@date1" against my date field in the database. How do I get the variable I'm pulling into the SP to work inside of the Openquery statement?

Kristen
Test

22859 Posts

Posted - 2007-10-05 : 12:12:29

OpenQuery cannot take an expression, so you will have to construct the concatenated string query into a variable first, and then use that as the parameter

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 12:18:30

Try this:


Declare @sql nvarchar(4000)

Set @sql = N'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(@sql)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 12:20:30

quote:
Originally posted by Kristen

OpenQuery cannot take an expression, so you will have to construct the concatenated string query into a variable first, and then use that as the parameter

Kristen

OPENQUERY does not like parameters either!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 12:32:59

Harsh Athalye~

I need to be able to push values into the SP at run time for Date1 and Date2. Can you please explain how this is done given your method?

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 12:35:02

If I run this SP and then copy and paste the results to query editor it returns exactly what I need. The problem is I need to set values to Date1 and Date2 at run time.

Declare @command as nvarchar(4000)

Declare @date1 as varchar(8)

Declare @date2 as varchar(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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 12:35:18

So?

I am also using @Date1 and @Date2 parameters of your SP to construct the text of @SQL variable.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 12:36:32

quote:
Originally posted by wrbenson

If I run this SP and then copy and paste the results to query editor it returns exactly what I need. The problem is I need to set values to Date1 and Date2 at run time.

Declare @command as nvarchar(4000)

Declare @date1 as varchar(8)

Declare @date2 as varchar(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

Exec(@command)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 12:40:07

Harsh ~
This is the code I have put in for my SP and it looks like it will work, if I can figure out how to assign @date1 and @date2 values at run time.

Thanks

Declare @sql nvarchar(4000)
Declare @date1 as nvarchar(8)
Declare @date2 as nvarchar(8)

Set @sql = N'INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )
SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(BeardQueries,
''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(@sql)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 12:42:44

But of course, @date1 and @date2 were parameters of your original SP? Why you are declaring them as local variables?

ALTER proc [dbo].[usp_CSAPartsSalesUpdate]
(
@date1 varchar(8),
@date2 varchar(8)
)

As
Declare @sql nvarchar(4000)

Set @sql = N'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(@sql)
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 12:44:51

Harsh
In my example above you will notice that date1 and date2 have values assigned to them before the procedure is run:

Set @date1 = 20070901

Set @date2 = 20070930

I need these values not to be assinged before the SP is run. I need the SP to look for the values to be passed to it at run time so I can run this Sp everymonth feeding it new values for the current month without having to edit the SP.

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 12:46:57

Harsh...
AHHHHHHH your last post is probably exactly what I'm looking for.

I'm new to using SPs when I see your last post the lightbulb comes on!!

Thanks =)

wrbenson
Starting Member

7 Posts

Posted - 2007-10-05 : 12:58:23

Pefect Harsh!

Thanks so much I can go eat lunch in peace now =)

Kristen
Test

22859 Posts

Posted - 2007-10-05 : 13:32:32

"OPENQUERY does not like parameters either! "

Yup, you are quite right. I misremembered that we always had to make a SQL String for eh OpenQuery parameter, but in fact I've checked and we have to make it for the whole thing ...

.. pity really, 'coz the doubling-up-of-the-doubled-up-quotes gets to be a nuisance.

In fact we don't double-double them up, we use a REPLACE to do that, so that the original is more readable.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-08 : 04:38:23

Quite right Kristen! Using REPLACE() would be much smarter option than doubling up the quotes manually.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 05:04:46

Just in case it helps anyone here is an example:


DECLARE	@strSQL	varchar(8000)

-- Actual query
SELECT @strSQL = '
SELECT	T1.MyCol1,
	T1.MyCol2
FROM	MyDB.MyTable AS T1
WHERE
	    T1.MyCol1 >= ''ABC''	-- embedded quote
	AND T1.MyDate =
		(
			SELECT	MAX(T2.MyDate) AS MAX_MyDate
			FROM	MyDB.MyTable AS T2
			WHERE	T2.MyPK = T1.MyPK
		)
ORDER BY T1.MyDate, T1.MyPK
'

SELECT [RawQuery] = @strSQL	-- embedded single-quotes

-- Convert into an OPENQUERY, doubling any embedded quotes.
SELECT @strSQL = '
SELECT	MyCol1,
	MyCol2
FROM	OPENQUERY(RemoteServer, ''
' 
	+ REPLACE(@strSQL, '''', '''''') 
	+ '''
	)'

SELECT [OpenQuery] = @strSQL	-- doubled-up embedded single-quotes

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources