How to pass table name to a store procedure in Ms SQL

We have a store procedure that list the columns of a table.  However, in some table names it worked while other don’t; return blank.

In the store procedure we have a declared variable @Var1 char(100) and placed it where the table name should be if used in constant value.

Tried placing in the coded were we need the table name:

@Var1
QUOTENAME( @Var1 )
char(39) + @Var1 + char(39)

Please advice on the proper way to pass a table name and it can be used in a stored procedure script.

Thank you.
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
You can query any table you want two ways:

1. Ad hoc queries, just use your connection and send it whatever SQL you want to execute.
2. Dynamic SQL, aka EXECUTE sp_executesql

For stored procedures having a table name as a parameter the second option is your only real chance and so you just have to watch out about the SQL string you create within that stored procedure to finally execute it.

Whether you want or need to put quotes around the variable/parameter name or use QUOTENAME or whatever depends on what you put together. In the end, the final whole query must work out.

Things people topple with creating dynamic SQL is that you put together a string, which has one extra level of how to write string literals within string literals by doubling the single quotes around them. It can get messy especially in length queries you build up in complicated ways.

It's not a first choice option anyway, as stored procedures run better with concrete queries than with varying queries.

It's not seldom a software has a huge set of queries it needs to run and defines them as either views or stored procs or both, but does so on the level of DDL (data definition language), not creating dynamic stored procs, instead creating all possible queries as much as necessary to cover cases you otherwise only can cover with dynamic sql. And also watch out that tool might be used for cases also solvable with a single constant query only paramterized in values/filter clauses, not in tables.

The best advice I can give you is PRINT @sqlquery and inspect what you want to execute looks okay or works out if you copy it to a new query window. On top of that look into error messages, use SQL profiler to record queries executed and learn to use the debugger.

Bye, Olaf.
rayluvsAuthor Commented:
I have stored procedure with the following lines:

   select tables.Table_Name,  columns.column_name,
          CASE WHEN RIGHT(DATA_TYPE,4)='CHAR' THEN 
             DATA_TYPE  +'('+ CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4))+')'
            ELSE CASE WHEN NUMERIC_PRECISION IS NOT NULL THEN
                   DATA_TYPE + '('+CAST(NUMERIC_PRECISION AS 
                   VARCHAR(4))+','+isnull(CAST(NUMERIC_SCALE AS VARCHAR(4)),'NULL')+')'
                  ELSE DATA_TYPE
                 END
          END AS Field_Attributes
     from INFORMATION_SCHEMA.Tables tables
     inner join INFORMATION_SCHEMA.Columns Columns on tables.table_catalog = columns.table_catalog
           and tables.table_schema = columns.table_schema
           and tables.table_name = columns.table_name
     where tables.table_type = 'BASE TABLE'
     and tables.table_name <> 'sysdiagrams'
     AND tables.table_name = @Var1
  ORDER BY 2

Open in new window


I run the stored procedure passing the table-name as follow:

exec sp_vc aSOURCE

I have changed  the line "AND tables.table_name = @Var1" to,

QUOTENAME( @Var1 )
char(39) + @Var1 + char(39)

Doesn't work.

Is my script missing something?

How can I pass correctly the table-name to this stored-procedure?

Thank you.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you also post the entire CREATE PROCEDURE script? I want to see how you are passing the parameter in the stored procedure definition.

The SELECT by itself does not have anything obvious that is standing out. Hence, I think it is either a problem with the handling of the parameter or the table does not exist in the database where the procedure is running.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

rayluvsAuthor Commented:
Here it is:
CREATE Proc sp_vc
 @Var1 VARCHAR(100)
 AS
 BEGIN
 SET NOCOUNT ON
   select tables.Table_Name,  columns.column_name,
          CASE WHEN RIGHT(DATA_TYPE,4)='CHAR' THEN 
             DATA_TYPE  +'('+ CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4))+')'
            ELSE CASE WHEN NUMERIC_PRECISION IS NOT NULL THEN
                   DATA_TYPE + '('+CAST(NUMERIC_PRECISION AS 
                   VARCHAR(4))+','+isnull(CAST(NUMERIC_SCALE AS VARCHAR(4)),'NULL')+')'
                  ELSE DATA_TYPE
                 END
          END AS Field_Attributes
     from INFORMATION_SCHEMA.Tables tables
     inner join INFORMATION_SCHEMA.Columns Columns on tables.table_catalog = columns.table_catalog
           and tables.table_schema = columns.table_schema
           and tables.table_name = columns.table_name
     where tables.table_type = 'BASE TABLE'
     and tables.table_name <> 'sysdiagrams'
     AND tables.table_name = @Var1
  ORDER BY 2
 SET NOCOUNT OFF
 END

Open in new window


To run it,

exec sp_vc2  spt_monitor <<-- this is a table in Master database

The above actually works, but if I go to another database, say "WORKAREAS", the results is empty.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Thank-you. That does make sense. The views (INFORMATION_SCHEMA) will return data only pertaining to the database to which one is currently connected to. If a table does not exist in that database, it will not return any results (in the example, it will not return anything if you try to query for "spt_monitor" when connected to a user database, say MyDB).

At this point, the thing that comes to mind is to convert this query to a dynamic query where you can append the database name (I.e. MyDB.INFORMATION_SCHEMA.TABLES) and so on.
rayluvsAuthor Commented:
Yes, I also thought of that, issue of INFORMATION_SCHEMA returning values of the connected database - so I also used this script :

CREATE Proc sp_vc
 @Var1 VARCHAR(100)
 AS
 BEGIN
 SET NOCOUNT ON
SELECT c.name 'Column Name', t.Name 'Data type', c.max_length 'Max Length', c.precision, c.scale, c.is_nullable, ISNULL(i.is_primary_key, 0) 'Primary Key'
 FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
 LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
 LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
 WHERE c.object_id = OBJECT_ID(QUOTENAME(@Var1))
 order by 1
SET NOCOUNT OFF
END

Open in new window


Is there a way to modify the above script to display the columns of the called Tables even though the store procedure is not in the database?

NOTE:
Prior placing the question, I solved this issue by just by creating the SP in every database.  But we have come upon that the SQL instance we use, we attached different DB, create new DB, etc. - hence, the reason for placing the question (we don't want to always have in mind to create these scripts)
Vikas GargAssociate Principal EngineerCommented:
Hello,

You can you the below query to find the column from the table name from each of the database from the current server.

EXEC sp_MSforeachdb 'USE ? SELECT ''?'' ''Database'',T.name,C.* FROM SYS.all_columns C
INNER JOIN SYS.tables T ON C.object_id = T.object_id
WHERE T.name = ''TableName'''

Open in new window

rayluvsAuthor Commented:
I want to run it on a specific table.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Is there a way to modify the above script to display the columns of the called Tables even though the store procedure is not in the database?

No. Catalog views and tables only return data specific to the active database context. Hence, the only way would  be to use a dynamic query against each database in question (similar to the one shown in ID: 42420593) or have copies of the stored procedure in multiple databases.

In order to ensure that you have the procedure in all your user databases (even if you moved them around via detach/attach), a quick and simple way would be to have a SQL Server Agent job running that keeps checking for required objects in user databases and creates them if not found.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Create the stored proc in the master db and mark it as a "system" procedure.  Then it will always run in the context of the current db.

USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_vc
...
GO
EXEC sp_ms_marksystemobject 'sp_vc'
GO

Then:

USE userdb1
EXEC sp_vc 'table1' --will list table1 for userdb1
USE userdb2
EXEC sp_vc 'table1' --will list table1 for userdb2
EXEC userdb3.dbo.sp_vc 'table1' --will list table1 for userdb3
rayluvsAuthor Commented:
Nakul,

I tried ID: 42420593 and it does work, but when I create a store procedure with it, I can't pass the tablename (the script below  I use @var1 to pass the tablename).  

CREATE Proc xx 
 @Var1 VARCHAR(100)
 AS
 BEGIN
 SET NOCOUNT ON

   EXEC sp_MSforeachdb 'USE ? SELECT ''?'' ''Database'',T.name,C.* FROM SYS.all_columns C
   INNER JOIN SYS.tables T ON C.object_id = T.object_id
   WHERE T.name = ''@Var1'''

 SET NOCOUNT OFF
 END

Open in new window


Scott,

I place my script to display tables in "..." of your script but when running in a another database, returns error
sp err
This the sp I created with your help:
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.xx
as
SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'

GO
EXEC sp_ms_marksystemobject 'xx'
GO

Open in new window

Scott PletcherSenior DBACommented:
To use globally from master, the procedure name must start with "sp_", which your original proc did.
rayluvsAuthor Commented:
Understood.

Do you know why this is by any chance?
Scott PletcherSenior DBACommented:
That's the way MS designed it. Any proc name that starts with sp_ (underscore is required) looks in master db first for that proc. (Btw, "sp" stands for "special", (not stored proc, as some people claim) , because of the way it automatically runs in the context of the current db.)

It makes sense really.  It would be too much overhead to look up every proc name first in master, so they chose a prefix to indicate "special" procs vs regular procs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.