Sql query could not fetch value on in clause when storeprocedure is used

Question

0.00/5 (No votes)

See more:

I have created sql query which works in normal dataset, When iam trying to use same query using store procedure, result does not fetch the value.

SQL

string datatypeval="'first name','address'";
 string FieldNames1 = "select FId,DataTypeName from tbl_fieldname where DataTypeName in(" + datatypeval + ");";

Above query works perfectly.

When using store procedure, it doesnt fetch output value.

SQL

DataTable dt1 = dataval(datatypeval);


public static DataTable dataval(string datatypeval)
    {
        try
        {
            DataTable dt1;
            string[] ColumnName = new string[1];
            string[] values = new string[1];
            ColumnName[0] = "@dataval"; values[0] = datatypeval;
            dt1 = returnDatatable("datatype_Details", ColumnName, values);
            return dt1;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
public static DataTable returnDatatable(string SPName, string[] ParameterName, string[] Values)
    {
        DataTable ds = new DataTable();
        try
        {
            openDBConnection();
            SqlConnection mssqlConn = mssqlConnection;
            SqlCommand Cmd = new SqlCommand(SPName, mssqlConn);
            SqlDataAdapter da = new SqlDataAdapter(Cmd);
            Cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < ParameterName.Length; i++)
            {
                Cmd.Parameters.AddWithValue(ParameterName[i], Values[i]);
            }
            da.Fill(ds);
        }
        catch (Exception ex)
        {
           throw ex;
        }
        finally
        {
            closeDBConnection();
        }
        return ds;
    }

My SP Code:

SQL

ALTER PROCEDURE [dbo].[datatype_Details]
(
@dataval VARCHAR 
) 
AS
BEGIN
	 
	select FId,DataTypeName from tbl_fieldname  where DataTypeName in(@dataval)
END

What I have tried:

SQL

DataTable dt1 = dataval(datatypeval);
public static DataTable dataval(string datatypeval)
    {
        try
        {
            DataTable dt1;
            string[] ColumnName = new string[1];
            string[] values = new string[1];
            ColumnName[0] = "@dataval"; values[0] = datatypeval;
            dt1 = returnDatatable("datatype_Details", ColumnName, values);
            return dt1;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
public static DataTable returnDatatable(string SPName, string[] ParameterName, string[] Values)
    {
        DataTable ds = new DataTable();
        try
        {
            openDBConnection();
            SqlConnection mssqlConn = mssqlConnection;
            SqlCommand Cmd = new SqlCommand(SPName, mssqlConn);
            SqlDataAdapter da = new SqlDataAdapter(Cmd);
            Cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < ParameterName.Length; i++)
            {
                Cmd.Parameters.AddWithValue(ParameterName[i], Values[i]);
            }
            da.Fill(ds);
        }
        catch (Exception ex)
        {
           throw ex;
        }
        finally
        {
            closeDBConnection();
        }
        return ds;
    }

Posted 8-May-17 20:09pm

vinodh muthusamy

Updated 8-May-17 20:56pm

v4

Add a Solution

Comments

_Asif_ 9-May-17 2:19am

You need to share your SP code as well

vinodh muthusamy 9-May-17 2:22am

Here is my SP code

ALTER PROCEDURE [dbo].[datatype_Details]
(
@dataval VARCHAR
)
AS
BEGIN

select FId,DataTypeName from tbl_fieldname where DataTypeName in(@dataval)
END

_Asif_ 9-May-17 2:26am

Share SP test script.

vinodh muthusamy 9-May-17 2:35am

how to check test script

_Asif_ 9-May-17 2:40am

SPs need to be test separately, usually using SQL Server Management Studio. For example a test script could be like.

DECLARE @dataval VARCHAR(200)
SELECT @dataval = 'varchar';

EXEC datatype_Details @Dataval;

And check whether SP is working as per requirement. If not then fix the SP.

vinodh muthusamy 9-May-17 2:44am

DECLARE @dataval VARCHAR(200)
SELECT @dataval = ('First name','Address');

EXEC datatype_Details @Dataval;

It throws error as

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

_Asif_ 9-May-17 3:09am

hoping you have understood by yourself why its a syntax error. Anyway to fix it you need to look at following Tip

http://stackoverflow.com/questions/3735152/most-succinct-way-to-transform-a-csv-string-to-an-table-in-tsql

Your SP need to be altered to use above Tip, similar to something like this
select FId,DataTypeName from tbl_fieldname T
INNER JOIN dbo.Split(@dataval, ',') M ON T.DataTypeName = M.items

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-05-08T20:56:00

Solution 1

See here: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 8-May-17 20:56pm

OriginalGriff