Using Declare in SQL Server view

Question

I have a query like this:

DECLARE @year_start INT
DECLARE @year_end INT

SET @year_start = 2005
SET @year_end   = 2014

; WITH p_year AS
(
    SELECT p_year = @year_start

    UNION ALL

    SELECT p_year = p_year + 1
    FROM p_year 
    WHERE p_year < @year_end
),
Interval AS
(---
),
CTE AS
(---
),
CTE_1 AS 
(---
)
SELECT something
FROM CTE_1
WHERE Rank <= 3
ORDER BY something

I tried it using creating table valued function but can't get that how to manipulate with variables in the table valued function declaration.

Whereas I tried creating table valued function as:

CREATE FUNCTION P_Count()
RETURNS TABLE
AS  
DECLARE ... 
...

I want to make it a view but Declare statement not allows me. How can I make it a view?

a view cannot have parameters, that's it. you have to create something else, like a table valued function. if you have issues creating a table valued function, post the relevant code and the errors you get. — Paolo, Jul 25 '16 at 14:22
Think about what a view is. It is a static virtual table in the database. By definition parameters cannot be there since that would be something else. — clifton_h, Jul 25 '16 at 17:36
Sounds like you should read [MSDN - User Defined Functions](https://msdn.microsoft.com/en-us/library/ms191007.aspx) which explains the 3 types of functions and the more detailed [MSDN - Create User Defined Functions](https://msdn.microsoft.com/en-us/library/ms191320.aspx), where it explains the Syntax, use cases, and has a great examples to help you. MSDN is awesome. :) — clifton_h, Jul 25 '16 at 17:44

score 0 · Answer 1 · answered Jul 25 '16 at 15:10

0

your create function script misses a BEGIN:

CREATE FUNCTION P_Count()
RETURNS @tableName TABLE (structure here)
AS  
BEGIN
  DECLARE...
  ...
  RETURN;
END;

here is the syntax reference on msdn

answered Jul 25 '16 at 15:10

Paolo

2,224
1
15
19

score 0 · Answer 2 · answered Dec 12 '17 at 21:26

Maliks, I suppose, some were confused with your SQL. It was not immediately apparent that you use a recursive CTE to generate years in a range. There are lots of awesome ways to generate a sequence of integers (or numbers, in general) that can be combined with a CTE to emulate variables in a view. If numbers are generated way too often, consider building a separate (and indexed) table for them.

Here is what I think you were looking for. I omit the CTEs, rank and grouping you have not defined in your question. You can modify it as you wish:

WITH params AS (SELECT BegYr = 2005, EndYr=2014),
nums AS (SELECT DISTINCT n = number FROM master..spt_values WHERE number>=0)
SELECT Yr=BegYr + n FROM nums, params
WHERE n <= EndYr - BegYr

More generally, here is a sample view query that uses CTE to nicely emulate internal variable construction. You can test-run it in your version of SQL Server.

CREATE VIEW vwImportant_Users AS
WITH params AS (
    SELECT 
    varType='%Admin%', 
    varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers, params
    WHERE status > varMinStatus OR name LIKE varType

SELECT * FROM vwImportant_Users

yielding output:

status  name
12      dbo
0       db_accessadmin
0       db_securityadmin
0       db_ddladmin

also via JOIN

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers INNER JOIN params ON 1=1
    WHERE status > varMinStatus OR name LIKE varType

also via CROSS APPLY

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers CROSS APPLY params
    WHERE status > varMinStatus OR name LIKE varType

Using Declare in SQL Server view

2 Answers2