How to pass selected columns and tables as parameters in stored procedure dynamically.

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hello,
My question is like ,i have two DropDownList.

1. From first DropDownList user will select table.
2. From second DropDownList user will select multiple columns based on the selected table from DropDownList 1st.

So i want to pass selected table (from 1st ddl) and multiple columns (from 2nd ddl) as a parameters in sql Stored Procedure.

Note: i want to pass columns as array.

Please Help.

Posted 7-Jun-15 19:08pm

Merajuddin Ansari

Add a Solution

Comments

Vipin Patil 8-Jun-15 1:50am

You want to pass array of columns to sql stored procedure or other class

Merajuddin Ansari 8-Jun-15 1:55am

Yes i want to pass array of columns to the sql stored procedure.

Vipin Patil 8-Jun-15 2:07am

if you have varchar parameter then
You can concate the values and pass to the stored procedure

Merajuddin Ansari 8-Jun-15 2:10am

Can you please give an example for that.?

Vipin Patil 8-Jun-15 2:22am

can we select multiple values from dropdownlist

Merajuddin Ansari 8-Jun-15 2:32am

yes

Vipin Patil 8-Jun-15 2:37am

string strCols = "";
foreach (string strVal in DropDownList1.SelectedIValues)
{
strCols = strCols + strVal;
}

pass strCols to stored procedure
cmd.Parameters.AddWithValue("@Cols", strCols);

Merajuddin Ansari 8-Jun-15 2:41am

Thank you.

ALTER PROCEDURE [dbo].[proc_test]
@cols nvarchar (20),
@tablename nvarchar(250)
AS

Exec('SELECT * FROM ' + @tablename)

-- how can i pass cols to above select statement.

Vipin Patil 8-Jun-15 2:54am

ALTER PROCEDURE [dbo].[proc_test]
(
@cols nvarchar (MAX),
@tablename nvarchar(250)
)
AS
DECLARE @Query varchar(MAX)
SET @Query = 'SELECT ' + @cols + ' from ' + @tablename

EXEC(@Query)

Merajuddin Ansari 8-Jun-15 3:10am

thank you very much..

but strCols should be separated from comma.

like col1,col2,col3,....coln

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)

Vipin Patil · Accepted Answer · 2015-06-07T21:12:00

C#

string strCols = "";
foreach (string strVal in DropDownList1.SelectedIValues)
{
      strCols = strCols + strVal + ",";
}

pass strCols to stored procedure

C#

cmd.Parameters.AddWithValue("@Cols", strCols);

SQL

ALTER PROCEDURE [dbo].[proc_test]
(
   @cols nvarchar (MAX),
   @tablename nvarchar(250)
)
AS
DECLARE @Query varchar(MAX)
SET @Query = 'SELECT ' + @cols + ' from ' + @tablename

EXEC(@Query)