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 a String into the InsertCommand of SqlDataSource at the @color character

Ok, so I'm a JSP guy and thing it should be easy to replace "@.color" with t_color after I initialized it to red by

String t_color = "red";

and then calling the insert

SqlDataSource1.Insert();

here is insert command:

InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, @.color)"

I've tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "+ t_color+")"

Ive tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "<%$ t_color %>" )"

Is there any easy way to do this?

or

Can I set it like

@.color = t_color?

Thanks in advance for ANY help

JSP turning ASP (Maybe)

Dan

Hi Dan,

Hope your day goes better!

If you are using @.something, this tells your database that a parameter is expected, in which case you would instantiate a new parameter and sent your string value.

your line would have worked (this one): InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "+ t_color+")"

had you remembered to use single-quotes around your color value (strings are interpreted in sql if they have single quotes around them)

so it would have been like this:

InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name,'"+ t_color+"')"

it know its hard to see, but in the end, the insertcommand recieved by your database server is...

INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, 'red') -- which would have worked.

hope this helps!!

|||

DARN!
THought that was it.

However, when I try '"+ t_color+"' i getParser Error Message:The server tag is not well formed.
when I try "'+ t_color+'" I getParser Error Message:The server tag is not well formed.

How avout setting the value of @.color to 'red' ?

Is that an option?

Sorry Im so green at this c# stuff

|||

In your GUI, look at the properties of the sqldatasource. click the [...] button for your insert command, and check to see if you have your parameters already created there.

if so, you can set your parameter value like this...

sdsMySQLDataSource.InsertParameters[

"color"].DefaultValue = t_string;

If you don't have any parameters, then you should add them using the GUI... you can do it programmatically though like this...

sdsMySQLDataSource.InsertParameters.Add(

newParameter("color",TypeCode.String,"black"));|||Incase you want to see my test code on this issue...

 Hereis the test code that I usedin testing your issue. it compiled nicely (I just don't have the tables to actually write the data to... cant test that)// add a parameter to the insert query string t_color = "red"; string myInsertSQL;// parameters myInsertSQL = "INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, @.color)"; sdsTestFiltering.InsertParameters.Add(new Parameter("color", TypeCode.String, "black")); sdsTestFiltering.InsertParameters["color"].DefaultValue = t_color;// sql injection myInsertSQL = "INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, '" + t_color + "')"; sdsTestFiltering.InsertCommand = myInsertSQL;

Pass value to stored procedure longer than 128 character

Hi all,

I want to pass a value longer more than 128 character but get an error.

what can i do?

I use sql 2005 and Visual Basic 6

thank'sI think you need to give a better description of the error; please give the exact message that you are getting. There is no problem passing an argument to a stored procedure that is longer than 128 characters. Also, can you potentially change the stored procedure and re-create or alter the stored procedure if the problem terns out to be the size of the argument?|||Thank for quikly response,

first i'm sorry about my english.

i was tried to pass parameter more small to the stored, and all was ok, the resultes are right.

I think the problem are only the lengh, i've only italian error description :

"La lunghezza del valore identificatore che inizia con '*MY PARAMETER*' è eccessiva.
La lunghezza massima consentita è 128."

I've edit stored for replace value more small to real value.

Example:

'VALUES EXPLANATION
SHORT VALUE REAL VALUE
DOF DATAORAFINE
DOS DATAORASCADENZA
DOI DATAORAINIZIO
'**************************************

'STORED CODE
SET @.CRITDATA = REPLACE(@.CRITDATA,'DOF','DATAORAFINE')
SET @.CRITDATA = REPLACE(@.CRITDATA,'DOS','DATAORASCADENZA')
SET @.CRITDATA = REPLACE(@.CRITDATA,'DOI','DATAORAINIZIO')

There is an other solution

thank's
Davide|||

Can you post the entire procedure? This works:

create procedure test
(
@.parameter varchar(200)
) as
select len(@.parameter), @.parameter
go
exec test '01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
go

Returns:

-- --
200 01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789

|||all right,

I've used double apen fore execute query from SQL MANAGEMENT STUDIO, now with the single apex work!

Thanks a lot|||

Just to be clear.

You were putting double quotes ("text") around the argument rather than single('text').

The problem was that putting double quotes around the text is the same as putting it in square brackets ([text]). This means that the text is treated as an identifier (and not as text), and identifiers in SQL Server are limited to 128 characters.

Thought we ought to make it clear what the problem had been and how it had been fixed.

|||

"You were putting double quotes ("text") around the argument rather than single('text'). "

Ah, nice catch. That went right over (past) my head, if only I could have read Italian (from google's translator:)

“The length of the identificatore value that it begins with “*MY PARAMETER*” is excessive. The concurred maximum length is 128.”

D'oh!