Text Parameter Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Text Parameter Problems

Author

Topic

Speeder
Starting Member

4 Posts

Posted - 2005-09-22 : 12:51:21

Hi All,

Help!

I have a stored procedure that accepts a 'text' parameter.
e.g.

CREATE PROCEDURE sp_test
@input text
AS
/* Do something with the @input parameter, etc, etc */

The problem I have is that it appears to be impossible to pass values that have more than 8000 characters. e.g.

DECLARE @value varchar(8000) /* maximum! */
SELECT @value = value FROM tbl_TEST_TABLE WHERE ID=1
EXEC sp_test @value

The value I want from the table is greater than 8000 characters.
It won't allow you to concatenate two variables either...
e.g. EXEC sp_test @value1 + @value2

Any ideas?

Thanks in advance.

Speeder.

ronstone
Starting Member

32 Posts

Posted - 2005-09-22 : 16:41:32

Wouldn't you need to declare a variable that can hold more than 8000 characters to start with?

The column value from tbl_TEST_TABLE must not be a varchar(8000) if it's returning data larger than 8000 chars...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 02:20:23

Try this

DECLARE @value1 varchar(8000) /* maximum! */
DECLARE @value2 varchar(8000) /* maximum! */
SELECT @value1 = substring(value,1,8000) FROM tbl_TEST_TABLE WHERE ID=1
SELECT @value2 = substring(value,8001,16000) FROM tbl_TEST_TABLE WHERE ID=1
EXEC sp_test @value1 + @value2

Madhivanan

Failing to plan is Planning to fail

Speeder
Starting Member

4 Posts

Posted - 2005-09-23 : 04:44:33

Madhivanan, thanks but you can't pass an expression to a stored procedure. I mentioned your suggestion in my first post.

Ronstone, the data in the table is greater than 8000 characters yes, but you can't declare a variable greater than 8000, this is the crux of the problem.

Speeder.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 04:48:31

Did you try that query?
You want to get text vale and pass that as parameter to the stored procedure
If this is the case the method I suggested is right I think

Madhivanan

Failing to plan is Planning to fail

Speeder
Starting Member

4 Posts

Posted - 2005-10-13 : 11:16:05

Madhivanan, thanks for the suggestion but I said in my very first post that your method does not work.

SQL Server does not allow an 'expression' to be passed as a parameter to a stored procedure.

Hence:
EXEC sp_test @value1 + @value2
is not valid.

Speeder.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-13 : 12:26:24

True TEXT parameters can only be passed to a stored procedure from a front-end application, since SQL Server 2000 does not allow you to declare a local TEXT parameter.

If you need to pass this data within SQL, create a temporary table with a TEXT column, and have the stored procedure get the data from that table.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources