InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 12:49:41 GMT20http://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspxThanksThat is the answer I was looking to confirm my thoughts.Thanks for the advice too.Tue, 23 Nov 2010 09:03:30 GMTnawilliahttp://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspxThe 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.Tue, 23 Nov 2010 08:43:11 GMTKen McKelveyhttp://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspxHII 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 anexample 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.Tue, 23 Nov 2010 08:00:52 GMTnawilliahttp://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspxDid you try the Ex I posted I have not had time to testTue, 23 Nov 2010 07:56:13 GMTSHumphrey_66http://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspxThanks for the advice, but i'm not looking to do string concatenation...i need to have the parameters passed usin sp_executesqlTue, 23 Nov 2010 07:38:00 GMTnawilliahttp://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspx-- 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))asbegindeclare @v_count intdeclare @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_categoryif @v_count >0 beginSELECT @SQL_QUERY = ' SELECT '+@P_CATEGORY+' FROM test'endelsebeginprint 'Invalid column name'endprint @sql_queryexec (@sql_query)endThanksSiva Kumar JTue, 23 Nov 2010 07:18:47 GMTsivaj2khttp://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspx[quote]@cmd = N'select @col1 from dbo.testtable'[/quote]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 isdeclare @SQLString AS VARCHAR(max)set @SQLString = 'select ' + @col1 + 'from dbo.testtable'@cmd = @SQLStringMy home server is done when I get into my office I will give this a try Fri, 19 Nov 2010 05:34:35 GMTSHumphrey_66http://www.sqlservercentral.com/Forums/Topic1023264-392-1.aspxcan 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.thanksThu, 18 Nov 2010 20:13:31 GMTnawillia