InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 05:44:58 GMT20http://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspxI strongly recommend against this approach. It's a security risk and a maintenance nightmare. You wouldn't develop a C# method that could handle customers or products depending on a parameter passed, don't do it to SQL procedures.Tue, 15 Jan 2013 12:41:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspx[quote][b]DiverKas (1/15/2013)[/b][hr]You would be better served with the last line like this:EXEC sp_executesql @SQLYou should have a higher plan reuse rate instead of the standard EXECUTE (@SQL)[/quote]AND it allows for parameterization which is vital if using dynamic sql.Tue, 15 Jan 2013 12:36:42 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspxOr even better create 3 stored procs to retrieve your data. You are using the "one query fits all via dynamic sql" approach. This is full of issues. It is difficult to maintain, once business rules start diverging you have really complicated code (this sounds the case here), you are running the risk of sql injection (maybe not now but in the future).The code that Kingston shared will work but breaking this into separate pieces is really the best move in the long run.Tue, 15 Jan 2013 12:34:31 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspxYou would be better served with the last line like this:EXEC sp_executesql @SQLYou should have a higher plan reuse rate instead of the standard EXECUTE (@SQL)Tue, 15 Jan 2013 11:45:14 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspxHi Kingston Dhasian,i implemented as per your inputs it is working as expected.thank you!!!Tue, 15 Jan 2013 03:36:25 GMTsubramani.rudrappa 78855http://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspxYou can implement this with Dynamic SQL as well[code="sql"]DECLARE @TableName VARCHAR(100), @SQL VARCHAR(1000)SET @TableName = 'agent'SET @SQL = ' SELECT * ' + ' FROM ' + @TableName + ' WHERE ' + CASE WHEN @TableName IN ( 'product', 'customer' ) THEN 'statecode = 0' ELSE '1=1' ENDEXECUTE ( @SQL )[/code]Tue, 15 Jan 2013 03:25:16 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1407119-391-1.aspxHi,I have following tablesproductcustomeragentwe have column by name "state code" in product and customer.i have sql likeselect * from table name(we get table name dynamically like product,customer and agent using variable)i need to add condition to get records with statecode=0when i use where condition in the above query it looks likeselect * from table name(we get table name dynamically like product,customer and agent)where statecode=0but this dynamic query fails for table "agent" as agent table does not contain statecode column.so how can i wirte query where i get "where state code=0" condtion only for product and customer but not to agent.meanfor product and customer query should look like this select * from table name(we get table name dynamically like product,customer and agent)where statecode=0for agent :select * from table name(we get table name dynamically like product,customer and agent)i have tried if (variable='product' or variable='customer')select * from table name(we get table name dynamically like product,customer and agent)where statecode=0elseselect * from table name(we get table name dynamically like product,customer and agent)am looking at, is there any other way to implement this?Tue, 15 Jan 2013 03:03:27 GMTsubramani.rudrappa 78855