?
Solved

Stored Procedure Input Parameter

Posted on 2003-03-19
5
Medium Priority
?
1,872 Views
Last Modified: 2007-12-19
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!
0
Comment
Question by:sammurai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 180 total points
ID: 8165998
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
 

Expert Comment

by:ashishjj
ID: 8166025
Hi,

  Try this

exec SPTEST '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678X'

Do not put double quotes, just single quotes

Ashish
0
 
LVL 1

Author Comment

by:sammurai
ID: 8166355
Thanx a lot!
It worked
0
 

Expert Comment

by:NANDA2002_US
ID: 8704261
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
 

Expert Comment

by:NANDA2002_US
ID: 8704272
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

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question