Query runs with error when passed parameters

Question

Query runs with error when passed parameters

kd_um

SSC-Addicted

Points: 460
More actions
January 8, 2013 at 6:22 am

#268475

Hi experts,
I am trying to create this query and essentially trying to pass parameter (@CardName) in table created by another query but it's giving error. I know I am doing something basic wrong in passing parameters because without them the query is running fine. Could someone please guide me? Thank you in advance.
Best Regards.
KD
Use EMMCLIVE;
DECLARE @CardName AS nvarchar(100)
/* WHERE */
SET @CardName = '[%0]'
SELECT
TB.InvoiceRef, TB.InvoiceDate, TB.Customer, TB.GrossAmount, TB.PayRef, TB.PayDate, TB.PaymentMadeOn, TB.DaystoPay, AVG(TB.DaystoPay) OVER () AS 'Avg Days'
FROM
(SELECT T2.[DocNum] as 'InvoiceRef', T2.[DocDate] as 'InvoiceDate', T2.[CardName] as 'Customer', T2.[DocTotal] as 'GrossAmount', T0.[DocNum] as 'PayRef', T1.[DocDate] as 'PayDate',
CASE WHEN T0.InvType = 203 THEN'A/R Down Payment'
WHEN T0.InvType = 13 THEN'A/R Invoice'
WHEN T0.InvType = 14 THEN'A/R Credit Memo'
WHEN T0.InvType = 204 THEN'A/P Down Payment'
WHEN T0.InvType = 18 THEN'A/P Invoice'
WHEN T0.InvType = 19 THEN'A/P Credit Memo'
WHEN T0.InvType = 24 THEN'Incoming Payment'
WHEN T0.InvType = 25 THEN'Deposit'
WHEN T0.InvType = 46 THEN'Payment Advice'
WHEN T0.InvType = 57 THEN'Checks for Payment'
WHEN T0.InvType = 76 THEN'Postdated Deposit'
WHEN T0.InvType = -2 THEN'Opening Balance'
WHEN T0.InvType = -3 THEN'Closing Balance'
WHEN T0.InvType = 30 THEN'Journal Entry'
WHEN T0.InvType = -1 THEN'All Transactions'
WHEN T0.InvType = 163 THEN'A/P Correction Invoice'
WHEN T0.InvType = 165 THEN'A/R Correction Invoice'
END AS 'PaymentMadeOn', DATEDIFF(DAY, T2.[DocDate], T1.[DocDate]) AS 'DaystoPay', AVG(DATEDIFF(DAY, T2.[DocDate], T1.[DocDate])) OVER () AS 'OverallAvg'
FROM
[dbo].[RCT2] T0 INNER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum
INNER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry
WHERE
T2.[DocStatus] like 'C' and T2.cardname LIKE @CardName
UNION ALL
SELECT
T2.[DocNum] AS 'InvoiceRef', T2.DocDate as 'InvoiceDate', T2.CardName as 'Customer', T2.[DocTotal] as 'GrossAmount', T0.[ReconNum] as 'PayRef', T0.[ReconDate] as 'PayDate', 'Account (or Internal Rec)' as 'PaymentMadeOn', DATEDIFF(DAY, T2.DocDate, T0.[ReconDate]) AS 'DaystoPay', AVG(DATEDIFF(DAY, T2.DocDate, T0.[ReconDate])) OVER () AS 'OverallAvg'
FROM
OITR T0 INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum INNER JOIN OINV T2 on T1.TransId=T2.TransId
WHERE
T2.[CardCode] = T1.[ShortName] AND T0.[ReconType] = 0 and T2.cardname LIKE @CardName
) TB
WHERE TB.CUSTOMER LIKE @CardName

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

What error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 2

Sorry for missing that out. The error is:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@CardName". 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

And exactly how are you calling that piece of code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 4

I am running it directly in the application's (SAP in this case) query generator.

Tried to run it SQL server management studio but it runs without error but with no result, so thought its not the place to run it.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

How *exactly* are you calling that? Are you calling the entire piece as you've written there? Are you calling just the query? If so, have you defined the parameters?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 6

I am calling the entire piece as written, with parameter @CardName declared exactly how I have above.

Regards.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

As you have it above, @CardName is not a parameter, it's a local variable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 8

Thanks for your reply.

I have brain freeze right now. Without disputing what you just said, I have more queries successfully running with the same syntax; what's uniquly stupid I am doing?

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 9

Fair warning. I only know the barest bit about SAP because I work with someone in another state who uses it. That being said, does this article help?

http://scn.sap.com/thread/1703641

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

kd_um SSC-Addicted Points: 460 More actions · Answer 10

Hi Brandie,

You pointed me to the right direction and sometimes that's the best thing to do.

Added this in the start of my query and everything was good:

/* SELECT FROM [dbo].[OINV] T2 */

DECLARE @Cust AS nvarchar(100)

/* WHERE */

SET @Cust = /* T2.CardName */ '[%0]'

[Although I must admit I spent more time reading your website and profile then working on the solution.]

Thank you.

Best Regards

Kanu

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 12

Glad I could help. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 13

GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?

Gail, believe it or not, that additional comment is required in the SAP interface. The code failed because it wasn't in his original code.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

kd_um SSC-Addicted Points: 460 More actions · Answer 14

Brandie Tarvin (1/8/2013)
GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?
Gail, believe it or not, that additional comment is required in the SAP interface. The code failed because it wasn't in his original code.

And I am looking for the reason why this extra bit is required, or rather how does a comment make so much difference to a program.