I am creating a query that will select all data on the table. The query will select to the table base on the variable that I will pass on the stored procedure.
Here in my example. If I execute example_sp table1 it will select in table1. Same thing if I use example_table table2, it should selecttable2.
ALTER PROCEDURE example_sp
@type varchar(10), -- value will be `table1` or `table2`
AS
BEGIN
SELECT * FROM @type
END
A slightly different version of what Gordon has suggested.....
ALTER PROCEDURE example_sp
@TableName SYSNAME --<-- Use appropriate data type for sql server objects
AS
BEGIN
SET NOCOUNT ON;
Declare @Sql NVARCHAR(MAX);
SET @Sql = N' SELECT * FROM ' + QUOTENAME(@TableName)
Exec sp_executesql @Sql
END
You need dynamic SQL to pass in identifiers such as table names and column names:
ALTER PROCEDURE example_sp (
@tablename varchar(10) -- value will be `table1` or `table2`
)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT t.* FROM @tablename t';
SET @sql = REPLACE(@sql, '@tablename', QUOTENAME(@tablename));
EXEC sp_executesql @sql;
END;
Your SP will have to return 2 result sets, one being empty. Just put both SELECT statements, including WHERE @type = 'table1'
It's even better if the result sets match, in which case you can simply merge them with UNION
