Creating a SQL table variable in a stored procedure

Question

I want to create SQL variable table in stored procedure which include this;

Select a,b,c,d from **@tablename** where a=1 and c=0

How can do this with sp when creating sp?

Refer this link http://www.codeproject.com/Articles/18972/Temporary-Tables-vs-Table-Variables-and-Their-Effe — Mukesh Kalgude, Jul 28 '15 at 10:53
You need to use dynamic SQL to include the table name as a parameter in a SQL query. — Gordon Linoff, Jul 28 '15 at 10:55
Which DBMS are you using? "*SQL*" is a query language, not a specific DBMS product. — , Jul 28 '15 at 11:25

score 8 · Answer 1 · answered Jul 28 '15 at 11:24

8

You can declare table variable in SP as:

DECLARE @tablename TABLE(
    a INT,
    b INT,
    c INT,
    d INT);

SELECT * FROM @tablename;

answered Jul 28 '15 at 11:24

Hari Chaudhary

630
1
7
20

score 0 · Answer 2 · answered May 18 '19 at 10:27

0

I hope this answer helps you

GO
CREATE PROCEDURE PrededureName
AS
BEGIN
DECLARE @tempTable TABLE
(
    a INT, 
    b INT,
    c INT,
    d INT
)
INSERT INTO @tempTable (a, b, c, d)
SELECT a, b, c, d FROM @tablename WHERE a=1 and c=0
END
GO;

answered May 18 '19 at 10:27

Ahmad Al ALloush

330
3
10

Creating a SQL table variable in a stored procedure

2 Answers2