Passing Column Name as parameter to sql store procedure

i am using asp.net 2005 with sql server 2005. in my database table contains

Table Name : Page_Content

Page_Id

1011021AbcPqr2Lmnoiu

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,@.aFrompage_content

end

Here in this above store procedure i want to pass 101 to @.lang_code

here is my output, but this is wrong output

Page_Id

Column111012101

but i want following output

Page_Id

1011021AbcPqr2Lmnoiu

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

|||

aadreja:

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

The above code is subject to sql injection attacks. Query on sql injection attacks if you don't know what they are.

As coded, someone could force your page to reveal sensitive data in other tables, or alter or destroy data in your database in ways you do not want to allow.

Given that a column name has very specific naming rules, you can test that the value you get in @.a is a plausible, safe column name.

If @.a has any character other than a letter from a-z, A-Z or 0-9, you should trap that and raise an error.

One way to test is to make a copy of @.a and remove all the valid characters. If nothing is left in the copy, it's a safe column name to process.

|||

I know you could do this with dynamic sql, but that's not always the best solution.

I think you could use a CASE/WHEN block to do what you are wanting. Each field would have to be known in advance, this wouldn't work "on the fly" if you add new columns to the table without updating the SP

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,
CASE
WHEN @.a = '101' THEN 101
WHEN @.a = '102' THEN 102
ELSE 101 -- you don't need an else, but this query will fail in a syntax error if the input doesn't match one of your defined values.
END Frompage_content

end

|||

I agree!

Benefits of your approach:

Sql Injection safe