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

Passing an Array to a Stored Procedures

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@.Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@.Tb1No) /* How to Passing an Array to a Stored Procedures ? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !If you are using SQL 2000 you can create a user-defined fnction - have a look at the following link:

Sql Team|||I Try CnvToChar() is work...

select * from CsvToChar('PO001234,PO123') is work

CharValue
-----
PO001234
Po123
-----

but,

=====================================================
select * from Tb1 Where TbNo IN (select * from CsvToChar('PO001234,PO123'))
have error message:

Server: Message 446, Level 16, Status 9, Line 1
can not to analyze 'equal to' action order collide.
=====================================================

thank you help...|||My SQL Server is Chinese System,
Character Record Fields in Table is default 'COLLATE Chinese_Taiwan_Stroke_BIN' vaule,
so i add the Default value in the function CvsTochar() return value.

==============================================
CREATE Function dbo.CsvToChar ( @.Array varchar(1000))
returns @.CharTable table
(CharValue char(10) COLLATE Chinese_Taiwan_Stroke_BIN)
AS
begin

declare @.separator char(1)
set @.separator = ','
.........
.........
===============================================

No error message to display, It's work... ^_^ Y

Ehorn - Thank you very much !!!

Johnny SCB

Passing a string report parameter to a stored proc

I have created a report that uses a SQL stored procedure. I am passing 3
parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
created 3 report parameters and I am passing them to the stored proc via the
parameters tab for the dataset. In visual studio the closest option to
NVARCHAR I have for the report parameter is string. If I run the sp from the
data area and pass it the params it works fine but when I try to preview the
report and enter the params I get no data (also no error message or anything)
I'm thinking I may have to do some sort of conversion but I'm not sure what
it would be. Any help would be aprrciated.Be sure the store proc parameter is defined in your Report parameters list.
"Jan Shulse" wrote:
> I have created a report that uses a SQL stored procedure. I am passing 3
> parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
> created 3 report parameters and I am passing them to the stored proc via the
> parameters tab for the dataset. In visual studio the closest option to
> NVARCHAR I have for the report parameter is string. If I run the sp from the
> data area and pass it the params it works fine but when I try to preview the
> report and enter the params I get no data (also no error message or anything)
> I'm thinking I may have to do some sort of conversion but I'm not sure what
> it would be. Any help would be aprrciated.