Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_executesql dynamic columns in select statement Expand / Collapse
Author
Message
Posted Thursday, November 18, 2010 8:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 101, Visits: 477
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
Post #1023264
Posted Friday, November 19, 2010 5:34 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:06 AM
Points: 31, Visits: 211

@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
Post #1023433
Posted Tuesday, November 23, 2010 7:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 60, Visits: 520
-- 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
Post #1025153
Posted Tuesday, November 23, 2010 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 101, Visits: 477
Thanks for the advice, but i'm not looking to do string concatenation...i need to have the parameters passed usin sp_executesql
Post #1025168
Posted Tuesday, November 23, 2010 7:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:06 AM
Points: 31, Visits: 211
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
Post #1025184
Posted Tuesday, November 23, 2010 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 101, Visits: 477
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.
Post #1025186
Posted Tuesday, November 23, 2010 8:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 848, Visits: 5,504
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.
Post #1025228
Posted Tuesday, November 23, 2010 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 101, Visits: 477
Thanks
That is the answer I was looking to confirm my thoughts.

Thanks for the advice too.
Post #1025243
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse