Identifier too long exception in SQL server 7

[sreenath reddy]

Hi

I have a stored procedure in SQL server 7 which takes a parameter and executes the query . It looks like

BEGIN
EXEC('DELETE FROM #TMPTable WHERE NEDevice NOT IN ('+@DeviceFilter+')')
END

and the DeviceFilter value will be passed as 'value1','value2',.............

But if the length of that parameter exceeds 128 , its throwing an exception saying Identier is too long , Maximul length is 128

i am using jtds driver

cn any one let me know the problem

[Paul Sturrock]

Just curious, why have you written this as a Stored Procedure instead of a simple piece of SQL in your application? I don't know if it will help if you change it though, but a Stored Procedure feels like an unecessary over-complication here.

[sreenath reddy]

Hi paul

Thats just an part of the stored procedure and it has many more SQL commands after this statement

Let me know if u have any idea

I can give some more hints on how its behaving

if i execute from query analyzer using

<SP_NAME> "'value1','Value2','Value3'" . It works fine but as soon as that paramter values seperated by comma exceeds 128 characters , that gives a problem

[Paul Sturrock]

Ah. Well, I should have thought of this earlier, but I don't think T-SQL allows you to pass multiple values for a single parameter. I think the normal way round it is to call a procedure repeatedly for each value, or to pass all the values as CSV and do some string processing in your procedure.

[sreenath reddy]

hi paul

I am passing the values as CSV only . U might have seen that "'Value1','Value2'..."

and what is this string processing u were talking abt in the procedure ?? what do i need to do there

[Paul Sturrock]

Not quite, this would be handling the parameter as CSV:

 
<SP_NAME> 'value1, Value2, Value3'

Notice the difference in the type of quotes I use. SQL server understands the contents of double quotes as something different then single quotes. All literal string values used in T-SQL/SQL (as far as SQL Server is concerned) should be defined in single quotes.

The string manipulation stuff I mention are T-SQL functions like SUBSTRING. This is one way of breaking your single-value parameter up into the multiple values you need. However its far better to call the procedure repeatedly rather than trying to interpret a single parameter value like this. You would just be introducing another point where the code could go wrong.
[ June 15, 2005: Message edited by: Paul Sturrock ]