How to write a function that relies on a database variable SQL

Question

I have the following code:

  SELECT c.name AS ColName, t.name AS TableName
  FROM sys.columns c
      JOIN sys.tables t ON c.object_id = t.object_id
  WHERE c.name LIKE '%MyName%'

I would like the code above to be put the TVF in a tools database, and call it from where-ever on the server and search the database of my choosing.

Is this even possible, would I have to use SP with the exec statement ? My concern is that I have other frequently used queries that I would also like to store in this but they are much longer...

Tab Alleman · Accepted Answer · 2016-10-25 20:20:55Z

1

The only way to use a variable for a database is to use dynamic sql, which is not possible in a TVF. You could only do it with a stored procedure.

answered Oct 25, 2016 at 20:20

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sean Lange Over a year ago

Or use OpenQuery. :)

Tab Alleman Over a year ago

MSDN says that OPENQUERY does not accept variables for its arguments, which jives with my experience with it. Am I missing something?

Sean Lange Over a year ago

No I meant you could use OpenQuery to execute your created string. Still can't execute it in a function.

Collectives™ on Stack Overflow

How to write a function that relies on a database variable SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related