0

l had a parameter in my stored procedure that was varchar(2000), my problem now is the characters being sent on the parameter have grown, the parameter now needs to handle 30 000 characters which are comma-separated. The biggest variable type that am now using (TEXT) is giving me issues in my where clause as below

Now using TEXT data type 

CREATE PROCEDURE
    (@ArrayList TEXT =''  -- '93238128,93238131,93238130,93238133,93238132 ......
   )

then in my where clause

WHERE c.CLAIM_ID IN (SELECT Element FROM dbo.Split(@ArrayList ,',')) 
  AND @ArrayList <>'') OR @ArrayList =''

Getting this error :

The data types text and varchar are incompatible in the not equal to operator.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prince
  • 43
  • 9
  • 2
    ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. – HoneyBadger Jul 24 '19 at 11:27
  • Don't use `text`, use `varchar(max)`. – Gordon Linoff Jul 24 '19 at 11:27
  • Thank you for your response . l have tried varchar(max) but it can not handle 30 000 characters – Prince Jul 24 '19 at 11:32
  • 1
    What about using [arrays](https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) ? – Cid Jul 24 '19 at 11:34
  • 1
    @Prince, `varchar(max)` can hold 2GB, 30.000 characters shouldn't be a problem. – HoneyBadger Jul 24 '19 at 13:32
  • Thank you for your input solution now working , Used arrays as suggested by @Cid – Prince Jul 25 '19 at 07:33

1 Answers1

0

Here's what l ended up using Tables! And it works great. Sending a table with one column to a stored procedure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prince
  • 43
  • 9