Call sql view inside store procedure

Sohail Shahzad

Currently working on BI tool in .NET MVC. i have created procedure that accept sql statement as string to clone temp table from another table using select query. Now i want to create view of temp table that is recently created by using string.How to do i perform this task.

My code is

USE [AirViewX2_DEV]
GO
/****** Object: StoredProcedure [dbo].[Tmp_CloneOfDataset] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Tmp_CloneOfDataset]
@Filter NVARCHAR(500),
@Value NVARCHAR(50)=NULL
AS
BEGIN
SET NOCOUNT ON
--IF(@Filter='CLONE_OF_DATASET')
--BEGIN
DECLARE @GetTemName varchar(200)= RTRIM(LTRIM( SUBSTRING(@Filter,CHARINDEX('into',@Filter)+4,CHARINDEX('from',@Filter)-CHARINDEX('into',@Filter)-4)))
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @GetTemName)
BEGIN
DECLARE @alreadyExist int =1;
return @alreadyExist
--DECLARE @SQL NVARCHAR(MAX)
--SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@GetTemName) + '';
-- EXEC sp_executesql @SQL;
EXECUTE(@Filter)
END
ELSE
BEGIN
EXECUTE(@Filter)
declare @sqlcode varchar(max)

Here i want to create sql view for @GetTemName

--select @sqlcode = 'create view @GetTemName as select * from MyTable'
--@sqlcode+=' '@GetTemName
--exec (@sql)
DECLARE @status varchar(200) ='Done'
SELECT @status
END
--END
END

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tamal Marick7y ago
Accepted answer
If you want to create a view from within a SP you need to use dynamic SQL.

Something like this

exec ('create view MyView as select * from MyTable as Col')

The create view... code has to be sent as a string parameter to exec and by the looks of it you already have the code you need for the view so just embed it in between the '.
+2
Sohail Shahzad7y ago
Thanks all for reply. Tamal Marick it works for me
0
Amit Kumar7y ago
Hi,You can call view like select data from table. Call view like: SELECT *FROM VIEWNAME
+1
Sivakumar Koneti7y ago
select * from viewname
lookover this link once

https://www.c-sharpcorner.com/UploadFile/raj1979/views-stored-procedure-in-sql-server-2005/
+2
Rajanikant Hawaldar7y ago
Hi Sohail, Calling view in stored procedure is like this : select *from GetTemName
+2