CODEWITHSUNDEEP
sqlsql-server
Candy
Candy

Reputation: 431

sql server parameter size overloading

i am calling a stored procedure from my ASP.NET application. the stored procedure takes one parameter. the value that i am providing from my WEB Form is too large that it did not fully loading in variable of sql server. the data type of my sql server parameter is nvarchar(max) and the data type in my ASP.NET application is string. the stored procedure is as below

Create procedure p_getProducts
@nm_emp nvarchar(max)
AS
BEGIN

  select * from tblProduct where nm_user in(convert(nvarchar(max),@nm_emp));

END

please tell me which sql server data type i should use to overcome this problem.

Thanks.

Upvotes: 1

Views: 331

Answers (1)

Steve
Steve

Reputation: 216313

For what I could suppose from your code, you should work with dynamic-sql and not using directly the parameter as value for the IN clause. Try with this proc.

Create procedure p_getProducts
      @nm_emp nvarchar(max)
AS
    BEGIN
    DECLARE @SQL NVARCHAR(MAX); 
    SELECT @SQL = N'select * from tblProduct where nm_user in(' + 
                  @nm_emp + N')'

    EXEC sp_executeSQL @SQL

Upvotes: 1

Related Questions