sp_executesql dynamic columns in select statement

Question

sp_executesql dynamic columns in select statement

nawillia

SSCrazy

Points: 2401
More actions
November 18, 2010 at 8:13 pm

#226785

can i use sp_excecutesql to run a cmd that has a parameter for the column. so the cmd looks like this
@cmd = N'select @col1 from dbo.testtable'
also can you do something like
@cmd = N'select * from @sometable'
I know the specifics of passing the parameters to sp_executesql, but i can't get the actual columns or tables to be dynamic.
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

shump66@yahoo.com Right there with Babe Points: 791 More actions · Answer 1

@cmd = N'select @col1 from dbo.testtable'

I am not sure if this correct and would need to test but if you give it a try I would think something like this may work

@cmd = N'select ' + @col1 + 'from dbo.testtable'

The other way is which may work is

declare @SQLString AS VARCHAR(max)

set @SQLString = 'select ' + @col1 + 'from dbo.testtable'

@cmd = @SQLString

My home server is done when I get into my office I will give this a try

Scott
I am a Senior Data Analyst/DBA
I work with MS SQL, Oracle & several ETL Tools

@thewolf66[/url]

sivaj2k Ten Centuries Points: 1188 More actions · Answer 2

-- This can be used for your solution. But this is not a complete solution.

-- Chance for having sql injection.

CREATE PROCEDURE [dbo].[Dynamic_Query_Example] (@p_category varchar(20))

as

begin

declare @v_count int

declare @sql_query varchar(max), @p_category_select varchar(max)

select @v_count = count(*) from information_schema.columns where table_name = 'test' and column_name = @p_category

if @v_count >0

begin

SELECT @SQL_QUERY = ' SELECT '+@P_CATEGORY+' FROM test'

end

else

begin

print 'Invalid column name'

end

print @sql_query

exec (@sql_query)

end

Thanks

Siva Kumar J

nawillia SSCrazy Points: 2401 More actions · Answer 3

Thanks for the advice, but i'm not looking to do string concatenation...i need to have the parameters passed usin sp_executesql

shump66@yahoo.com Right there with Babe Points: 791 More actions · Answer 4

Did you try the Ex I posted I have not had time to test

Scott
I am a Senior Data Analyst/DBA
I work with MS SQL, Oracle & several ETL Tools

@thewolf66[/url]

nawillia SSCrazy Points: 2401 More actions · Answer 5

HI

I did not try your example per se, because it use string concatenation with the @table or @col outside of quotes.

This will lead to sql injection. What i was looking for was an

example where the string looks like ' select @col from @table' all inside the quotes and then @col and @table are passed as variables to the sp_executesql stored procedure.

I never got it to work, but my app isn't a web app, so sql injection is not a big deal. I just wanted to code it securely anyways.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

The sp_executesql procedure works like a dynamic SP so you can only pass values, and not object names, as parameters.

To guard against SQL injection you can validate column names against INFORMATION_SCHEMA.COLUMNS and table names against INFORMATION_SCHEMA.TABLES.

nawillia SSCrazy Points: 2401 More actions · Answer 7

Thanks

That is the answer I was looking to confirm my thoughts.

Thanks for the advice too.