how to get select parameter too in input parameter in stored procedure
-
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.
-
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
-
-
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 intAS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @IDCOMMIT TRANSACTION
Is it possible something like this with select paramter also included in input paramter to the stored procedure? Thanks in advance,
Dhyanga
Hi, try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID intAS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @IDCOMMIT TRANSACTION
Karthik Harve
-
Hi, try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID intAS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @IDCOMMIT TRANSACTION
Karthik Harve
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
-
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 intAS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @IDCOMMIT TRANSACTION
Is it possible something like this with select paramter also included in input paramter to the stored procedure? Thanks in advance,
Dhyanga
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
)
ASDECLARE @sql NVARCHAR(4000) SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10)) EXEC sp\_executesql @sql -
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
)
ASDECLARE @sql NVARCHAR(4000) SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10)) EXEC sp\_executesql @sql -
Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.
suchita
Glad I could help.
-
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
-
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
)
ASDECLARE @sql NVARCHAR(4000) SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10)) EXEC sp\_executesql @sql