Creating a SQL table variable in a stored procedure

sql stored-procedures sqlparameter
user5164496 picture user5164496 · Jul 28, 2015 · Viewed 33.3k times · Source

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?

Answer

Hari Chaudhary picture Hari Chaudhary · Jul 28, 2015

You can declare table variable in SP as:

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

SELECT * FROM @tablename;

Related questions

How to call Stored Procedures (with 2 parameters) in a Stored Procedure?

I have stored procedures with same parameters (server name and date). I want to write a stored procedure and Exec them in that SP (called it SP_All). CREATE PROCEDURE [dbo].[SP_All] AS BEGIN exec sp_1 @myDate datetime, @ServerName …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More