Problem : Passing a Variable is not working in Dynamic Query

Question

Problem : Passing a Variable is not working in Dynamic Query

oneteabag

SSC Eights!

Points: 954
More actions
September 4, 2022 at 8:23 pm

#4083815
Hi all,
I am not able to pass the table name as a constant in Dynamic Query:
The logic is to loop through a list of tables name stored in a table 'tbl_names' and produce an output of
tbl_name, row count
My code is a follows:
```
/*

Table tbl_names has 3 column (id, tbl_name varchar (500) holding all table names in the Database, is_processed (0 or 1))

*/
Declare @vtbl varchar(300)

while (select count(*) from tbl_names where is_processed = 0 )>0

Begin

Select Top 1 @vtbl=tbl_name from tbl_names where processed=0

Set @sql='Select'+@vtbl+'count(*) as cnt From'+@vtbl+

Exec sp_executesql @sql

update tbl_names set is_processed=1 where tbl_name=@vtbl

End
```
I am getting an error 'Column name not valid' because it is trying to select a column with the table name.
Appreciate your help
- This topic was modified 2 years, 2 months ago by Steve Jones - SSC Editor. Reason: code format

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

No need for the Dynamic SQL, the WHILE loop, or any of the other complexities that go along with those things.

Here's a sample "tbl_names" table that I made up for my testing. It's NOT a part of the solution but, do notice, I used Wild Cards as well as discrete names.

--===== Setup a search table similar to what the OP cited.
     -- Notice that we can use WILD CARDS here.
     -- (Leading Wild Cards are not normally recommended but
     --  sometimes tough to avoid).
 SELECT tbl_name = CONVERT(SYSNAME,v.tbl_name)
   INTO dbo.tbl_names
   FROM (VALUES
         ('Employee%')
        ,('Address')
        ,('Location')
        ,('%Order%')
        ,('Product')
        ,('%History')
        )v(tbl_name)
;
GO

Then, I ran the code against an old AdventureWorks database I had lying around. Here's the code...

 SELECT  ObjectID   = pstat.object_id
        ,SchemaName = OBJECT_SCHEMA_NAME(pstat.object_id)
        ,ObjectName = OBJECT_NAME       (pstat.object_id)
        ,RowCnt     = SUM(pstat.row_count)
   FROM dbo.tbl_names             tnam
   LEFT JOIN sys.objects          obj ON obj.name LIKE tnam.tbl_name
   JOIN sys.dm_db_partition_stats pstat
     ON obj.object_id = pstat.object_id
  WHERE obj.type            = 'U'   --User Tables
    AND pstat.index_id     IN (0,1) --Heap or Clustered
    AND obj.is_ms_shipped   = 0x0   --Not MS Shipped
  GROUP BY pstat.object_id
  ORDER BY SchemaName, ObjectName
;

... and here's the output ...

There are a shedload of other possibilities. For example, you could return whether the object were a Heap or Clustered Table, the number of non-clustered indexes, the create_date and modify_date for each table, the amount of reserved, used, and free space for each table, how much of it is data, index, or lob, etc, etc, etc.

The first thing to do, though, is read about sys.objects, sys.dm_db_partition_stats, some of the functions I used, Wild Cards in association with the word LIKE, types of joins and, of course, GROUP BY.

You also don't actually need a tbl_names table if you don't want to. You could add a WHERE clause with what you want to look up. You could also turn this into a high-performance iTVF (Inline Table Value Function - Lookup "CREATE FUNCTION" for more on that )

The code I produced does what you wanted... now, let your imagination run wild with "possibilities". 😀

p.s. You might want to get out of the habit of using prefixes like "tbl_" for object names. It's a real booger when you need to convert a table to a view (which I've had to do many times for various reasons) and you end up with a view that starts with "tbl_" because you didn't want to break anyone's code.

Although I didn't use a Schema_Name for the search control table so that I could demo what you asked for, it would be a great idea to always use 2 part naming for all of your objects.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

oneteabag SSC Eights! Points: 954 More actions · Answer 2

Thank you Jeff. Your code worked well.

I was hoping to code it in Dynamic Query and loop through the table, still bother me it did not work 🙁

aaron.reese SSChampion Points: 13498 More actions · Answer 3

If you wanted more information on why your original code doesn't work, put

PRINT @SQL

between your SET and EXEC. You will see that the query is badly formatted and not valid SQL syntax. This is a handy tip for seeing what is going to be run during your loop process, especially it if is going to peroform an undoable DELETE, TRUNCATE, UPDATE etc.

Another tip, avoid writing WHILE, CURSOR or any other looping strategy if at all possible. MSSQL is a set-based engine and works really well with calculating the best stategy to fetch data (most of the time...) Looped code will be slower than set based code by a factor of several hundred if it is complex and is one of the biggest performance killers in a RDBMS

This reply was modified 2 years, 2 months ago by aaron.reese.

oneteabag SSC Eights! Points: 954 More actions · Answer 4

Thank Aaron for the Tip. Dynamic SQL is a pain anyway but in some situation you are forced to use it.