how to get select parameter too in input parameter in stored procedure

[Lost User]

Dhyanga wrote:

I don't know whelther we can send fieldname as input parameter to get the value of that field.

You could join on the system-tables, but to what use? You'll be concatenating constants in a way that can hardly be considered helpfull. If writing Sql is that much of a problem, consider a ORM-framework.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

[jschell]

Dhyanga wrote:

I don't know whelther we can send fieldname as input parameter

You can't. Your choices are 1. Create the SQL dynamically in C# and then execute it. 2. Create the SQL dynamically in SQL and then execute it in SQL. 3. Use a fixed set of fixed SQL statements and select one based on the data passed in.

[Dhyanga]

I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.

string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.

This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.

suchita

[Dhyanga]

Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...

suchita

[Lost User]

I can only point to jschells' comment :)

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

[Dhyanga]

Thanks Eddy for your time and views. :)

suchita

[Karthik Harve]

Hi, try like this..

CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int

AS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @ID

COMMIT TRANSACTION

Karthik Harve

[Mycroft Holmes]

Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!

Never underestimate the power of human stupidity RAH

[Michael Potter]

You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.

CREATE PROCEDURE SelectItemList
(
@ColName string,
@ID int
)
AS

 DECLARE @sql NVARCHAR(4000)

 SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))

 EXEC sp\_executesql @sql

[Dhyanga]

Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.

suchita

[Michael Potter]

Glad I could help.

[jschell]

Dhyanga wrote:

i can just execute it without using stored procedure

My solutions work with or without stored procs.

[jschell]

Keep in mind that as formatted that solution is open to SQL injection attacks.