How can I convert my sql query into a View?

Question

I know we cannot use declare in a view, however I have no choice now...

is there a way I can use the below in a view, and how can this be done?

    Declare @q varchar(MAX)

;WITH N AS (
  SELECT DISTINCT FD_2A7417DC Pvt_Col 
  FROM   FD_Documents
), C (Cols) As (
  SELECT STUFF((SELECT ',' + QUOTENAME(Pvt_Col) 
                FROM   N
                ORDER BY Pvt_Col
                FOR XML PATH(''), TYPE
               ).value('.', 'NVARCHAR(MAX)') 
              , 1, 1, '')
)
SELECT @q 
       = 'SELECT ID_Number, Company, Date_Of_Birth, Department, Name
               , Surname, Passport_No, Job_Title, Start_Date
               , End_Date, Type
               , ' + Cols + ' 
          FROM  (SELECT FD_0D39C6B0 AS Company, FD_DDD24E25 AS Date_Of_Birth, FD_74290EA9 AS Department, FD_2A7417DC AS Document_Type, FD_E3421C61 AS Name
                      , FD_2B9CFA8C AS Surname, FD_96990BE2 AS ID_Number, FD_E6621D8A as Passport_No, FD_3744C28A AS Job_Title, FD_903EBB60 as Start_Date
                      , FD_9BBAD0CA AS End_Date, FD_AE108F93 as Type
                 FROM FD_Documents
                ) x
                PIVOT 
                (COUNT(Document_Type) FOR Document_Type in (' + Cols + ')) pvt'
FROM C

execute(@q)

Update 15/08/2014

This is were I'm at now.

create function dbo.tvf_getDocumentType()
returns @t table(ColName int FD_0D39C6B0 AS Company, FD_DDD24E25 AS Date_Of_Birth, FD_74290EA9 AS Department, FD_2A7417DC AS Document_Type, FD_E3421C61 AS Name
                      , FD_2B9CFA8C AS Surname, FD_96990BE2 AS ID_Number, FD_E6621D8A as Passport_No, FD_3744C28A AS Job_Title, FD_903EBB60 as Start_Date
                      , FD_9BBAD0CA AS End_Date, FD_AE108F93 as Type)
as
begin
   Declare @q varchar(MAX)

;WITH N AS (
  SELECT DISTINCT FD_2A7417DC Pvt_Col 
  FROM   FD_Documents
), C (Cols) As (
  SELECT STUFF((SELECT ',' + QUOTENAME(Pvt_Col) 
                FROM   N
                ORDER BY Pvt_Col
                FOR XML PATH(''), TYPE
               ).value('.', 'NVARCHAR(MAX)') 
              , 1, 1, '')
)
SELECT @q 
       = 'SELECT ID_Number, Company, Date_Of_Birth, Department, Name
               , Surname, Passport_No, Job_Title, Start_Date
               , End_Date, Type
               , ' + Cols + ' 
          FROM  (SELECT FD_0D39C6B0 AS Company, FD_DDD24E25 AS Date_Of_Birth, FD_74290EA9 AS Department, FD_2A7417DC AS Document_Type, FD_E3421C61 AS Name
                      , FD_2B9CFA8C AS Surname, FD_96990BE2 AS ID_Number, FD_E6621D8A as Passport_No, FD_3744C28A AS Job_Title, FD_903EBB60 as Start_Date
                      , FD_9BBAD0CA AS End_Date, FD_AE108F93 as Type
                 FROM FD_Documents
                ) x
                PIVOT 
                (COUNT(Document_Type) FOR Document_Type in (' + Cols + ')) pvt'
FROM C

execute(@q)

end

Error Message. Msg 102, Level 15, State 1, Procedure tvf_getDocumentType, Line 2 Incorrect syntax near 'FD_0D39C6B0'.

Im now going into uncharted water with this code for me.

You can't since views are static (you can't paramterize them). Use a procedure instead. — Patrick Hofman, Aug 04 '14 at 14:05

score 2 · Accepted Answer · edited May 23 '17 at 10:26

I would start looking at this link right here for starters. However, your stored procedure should look something like this:

/****** Object:  StoredProcedure [dbo].[getDocumentType]    Script Date: 8/4/2014 1:11:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getDocumentType]

AS
BEGIN
    Declare @q varchar(MAX)

    ;WITH N AS (
      SELECT DISTINCT FD_2A7417DC Pvt_Col 
      FROM   FD_Documents
    ), C (Cols) As (
      SELECT STUFF((SELECT ',' + QUOTENAME(Pvt_Col) 
                    FROM   N
                    ORDER BY Pvt_Col
                    FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
                  , 1, 1, '')
    )
    SELECT @q 
           = 'SELECT ID_Number, Company, Date_Of_Birth, Department, Name
                   , Surname, Passport_No, Job_Title, Start_Date
                   , End_Date, Type
                   , ' + Cols + ' 
              FROM  (SELECT FD_0D39C6B0 AS Company, FD_DDD24E25 AS Date_Of_Birth, FD_74290EA9 AS Department, FD_2A7417DC AS Document_Type, FD_E3421C61 AS Name
                          , FD_2B9CFA8C AS Surname, FD_96990BE2 AS ID_Number, FD_E6621D8A as Passport_No, FD_3744C28A AS Job_Title, FD_903EBB60 as Start_Date
                          , FD_9BBAD0CA AS End_Date, FD_AE108F93 as Type
                     FROM FD_Documents
                    ) x
                    PIVOT 
                    (COUNT(Document_Type) FOR Document_Type in (' + Cols + ')) pvt'
    FROM C

    execute(@q)
END
GO

You should then be able run an exec getDocumentType to retrieve the data (or through your front end code, however you were going to retrieve it).

UPDATE ON 08/12/2014

Based on our conversation in the comments, you could also go down the route of a table valued function, which shouldn't look too different than a stored procedure. I would suggest the following:

CREATE FUNCTION dbo.tvf_getDocumentType()
RETURNS TABLE
    AS
RETURN
    (
        /*
        Put all code between the BEGIN and END here
        */
    )
GO

You should be able do then do the following:

select * dbo.tvf_getDocumentType()

This select statement should be able to go into a view.

UPDATE ON 8/14/2014

There's apparently 2 ways to use table valued functions, and the option mentioned above is one way. The second option is listed here. I'm not sure how to exactly tweak your query to go along with it, but you would have to setup in this fashion:

create function dbo.tvf_getDocumentType()
returns @T table(ColName int /*list necessary columns here*/)
as
begin
  /*insert code here*/
end

You may have to modify your dynamic query to select your items into the table variable as part of your execution. I think I may need to do more research on this option.

Update on 8/19/2014

Based on your notes below, what happens when you do this? Referenced Setup listed here.

create function dbo.tvf_getDocumentType()
returns @TableResults TABLE (
    /*Please adjust your data types according to your column structures.
      Data types listed here are strictly for explanation and is just a 
      guess on what you have.*/
    ID_Number int NOT NULL,
    Company varchar(32) NOT NULL,
    Date_Of_Birth datetime NOT NULL, 
    Department varchar(32) NOT NULL, 
    Name varchar(32) NOT NULL, 
    Surname varchar(32) NOT NULL, 
    Passport_No varchar(16) NOT NULL, 
    Job_Title varchar(32) NOT NULL, 
    Start_Date datetime NOT NULL,
    End_Date datetime NOT NULL, 
    Type varchar(32) NOT NULL,
    Cols varchar(32) NOT NULL
    )
as
begin
    Declare @q varchar(MAX)

    ;WITH N AS (
      SELECT DISTINCT FD_2A7417DC Pvt_Col 
      FROM   FD_Documents
    ), C (Cols) As (
      SELECT STUFF((SELECT ',' + QUOTENAME(Pvt_Col) 
                    FROM   N
                    ORDER BY Pvt_Col
                    FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
                  , 1, 1, '')
    )
    SELECT @q 
           = 'INSERT INTO  @TableResults
            (ID_Number, Company, Date_Of_Birth, Department, Name
                   , Surname, Passport_No, Job_Title, Start_Date
                   , End_Date, Type, Cols) 
            SELECT ID_Number, Company, Date_Of_Birth, Department, Name
                   , Surname, Passport_No, Job_Title, Start_Date
                   , End_Date, Type
                   , ' + Cols + ' 
              FROM  (SELECT FD_0D39C6B0 AS Company, FD_DDD24E25 AS Date_Of_Birth, FD_74290EA9 AS Department, FD_2A7417DC AS Document_Type, FD_E3421C61 AS Name
                          , FD_2B9CFA8C AS Surname, FD_96990BE2 AS ID_Number, FD_E6621D8A as Passport_No, FD_3744C28A AS Job_Title, FD_903EBB60 as Start_Date
                          , FD_9BBAD0CA AS End_Date, FD_AE108F93 as Type
                     FROM FD_Documents
                    ) x
                    PIVOT 
                    (COUNT(Document_Type) FOR Document_Type in (' + Cols + ')) pvt'
    FROM C

    execute(@q)

return
end

This should work, though I can't test your query. Once done, run the following line:

select * from dbo.tvf_getDocumentType()

If it works then, you should be able to throw that into a view. I'm at a loss beyond this, if it doesn't work.

Hi Tiny Haitian, this is perfect. thank you so much. No when I run the exec getDocumentType, it gives me an error. — user3906930, Aug 12 '14 at 09:49
Just find out that the front end software can only cater for Select statements or views, can I save the SP in a view or create a Select statement on my SP — user3906930, Aug 12 '14 at 09:58
I've never tried to do a stored procedure inside a view (not sure if possible), but I can research too. will keep you posted. — Tiny Haitian, Aug 12 '14 at 14:15
No problem. After a little bit of research, you may have to go the route of a table valued function, which would give you a similar setup. Look [here](http://stackoverflow.com/questions/916784/how-to-call-stored-procedure-in-a-view). I've messed around a little with table valued functions and will post another code set shortly. — Tiny Haitian, Aug 12 '14 at 14:31
Hey @user3906930, Check my update and let me know if it works. I can't fully test your query, but I was able to test the suggested setup with dummy info and create/test both the function and view with no issue. — Tiny Haitian, Aug 12 '14 at 15:04
Hi @Tiny Haitian, thank you for the response, I insert my original code, but it gave me an error. Msg 156, Level 15, State 1, Procedure tvf_getDocumentType, Line 5 Incorrect syntax near the keyword 'Declare' — user3906930, Aug 14 '14 at 11:21
I added an update again; I'd have to do more research, but I think this should point you to the right direction. — Tiny Haitian, Aug 14 '14 at 15:39
HI @Serpiton, I know you have help before, Please could you also have a look at this? — user3906930, Aug 15 '14 at 05:44
I think I may have exhausted my last bit of suggestion, but give yet another look to my update. Please let me know if it works. I'm at a loss beyond this, if it doesn't work. I also noticed after my code update, that you have to specify the data type for the columns as opposed to listing your columns names as something else. — Tiny Haitian, Aug 19 '14 at 19:20
Hi @Tiny Haitian, I get this error Msg 443, Level 16, State 14, Procedure tvf_getDocumentType, Line 52 Invalid use of a side-effecting operator 'EXECUTE STRING' within a function. — user3906930, Aug 20 '14 at 05:36

How can I convert my sql query into a View?

1 Answers1