Stored Procedure Input Parameter

Hi all, i'll put this question in a simple way..
I have this stored procedure:

-------------
CREATE PROCEDURE SPTEST @STR varchar(1000)
AS
PRINT @STR
GO
-------------


If in query analizer or in asp code i try this:
exec SPTEST "12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678X"

I receive an error:
The identifier that starts with '12345678.....345678' is too long. Maximum length is 128.



If i remove the last char ('X') in the Input Parameter it works!
Is there a way to pass an input parameter bigger than 128 length?

Thanx in advance!
LVL 1
sammuraiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi sammurai,

You are using " (double quote characters) instead of ' (single-quote characters) This  means that SQL query parser is interpreting your string as a column name rather than as a string. The maximum length for a column name is 128 characters which is why you get the error. Replace your " with ' and it should be fine.

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com

Experts-Exchange Advisory Board Member
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ashishjjCommented:
Hi,

  Try this

exec SPTEST '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678X'

Do not put double quotes, just single quotes

Ashish
0
sammuraiAuthor Commented:
Thanx a lot!
It worked
0
NANDA2002_USCommented:
I want to pass like this:  How??

"WHERE Location IN ('Beaumont Royal Oak','Bgread Hospital','CoimMedicalCenter','Crittenton Hospital','Gladwyn Ostepathic Care Center','Government Hospital','Patient Home','ABC HOSP','St Joseph Mercy Hospital') AND provider_code IN ('Tina','Davis','TCG','DG','MVK','Larson','DB','Dr Powell','SMITH','PRO1')"

Thanks In Advance
-Nanda
0
NANDA2002_USCommented:
Opps...

Its one of the paramenter for a stored procedure

Im Generating a Big Where class from the FrontEnd  Based on several Filter Conditions & Callin a stored procedure with this where class as parameters..

How to handle this..      

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.