CREATE PROCEDURE MySproc
     @ParamN int
AS

... DECLARE and Populate @TableVarB ...

    SELECT A_Col1, A_Col2, B.B_Col1, B.B_Col2
    FROM TableA AS A
         JOIN @TableVarB AS B
             ON B.ID = A.ID_B1
    WHERE A.ColN = @ParamN
    ORDER BY A_Col1, B.B_Col2

CREATE PROCEDURE MySproc
     @ParamN int
AS
DECLARE @strSQL nvarchar(MAX)

... DECLARE and Populate @TableVarB ...

    SELECT @strSQL='A_Col1, A_Col2, B.B_Col1, B.B_Col2
    FROM TableA AS A
         JOIN @TableVarB AS B
             ON B.ID = A.ID_B1
    WHERE 1=1'
    + CASE WHEN @ParamN IS NULL THEN '' ELSE ' AND A.ColN = @ParamN'
    + ... more conditional criteria ...
    + ' ORDER BY A_Col1, B.B_Col2'

    EXEC sp_ExecuteSQL @strSQL, N'@ParamN int', @ParamN, ...

quote:

---

Originally posted by X002548

You need to declare AND populate the table variable with in the dynamic sql you are creating

---

quote:

---

Originally posted by X002548

the same reason you can't use a local table variable in dynamic sql is the same reason you can't use a local temp table

It must be a global or permanent table if you are going the reach beyond the scope of the dynamic sql where the table variable or temp table are referenced in

---

quote:

---

Originally posted by Lamprey

Is this a follow on to your dynamic select list from yesterday?

---

WHERE     (@Param1 IS NULL OR MyCol1 = @Param1)
      AND (@Param2 IS NULL OR MyCol2 = @Param2)
      ...

quote:

---

Originally posted by Kristen

<snip>I think I can write the Sprocs to be maintainable, but time will tell. (My thought is to have the original SELECT list "in the code" but commented out, and in the style of an SProc call, so I can just EXECUTE that bit, manually, if it changes which will update the Metabase in the DEV system with the actual syntax of the various permitted columns in the SELECT list, so the "master version" will always be up-to-date, and then the Client/DEV can choose from that Master list during the Development / Customisation phase. Hope that makes sense? if not picture me waving my arms about furiously in order to explain it better

---

quote:

---

Less recompiling, less locking, less Transaction Log activity. Perhaps some significant in-memory-only activity on @TableVars which would be TEMPDB activity with #TempTable?

---

CREATE TABLE #temp(Col1 int); 
INSERT INTO #temp(Col1) SELECT 1;
DECLARE @sql varchar(8000); 
SET @sql = 'SELECT * FROM #temp';
EXEC(@sql);
DROP TABLE #temp;

quote:

---

Originally posted by GilaMonster

If you're going lots of dynamically generated dynamic SQL, watch the impact on the plan cache, it will grow larger than it would with just procedures.

---

SELECT @strWhereSQL='WHERE 1=1'
    + CASE WHEN @ParamN IS NULL THEN '' ELSE ' AND A.ColN = @ParamN'
    + ... more conditional criteria ...

quote:

---

Originally posted by X002548

Is this new behavior?

---

DECLARE @sql varchar(8000); 
SET @sql = 'CREATE TABLE #temp(Col1 int); 
INSERT INTO #temp(Col1) SELECT 1'
EXEC(@sql)
SELECT * FROM #temp

quote:

---

Originally posted by Kristen


Since SQL 2000 I have converted pretty much all #TempTables to @TableVars.

---

quote:

---

Originally posted by Kristen

quote:

---

Originally posted by GilaMonster

If you're going lots of dynamically generated dynamic SQL, watch the impact on the plan cache, it will grow larger than it would with just procedures.

---

You mean if I have lots of different variants of

SELECT @strWhereSQL='WHERE 1=1'
    + CASE WHEN @ParamN IS NULL THEN '' ELSE ' AND A.ColN = @ParamN'
    + ... more conditional criteria ...

each variant combination that gets used will create a different cache plan?

---

quote:

---

Originally posted by Kristen

Interesting, thanks. Well I won't be shy about converting them back then, and that will solve my SCOPE on @TableVars .

Mine usually only hold a handful of rows, so perhaps no big issue on performance. Never tested them carefully side-by-side for performance though.

---