sql server parameter size overloading

Question

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.

why are you converting the variable into the same data type? — John Woo
– John Woo, Commented Mar 2, 2013 at 13:30
You have a logic problem here. A parameter passed in this way cannot expand itself to a list of IN values. Your query will never work. (at least not if @nm_emp is something like 'joe','mark','steve' ) — Steve
– Steve, Commented Mar 2, 2013 at 13:37
@steve, yes i am formating the values in c# like 'joe','mark','steve' and save it in variable and pass it to the stored procedure. they are working well but when the size of text in parameter increased then it creates problem — Candy
– Candy, Commented Mar 2, 2013 at 13:42
@jw i have tried it by eliminating conversion but still the problem occures. — Candy
– Candy, Commented Mar 2, 2013 at 13:44
Are you sure? Have you tried with more that one name and the results include both names? — Steve
– Steve, Commented Mar 2, 2013 at 13:45

Steve · Accepted Answer · 2013-03-02 15:12:09Z

1

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

edited Mar 2, 2013 at 15:12

answered Mar 2, 2013 at 13:48

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tony Hopkinson Over a year ago

As long as his parameter doesn't exceed about 3960 unicode chars. :)

Martin Smith Over a year ago

Possible SQL injection issue depending on where the value in @nm_emp comes from.

YvesR Over a year ago

@MartinSmith if he calls this function from ASP.NET using procedure ADO function and a good sanitizer he can manage to make safe calls imho.

Martin Smith Over a year ago

I didn't say it wasn't possible to but for all we know it just takes the values directly from Request.Form["MultiSelectListbox"]

Collectives™ on Stack Overflow

sql server parameter size overloading

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related