How to give table name dynamically in stored procedure in SQL Server 2012
Sk Asraf
I am creating two tables that have some rows and columns. And I created a stored procedure which dynamically takes table name and produce the result.
But when I pas the table name dynamically, then it not work.
CREATE PROC spGetAll (@tableName varchar(50))
AS
BEGIN
SELECT
*
FROM @tableName
END
gofr1
Just use dynamic SQL. Example:
CREATE PROC spGetAll (
@tableName SYSNAME)
AS
BEGIN
DECLARE @sql nvarchar(max)
IF EXISTS (SELECT name FROM sys.objects WHERE name = @tableName)
SET @sql = 'SELECT * FROM test.'+ QUOTENAME(@tableName)
EXEC(@sql) -- Or EXEC sp_executesql @sql
END
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Incorrect structure of XML generated in SQL
- Next: App Engine: What is the Maximum URLFetch Timeout Deadline in a Taskqueue / Backend
Related
Related Related
- 1
How to give table name dynamically in stored procedure in SQL Server 2012
- 2
SQL Server 2012 - Pass stored procedure name to another stored procedure
- 3
Stored procedure in SQL Server 2012
- 4
Passing table name and column name dynamically to PL/SQL Stored procedure
- 5
In SQL Server 2012, how do I get the column name and data type from a view, function, or stored procedure?
- 6
How to pass column name as well as Table Name to a stored procedure as a parameter in SQL Server?
- 7
How to create a select, and then an update stored procedure in SQL Server 2012
- 8
Dynamic SQL Server stored procedure with table name as input param
- 9
SQL Server: How to use a database name as a parameter in a stored procedure?
- 10
SQL Server: How to use a database name as a parameter in a stored procedure?
- 11
How to retrieve dropped stored procedure, function, table in SQL Server 2008
- 12
How to read data table from SQL Server stored procedure
- 13
How to read data table from SQL Server stored procedure
- 14
How to create a stored procedure on SQL server 2014 for create table operation?
- 15
How to DROP and CREATE a table in SQL Server 2016 Stored Procedure
- 16
SQL Server Profiler: Get stored procedure name
- 17
Invalid object name sql server stored procedure
- 18
SQL server stored procedure return a table
- 19
SQL Server insert table column to stored procedure
- 20
How do you merge two different SQL Server 2012 database tables in single stored procedure?
- 21
Execute multiple statements and CTE in stored procedure in SQL Server 2012?
- 22
Rollback in single stored procedure for forloop & insert query in SQL Server 2012
- 23
SQL Server 2012 Insert Stored Procedure for N parameters
- 24
tsql not firing (stored procedure via ssis) - SQL Server 2012
- 25
Execute Stored Procedure with RollbackTrans using VBA and SQL Server 2012
- 26
SQL Server 2012 Write Return Values of Stored Procedure to Cells using Stored Procedure
- 27
How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?
- 28
How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?
- 29
How to capture error in SQL Server stored procedure?
Comments