Paramater Concatenation

• ksrikanth77

  SSCrazy

  Points: 2232

  More actions

  October 3, 2013 at 2:43 pm

  #299459

  Hi Everyone,

  DECLARE @CN VARCHAR (100)

  SET @CN = 'abc pvt Ltd'

  select * from [@CN'+'$Production]

  My output should be select * from [abc pvt Ltd$Production].I DO NOT want to use a string for my Select Query

  Could anyone of you help on me this.

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  October 3, 2013 at 2:54 pm

  #1655691

  As far as I know of, without building your query as a string and passing it to EXEC or (preferably) sp_executesql, this isn't possible, to the best of my knowledge.

  If I remember correctly, it has something to do with requiring database names in queries to be explicit declarations; you can accomplish this with a string that gets run through EXEC, since the variable is replaced by its value, but not in a non-EXEC query.

  - 😀

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  October 3, 2013 at 4:23 pm

  #1655706

  You can include table names or derived tables in your from clause but not a non-static value. Dynamic SQL (using sp_executesql not EXEC) is the way to go for this.There is no problem with Dynamic SQL when it done correctly (e.g. in a way where SQL Injection can't happen).

  That said, here is an alternative solution if you MUST avoid Dynamic SQL.

  --sample data

  IF OBJECT_ID('tempdb..#tbl1') IS NOT NULL DROP TABLE #tbl1;

  IF OBJECT_ID('tempdb..#tbl2') IS NOT NULL DROP TABLE #tbl2;

  IF OBJECT_ID('tempdb..#tbl3') IS NOT NULL DROP TABLE #tbl3;

  IF OBJECT_ID('tempdb..#tbls') IS NOT NULL DROP TABLE #tbls;

  SELECT 't1' AS tbl INTO #tbl1;SELECT 't2' AS tbl INTO #tbl2;SELECT 't3' AS tbl INTO #tbl3;

  -- catch-all query

  WITH tbls(tbl) AS (SELECT '#tbl1' UNION SELECT '#tbl2' UNION SELECT '#tbl3')

  SELECT * INTO #tbls FROM tbls

  GO

  DECLARE @CN VARCHAR (100)='#tbl1';

  IF EXISTS (SELECT tbl FROM #tbls WHERE tbl=@CN)

  BEGIN

  IF @CN='#tbl1'

  SELECT * FROM #tbl1

  --SELECT TOP 10 * FROM #tbl1

  IF @CN='#tbl2'

  SELECT * FROM #tbl2

  --SELECT TOP 10 * FROM #tbl2

  IF @CN='#tbl3'

  --SELECT TOP 10 * FROM #tbl3

  SELECT * FROM #tbl3

  END

  ELSE

  SELECT 'table does not exist or has not been entered into #tbls. Fire someone.' AS Msg

  Here I am using a catch-all query; see this article[/url] by Gail Shaw for more information about catch-all queries...

  One final important note

  Do you really need all the rows from any table defined by @CN? Even if it has a couple billion rows?

  I would suggest that you have the query return a limited number of values (note my commented out code above)...

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001