Consider the following stored procedure:
CREATE PROCEDURE spGetResult
@empname varchar(5000),
@ccode varchar(10),
@month varchar(5),
@year varchar(5)
AS
DECLARE
@sql varchar(8000)
SET @sql='SELECT * FROM tblName WHERE EName IN (' + @empname + ') AND............'
EXEC (@sql)
If the above procedure is invoked from an ASP page using the following SQL statement (please do not go through the entire EXEC statement. I just want to show the length of the parameter that is being passed to the variable @empname in the stored procedure. As such, there is nothing wrong with the statement - no syntax error.....nothing):
EXEC spGetResult 'QL',"'MR AVINASH MULKY','MR A JOHN GERALD MANOJ','MR B T JUMANI','MR BIJU GEORGE','MR C CHANDRA SEKHAR','MRS ELIZABETH SHYAM','MR J U V NAGABHUSHANAM','MR KRISHANA PABSETTI','MR M RAGHURAM','MR N RENGARAJAN','MR N GURU NAGENDRAKUMAR','MR P P SIDDHARTHA','MR P NARASIMHARAJU','MR SHILADITYA GOSH','MR S ASHOK KUMAR','MR S MALHOTRA','MR V BALAKRISHANAN','MR VASANT KALA','MR VINAYAK KAPSE','MR MANOHAR G M','MS K SATYA VANI','MR SUJIT KUMAR KRISHANA','MR S NELSON','MR SANJAY MESTRY','MR SAMANTARAY CHITTARANJAN','MR SURJIT MUKHERJI','MR SANJAY CHAVAN','MR SURESH DANIEL','MS MABEL D','MR A H C V KUMAR','MS PREETA PAIS','MR S ADIKESSAVANE','MR LAWRENCE ROSARIO','MR RAVISANKAR DAMMU',",04,2001
I am getting the following Microsoft OLEDB error:
The identifier that starts with 'MR AVINASH MULKY','MR A JOHN GERALD MANOJ','MR B T JUMANI','MR BIJU GEORGE','MR C CHANDRA SEKHAR','MRS ELIZABE' is too long. Maximum length is 128.
If only a few names are passed, say the 1st 4 or 5 names, the procedure gets executed without any problems. Which identifier is being referred to in the error message? How do I overcome this error? Please note that if the same EXEC statement is executed in the Query Analyzer, it gets executed without any errors.
Thanks,
LOL
Arpan
CREATE PROCEDURE spGetResult
@empname varchar(5000),
@ccode varchar(10),
@month varchar(5),
@year varchar(5)
AS
DECLARE
@sql varchar(8000)
SET @sql='SELECT * FROM tblName WHERE EName IN (' + @empname + ') AND............'
EXEC (@sql)
If the above procedure is invoked from an ASP page using the following SQL statement (please do not go through the entire EXEC statement. I just want to show the length of the parameter that is being passed to the variable @empname in the stored procedure. As such, there is nothing wrong with the statement - no syntax error.....nothing):
EXEC spGetResult 'QL',"'MR AVINASH MULKY','MR A JOHN GERALD MANOJ','MR B T JUMANI','MR BIJU GEORGE','MR C CHANDRA SEKHAR','MRS ELIZABETH SHYAM','MR J U V NAGABHUSHANAM','MR KRISHANA PABSETTI','MR M RAGHURAM','MR N RENGARAJAN','MR N GURU NAGENDRAKUMAR','MR P P SIDDHARTHA','MR P NARASIMHARAJU','MR SHILADITYA GOSH','MR S ASHOK KUMAR','MR S MALHOTRA','MR V BALAKRISHANAN','MR VASANT KALA','MR VINAYAK KAPSE','MR MANOHAR G M','MS K SATYA VANI','MR SUJIT KUMAR KRISHANA','MR S NELSON','MR SANJAY MESTRY','MR SAMANTARAY CHITTARANJAN','MR SURJIT MUKHERJI','MR SANJAY CHAVAN','MR SURESH DANIEL','MS MABEL D','MR A H C V KUMAR','MS PREETA PAIS','MR S ADIKESSAVANE','MR LAWRENCE ROSARIO','MR RAVISANKAR DAMMU',",04,2001
I am getting the following Microsoft OLEDB error:
The identifier that starts with 'MR AVINASH MULKY','MR A JOHN GERALD MANOJ','MR B T JUMANI','MR BIJU GEORGE','MR C CHANDRA SEKHAR','MRS ELIZABE' is too long. Maximum length is 128.
If only a few names are passed, say the 1st 4 or 5 names, the procedure gets executed without any problems. Which identifier is being referred to in the error message? How do I overcome this error? Please note that if the same EXEC statement is executed in the Query Analyzer, it gets executed without any errors.
Thanks,
LOL
Arpan