Passing Character paramter to stored procedure

I need to pass to an SQL stored procedure a character parameter that exceeds
the nvarchar limit, what would be the best way to accomplish this?
Thank you.Tim,
The question itself begs another question being, why would you want to pass
a parameter exceeding 4000 characters whe, by inference, you are using an
nvarchar datatype with that limit?
What does this parameter do, or what's it's use once in the Stored
Procedure? Can it not be split, or can you not use standard varchar datatype
for the parameter?
"Tim Harvey" wrote:

> I need to pass to an SQL stored procedure a character parameter that excee
ds
> the nvarchar limit, what would be the best way to accomplish this?
>
> Thank you.
>
>|||It's used as a paramter to a stored procedure to return a dataset. I can
have the entire dataset returned and then filtered but that takes sometime
in vb.net. It's used as a filter to a dataset.
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:F3878BEC-E20B-48D3-8C92-C06BA59195CC@.microsoft.com...
> Tim,
> The question itself begs another question being, why would you want to
> pass
> a parameter exceeding 4000 characters whe, by inference, you are using an
> nvarchar datatype with that limit?
> What does this parameter do, or what's it's use once in the Stored
> Procedure? Can it not be split, or can you not use standard varchar
> datatype
> for the parameter?
> "Tim Harvey" wrote:
>|||Tim,
Can you post the first 100 characters of a standard parameter value passed
please, maybe that will assist me in understanding it's nature.
It may also be an advantage to post the SProc code itself to see how the
param is being used.
Thanks,
"Tim Harvey" wrote:

> It's used as a paramter to a stored procedure to return a dataset. I can
> have the entire dataset returned and then filtered but that takes sometime
> in vb.net. It's used as a filter to a dataset.
>
>
>
> "Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
> news:F3878BEC-E20B-48D3-8C92-C06BA59195CC@.microsoft.com...
>
>|||Here's ,y procedure, I would liek to filter the procdure before I get the
data back but as I said the string being passed exceeds the nvarchar limit
in some cases:
CREATE PROCEDURE srrptarCTBInvoiceApplied
@.GLShortPeriod varchar(10),
@.InvoiceIDs nvarchar(4000) = null
AS
--AR Reports 4-8-2004
--Version 1
DECLARE @.T_1 TABLE
(CTBType CHAR(1) NULL,
InvoiceID INT NULL,
TransType VARCHAR(3) NULL,
GLPeriod VARCHAR(15) NULL,
Amount FLOAT NULL,
AppliedFrom VARCHAR(50))
INSERT INTO @.T_1
SELECT 'I' as CTBType,
tblarOtherTransApplied.pkARInvoiceID AS InvoiceID,
tblarOtherTransApplied.fkARTransTypeID AS TransType,
trefgenPeriodValues.LongPeriodValue AS GLPeriod,
tblarOtherTransApplied.AROtherTransAmount AS Amount,
AppliedFrom = CASE tblarOtherTransApplied.fkARTransTypeID WHEN 'CM' THEN
'Credit Memo' WHEN 'WO' THEN 'Write Off' ELSE '' END + ' '
+ AROtherTransNumber
FROM tblarOtherTransApplied INNER JOIN
tblarInvoiceHeader ON tblarOtherTransApplied.pkARInvoiceID =
tblarInvoiceHeader.pkARInvoiceID INNER JOIN
trefgenPeriodValues ON tblarOtherTransApplied.fkGLPeriodID =
trefgenPeriodValues.pkPeriodID
WHERE dbo.trefgenPeriodValues.ShortPeriodValue <= @.GLShortPeriod
DECLARE @.T_2 TABLE
(CTBType CHAR(1) NULL,
InvoiceID INT NULL,
TransType VARCHAR(3) NULL,
GLPeriod VARCHAR(15) NULL,
Amount FLOAT NULL,
AppliedFrom VARCHAR(200) NULL)
INSERT INTO @.T_2
SELECT 'I' as CTBType,
dbo.tblArPaymentHeader.fkInvoiceHeader AS InvoiceID,
'PA' AS TransType,
dbo.trefgenPeriodValues.LongPeriodValue AS GLPeriod,
dbo.tblArPaymentHeader.headerAmount AS Amount,
'Payment ' + isnull(dbo.tblArPayment.customerCheckNum,'') + ' on ' +
CONVERT(char(10), dbo.tblArPayment.DatePaid, 101) + ' applied on ' +
CONVERT(char(10),
dbo.tblArPaymentHeader.postedSubLedger, 101) AS AppliedFrom
FROM dbo.tblArPaymentHeader INNER JOIN
dbo.tblarInvoiceHeader ON dbo.tblArPaymentHeader.fkInvoiceHeader =
dbo.tblarInvoiceHeader.pkARInvoiceID INNER JOIN
dbo.trefgenPeriodValues ON dbo.tblArPaymentHeader.fkPeriod =
dbo.trefgenPeriodValues.pkPeriodID INNER JOIN
dbo.tblArPayment ON dbo.tblArPaymentHeader.fkPmt = dbo.tblArPayment.keyPay
WHERE (dbo.tblArPaymentHeader.fkInvoiceHeader IS NOT NULL) AND
(dbo.tblArPaymentHeader.postedSubLedger IS NOT NULL) AND
(NOT (dbo.tblArPaymentHeader.fkPayClassheader IN ('AR', 'NR'))) AND
(dbo.trefgenPeriodValues.ShortPeriodValue <= @.GLShortPeriod)
DECLARE @.F TABLE
(CTBType CHAR(1) NULL,
InvoiceID INT NULL,
TransType VARCHAR(3) NULL,
GLPeriod VARCHAR(15) NULL,
Amount FLOAT NULL,
AppliedFrom VARCHAR(200) NULL)
INSERT INTO @.F
SELECT CTBType,InvoiceID,TransType,GLPeriod,Amo
unt,AppliedFrom FROM @.T_1
UNION ALL
SELECT CTBType,InvoiceID,TransType,GLPeriod,Amo
unt,AppliedFrom FROM @.T_2
IF @.InvoiceIDs is null
SELECT * FROM @.F
ELSE
SELECT * FROM @.F WHERE CHARINDEX( '~' + convert(nvarchar(20),InvoiceID )
+ '~', @.InvoiceIDs ) > 0
GO
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:6CCE0292-BFBC-4CF0-886E-01B47ED6B3BA@.microsoft.com...
> Tim,
> Can you post the first 100 characters of a standard parameter value passed
> please, maybe that will assist me in understanding it's nature.
> It may also be an advantage to post the SProc code itself to see how the
> param is being used.
> Thanks,
>
> "Tim Harvey" wrote:
>|||http://www.sommarskog.se/arrays-in-sql.html