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

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)

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

1 solution

Solution 1

Add your solution here

Preview 0

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

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us