November 10, 2016 at 10:54 am
Hello, I have the following code below that is throwing the following error:
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.
I have been looking at this too long and just need a second pair of eyes to help me fix the issue. I actually want this to be a stored procedure when finished and I will be passing in the declared parameters below. Any assistance is greatly appreciated.
DECLARE
@DBName NVARCHAR(25) = 'CRHC',
@BegDate DATE = '9/1/2016',
@EndDate DATE = '9/1/2016'
EXECUTE sp_executesql
N'USE @DBName; SELECT DISTINCT
b.vendname AS [Vendor Name],
c.APFRDCNM AS [Applied From Check Number],
'' '' as [Applied To Invoice Number],
CONVERT(VARCHAR(12), c.glpostdt, 101) AS [Post To GL Date],
CASE WHEN c.doctype = 6 THEN ''VOUCHER'' END AS [Document Type],
CASE WHEN c.APTODCTY = 1 THEN ''INVOICE'' END AS [Applied To Document Type],
C.VCHRNMBR AS [Voucher Number],
PMC.DOCAMNT AS DocumentAmount
,am.ACTNUMST AS GL_ACCOUNT
,GL.CRDTAMNT AS [Credit]
,GL.DEBITAMT AS [Debit]
FROM
PM30300 c INNER JOIN PM00200 b (NOLOCK) ON C.vendorid = b.vendorid
INNER JOIN PM30200 PMC ON PMC.VCHRNMBR = C.VCHRNMBR and PMC.VENDORID = c.VENDORID
LEFT JOIN GL20000 GL ON C.VCHRNMBR = GL.ORCTRNUM and C.vendorid = GL.ORMSTRID
INNER JOIN GL00105 am ON GL.ACTINDX = am.ACTINDX
WHERE
c.applyfromglpostdate BETWEEN @BegDate AND @EndDate
UNION
SELECT DISTINCT
b.vendname AS [Vendor Name],
c.APFRDCNM AS [Applied From Check Number],
''_'' + c.APTODCNM AS [Applied To Invoice Number],
CONVERT(VARCHAR(12), c.glpostdt, 101) AS [Post To GL Date],
CASE WHEN c.doctype = 6 THEN ''VOUCHER'' END AS [Document Type],
CASE WHEN c.APTODCTY = 1 THEN ''INVOICE'' END AS [Applied To Document Type],
a.VCHRNMBR AS [Voucher Number],
c.ActualApplyToAmount as DocumentAmount
,ama.ACTNUMST AS INV_ACCOUNT
,a.CRDTAMNT AS INVOICE_CREDIT
,a.DEBITAMT AS INVOICE_DEBIT
FROM
PM30600 a (NOLOCK) INNER JOIN PM00200 b (NOLOCK) ON a.vendorid = b.vendorid
INNER JOIN PM30300 c (NOLOCK) ON a.vendorid = c.vendorid AND a.VCHRNMBR = c.APTVCHNM
JOIN PM30200 PMI ON PMI.VCHRNMBR = c.APTVCHNM
INNER JOIN GL00105 ama ON a.DSTINDX = ama.ACTINDX
WHERE
c.applyfromglpostdate BETWEEN @BegDate AND @EndDate
ORDER BY
[Vendor Name] ,
[Applied From Check Number],
[Applied To Invoice Number]'
,'@DBName NVARCHAR(25), @BegDate DATE, @EndDate DATE',
@DBName = @DBName, @BegDate = @BegDate, @EndDate = @EndDate
Thank you,
David
November 10, 2016 at 11:18 am
As the error message suggests, try changing this line
,'@DBName NVARCHAR(25), @BegDate DATE, @EndDate DATE',
to this
,N'@DBName NVARCHAR(25), @BegDate DATE, @EndDate DATE',
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2016 at 11:36 am
Okay, that resolved that error, thank you. I am still getting the following error"
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@DBName'.
Thank you,
David
November 10, 2016 at 11:58 am
oradbguru (11/10/2016)
Okay, that resolved that error, thank you. I am still getting the following error"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@DBName'.
Thank you,
David
OK, I think that the problem is that you cannot parameterise USE statements. This simple example backs this up:
DECLARE @sql NVARCHAR(2000) = 'Use @DBName';
DECLARE @params NVARCHAR(1000);
DECLARE @DBName sysname = 'master';
SET @params = N'@DBName sysname';
EXEC sys.sp_executesql @sql, @params, @DBName;
Which results in
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@DBName'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2016 at 12:23 pm
That's disappointing.
November 10, 2016 at 12:30 pm
Here's a safe option for you:
DECLARE
@DBName NVARCHAR(25) = 'CRHC',
@BegDate DATE = '9/1/2016',
@EndDate DATE = '9/1/2016',
@SQL NVARCHAR(MAX)
SELECT @SQL = N'USE ' + QUOTENAME( db.name) + ';
SELECT DISTINCT
b.vendname AS [Vendor Name],
c.APFRDCNM AS [Applied From Check Number],
'' '' as [Applied To Invoice Number],
CONVERT(VARCHAR(12), c.glpostdt, 101) AS [Post To GL Date],
CASE WHEN c.doctype = 6 THEN ''VOUCHER'' END AS [Document Type],
CASE WHEN c.APTODCTY = 1 THEN ''INVOICE'' END AS [Applied To Document Type],
C.VCHRNMBR AS [Voucher Number],
PMC.DOCAMNT AS DocumentAmount
,am.ACTNUMST AS GL_ACCOUNT
,GL.CRDTAMNT AS [Credit]
,GL.DEBITAMT AS [Debit]
FROM
PM30300 c INNER JOIN PM00200 b (NOLOCK) ON C.vendorid = b.vendorid
INNER JOIN PM30200 PMC ON PMC.VCHRNMBR = C.VCHRNMBR and PMC.VENDORID = c.VENDORID
LEFT JOIN GL20000 GL ON C.VCHRNMBR = GL.ORCTRNUM and C.vendorid = GL.ORMSTRID
INNER JOIN GL00105 am ON GL.ACTINDX = am.ACTINDX
WHERE
c.applyfromglpostdate BETWEEN @BegDate AND @EndDate
UNION
SELECT DISTINCT
b.vendname AS [Vendor Name],
c.APFRDCNM AS [Applied From Check Number],
''_'' + c.APTODCNM AS [Applied To Invoice Number],
CONVERT(VARCHAR(12), c.glpostdt, 101) AS [Post To GL Date],
CASE WHEN c.doctype = 6 THEN ''VOUCHER'' END AS [Document Type],
CASE WHEN c.APTODCTY = 1 THEN ''INVOICE'' END AS [Applied To Document Type],
a.VCHRNMBR AS [Voucher Number],
c.ActualApplyToAmount as DocumentAmount
,ama.ACTNUMST AS INV_ACCOUNT
,a.CRDTAMNT AS INVOICE_CREDIT
,a.DEBITAMT AS INVOICE_DEBIT
FROM
PM30600 a (NOLOCK) INNER JOIN PM00200 b (NOLOCK) ON a.vendorid = b.vendorid
INNER JOIN PM30300 c (NOLOCK) ON a.vendorid = c.vendorid AND a.VCHRNMBR = c.APTVCHNM
JOIN PM30200 PMI ON PMI.VCHRNMBR = c.APTVCHNM
INNER JOIN GL00105 ama ON a.DSTINDX = ama.ACTINDX
WHERE
c.applyfromglpostdate BETWEEN @BegDate AND @EndDate
ORDER BY
[Vendor Name] ,
[Applied From Check Number],
[Applied To Invoice Number];'
FROM sys.databases db
WHERE db.name = @DBName;
EXECUTE sp_executesql
@SQL, N'@DBName NVARCHAR(25), @BegDate DATE, @EndDate DATE',
@DBName = @DBName, @BegDate = @BegDate, @EndDate = @EndDate;
It prevents SQL Injection and validates the database name. It's a double protection, but might be worth it.
November 10, 2016 at 12:36 pm
That's clever, Luis 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2016 at 12:47 pm
That works. Brilliant Luis!
Thank you,
David
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply