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

Dhyanga

Hi I have a problem writing an query with select parameter as input paramter in the stored procedure.

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

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

COMMIT TRANSACTION

Is it possible something like this with select paramter also included in input paramter to the stored procedure? Thanks in advance,

Dhyanga

Luc Pattyn

Why don't you try along these lines:

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

AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where @FieldName = @FieldValue

COMMIT TRANSACTION

:doh: [EDIT: this doesn't solve it. The message by Michael Potter holds the answer.[/EDIT]

Luc Pattyn [My Articles] Nil Volentibus Arduum

Dhyanga

Thanks for the reply but it didn't work out. I am restating my problem with following table. I have one table named ItemList like this:

ID Name Price($)
1 Bag 20
2 Fabrics 35
3 Tools 100

now my query was

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

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

COMMIT TRANSACTION

I need output something like this. If I have @ID = 1 and @ColName = 'Name', then my output should be

Name

Bag

I tried my way that i posted earlier and your way, but it didn't work out. It gave output like this instead.

Column1

Name
Name

Is there any other way or am I doing any wrong in my query? Please help.

suchita

Lost User

You'd need to introduce the output-keyword to one of the parameters, as described in the documentation[^]. Your sproc is a wrapper around a simple statement, adding complexity without any benefits. The design, the approach, it's wrong. There's nothing to "commit" to the database, and a simple select-statement (with parameters) would be sufficient.

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

Dhyanga

Thanks Eddy, I will take that commit out from my query. I mistakenly forgot to take that out. But my question is still the same. I don't know whelther we can send fieldname as input parameter to get the value of that field.I know it looks very weird for that small table but I have to use the same concept for my huge database system. I thought that table would be easy for me to explain what my output should look like. I am going through the documentation you sent me but at glance, i couldn't see what I am looking for but I am going through it line by line. Thanks for your time .

suchita

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.