sp_executesql dynamic columns in select statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » sp_executesql dynamic columns in select...

sp_executesql dynamic columns in select statement

Rate Topic

Display Mode

Topic Options

Author

Message

nawillia

Posted Thursday, November 18, 2010 8:13 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 89, Visits: 387

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

SHumphrey_66

Posted Friday, November 19, 2010 5:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:27 AM
Points: 31, Visits: 184

@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

sivaj2k

Posted Tuesday, November 23, 2010 7:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 2:46 AM
Points: 45, Visits: 420

-- 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

nawillia

Posted Tuesday, November 23, 2010 7:38 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 89, Visits: 387

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

SHumphrey_66

Posted Tuesday, November 23, 2010 7:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:27 AM
Points: 31, Visits: 184

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

nawillia

Posted Tuesday, November 23, 2010 8:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 89, Visits: 387

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

Ken McKelvey

Posted Tuesday, November 23, 2010 8:43 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 822, Visits: 5,100

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

nawillia

Posted Tuesday, November 23, 2010 9:03 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 89, Visits: 387

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

Thanks for the advice too.

Post #1025243

« Prev Topic | Next Topic »

Permissions