pass parameter into query for table name

[prettitoni]

Is there a way to pass a parameter into the query of a SP for the table name?

If you send in "Table1", the query is:

SELECT * FROM Table1

and if you pass in Table2, the same query becomes:

SELECT * FROM TABLE2

 

[hmckillop]

]you can do it as shown, but why do you wish to do this?

create proc usp_ChangingTables
(
@pi_Tablename varchar(100)
)
AS
BEGIN
declare @select1 varchar(200)

set @select1='select * from ' + @pi_TableName

exec (@select1)
END

"I'm living so far beyond my income that we may almost be said to be living apart

 

[prettitoni]

Because I have 3 tables all with the same structure, each updated from a different form in VB. I'd rather call one SP based on each table instead of having 3 SPs with the same code.

 

[prettitoni]

Can you use that in an IF EXISTS clause? I actually really need it for an UPDATE and an INSERT statement.

 

[hmckillop]

Based on the info you have given I would create 3 different stored procedures, each referencing a specific table. It may sound like overkill for "similiar functionality" but the database will perform better if it can create a specific query plan per procedure.
It would then be just as simple in the VB code to change which stored procedure you are calling with the all same parameters.
Just how I would do it.

In answer to your question, nope you cant use the EXEC with 'IF EXISTS'. You could do options like
IF @pi_Tablename = 'Table1'
BEGIN
--do stuff for table1 in here
END

But once again this would cause recompiles depending on the different scenarios and isnt the ideal way of doing it in SQL.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[prettitoni]

Okay, I'll create 3 different SPs, even thought it will be the exact same code in each.

 

[hmckillop]

Good move, its hard to remove the procedural way of thinking when approaching development within a database, but the whole idea of code reuse isnt necessarily a good thing within databases.
When developing a database within SQL, you should be thinking of performance from the outset. To me performance should always outweigh maintainability.

My 2 cents

"I'm living so far beyond my income that we may almost be said to be living apart

 

[prettitoni]

Thanks for the advice. This is my first time creating a database on the SQL Server....also first time with SPs and Views.

 

[dbomrrsm]

Is this of any use ?

CREATE PROCEDURE TABLE_ROW_COUNT
as

/* declare local variables used for fetch */
declare @tablename nvarchar(80)
declare @strSQL   nvarchar(500)

/* declare the cursor */
declare user_tables cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
open user_tables 

fetch next from user_tables into @tablename
while (@@FETCH_STATUS = 0)
begin
	SET @strSQL = 'SELECT COUNT (*) AS '+ @tablename +' FROM ' + @tablename 
           PRINT @strSQL
	EXEC sp_executesql @strSQL
         fetch next from user_tables into @tablename 
end
CLOSE user_tables
DEALLOCATE user_tables

[blue]DBomrrsm[/blue]