Issues passing Table Name as a parameter to a Stored Procedure

Hi Friends,

I use the stored procedure pr_Set_Min_Max_Ind to set some indicators in certain type of tables in my databases.

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind] ( @.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName )
................
................
...
...
...
................
End

The procedure pr_Set_Min_Max_Ind takes five parameters ( @.t, @.k1, @.k2, @.r1, @.r2 ) of which
@.t is the table name in which I want to set some indicators and
the rest of the parameters (@.k1, @.k2, @.r1, @.r2) are column names in the table @.t

Say, I have this procedure stored in database A. Then to execute the procedure on table TestTable_A in database A , I write the statement as:

exec pr_Set_Min_Max_Ind TestTable_A, Column1, Column2, Column3, Column4 (statement-1)

Now, if I want to execute this procedure on a table TestTable_B in a different database B, I am NOT able to issue a statement like:

exec pr_Set_Min_Max_Ind B.dbo.TestTable_B, Column1, Column2, Column3, Column4 (statement-2)

The only way I now know to accomplish this is to save a copy of this procedure in database B and write a statement similar to statement-1. But, for some reasons I DO NOT want to do that. I just want to have the procedure stored in only one database and be able to pass the table names from other databases in the format database_name.dbo.table_name ;So, guys please let me know how I can pass the table name in the format shown as in statement-2 above.

Thanks in advance for taking time to give me a solution.

With Best Regards,

-Ram.

Depends on how you create your SQL statements based on the table names and column names that you pass to your stored procedure but you should be able to do a SELECT statement from another database in the same SQL Server. Try posting your SP so that we will be able to see where the problem could be.

SQL Server Helper
http://www.sql-server-helper.com

|||

Hi,

The following is the SP that I was referring to.

Thank you.

--

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind](@.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName)

As

Declare @.SQL Varchar(8000),

@.x SysName

Set @.x = '##_' + @.t + '_' + Replace(Convert(Char,GetDate(),114),':','')

Begin

-- Tables which have a Min_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Min_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 + ' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ',' + @.r2 + ') = 1 Then ''Y'' Else ''N'' End New_Min_Ind, IsNull(Min_Ind,''-'') Old_Min_Ind From ' + @.t +') t Where Old_Min_Ind <> New_Min_Ind And New_Min_Ind = ''Y'' '

Print @.Sql

Exec(@.SQL)

-- Update any record based on key fields and set the Min_Ind to N if it is not N

Set @.SQL = 'Update a Set Min_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Min_Ind,''-'') <> ''N'' '

Print @.Sql

Exec(@.SQL)

-- Update table and set the Min_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Min_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 +' And b.k2 = a.' + @.k2 + ' And b.r1 = a.' + @.r1 + ' And b.r2 = a.' + @.r2

Print @.Sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

-- Tables which have a Max_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Max_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 +' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ' Desc,' + @.r2 + ' Desc) = 1 Then ''Y'' Else ''N'' End New_Max_Ind, IsNull(Max_Ind,''-'') Old_Max_Ind From ' + @.t +') t Where Old_Max_Ind <> New_Max_Ind And New_Max_Ind = ''Y'' '

Print @.sql

Exec(@.SQL)

-- Update any record based on key fields and set the Max_Ind to N if it is not N

Set @.SQL = 'Update a Set Max_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Max_Ind,''-'') <> ''N'''

Print @.sql

Exec(@.SQL)

-- Update table and set the Max_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Max_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = ' + @.k1 +' And b.k2 = ' + @.k2 + ' And b.r1 = ' + @.r1 + ' And b.r2 = ' + @.r2

Print @.sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

Return @.@.Error

End

|||

You will need to prefix your If ... from Information_Schema.Columns with the name of the database passed from your @.t variable...

Something like this

declare @.DBName varchar(50)
declare @.TblName varhcar(50)

Set @.DBName = parsename(@.t, 3)
Set @.TblName = parsename(@.t, 1)

-- 1=object name, 2=schema name, 3=database name, 4= server name

Then you'll need to change your If count(*)... from Information_Schema.Columns statement into dynamic SQL (so that the database name gets interpretted)...

I would use sp_executesql to return your count (from the If statement against information_schema). See BOL on how to return a value using sp_executesql.

Note, just a partial example...

SET @.SQLString = N'Select @.retCountOut = Count(*) From ' + @.dbname + '.Information_Schema.Columns Where Table_Name = ''' + @.tblname + ''' Column_Name = 'Min_Ind''

SET @.ParamDef = N'@.retCountOut int Output, @.dbname varchar(50), @.tblname varchar(50)';

EXECUTE sp_executesql @.SQLString, @.ParamDef, @.dbname = @.dbname, @.tblname = @.tblname, @.retCountout=@.retCount OUTPUT;

If @.retCount<> 0
Begin.....

|||I would strongly suggest against this sort of thing. This code is really hard to follow and will be problematic. I would consider using this code to generate non-dynamic SQL that you run directly. Then the code generator might be dynamic, but each module won't be, and will perform better, and be easier to manage.