How to create a views with variable

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  February 24, 2017 at 1:01 pm

  #319015

  The code below can run in store procedure.
  How to create a views from this code?

  DECLARE @CODE VARCHAR(10)
  SET @CODE = 'AAA'
  SELECT *
  FROM MYORDER
  WHERE @CODE BETWEEN CODEFROM AND CODETO

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 24, 2017 at 1:09 pm

  #1930260

  Views do not accept parameters. If you must pass a parameter, then you need to continue to use a stored procedure.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  February 24, 2017 at 1:29 pm

  #1930269

  You might want to check an inline table-valued function.
  
--Create some sample data and data structure
CREATE TABLE MYORDER(
  ORDERID int,
  CODEFROM varchar(10),
  CODETO varchar(10)
)
INSERT INTO MYORDER
VALUES( 1, 'A', 'L'),
  ( 2, 'LA', 'ZZZZ')
GO

  --Create a function instead of a view to allow parameters
CREATE FUNCTION OrderWithCode(
  @CODE varchar(10)
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM MYORDER
WHERE @CODE BETWEEN CODEFROM AND CODETO ;
GO

  --Use the table valued function
DECLARE @CODE VARCHAR(10);
SET @CODE = 'AAA';

  SELECT *
FROM OrderWithCode(@CODE) o;

  GO
--Clean my sandbox
DROP FUNCTION OrderWithCode;
DROP TABLE MYORDER;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jeff Moden

  SSC Guru

  Points: 1003895

  More actions

  February 25, 2017 at 3:06 pm

  #1930352

  Luis Cazares - Friday, February 24, 2017 1:29 PM

  You might want to check an inline table-valued function.

  I strongly second that.  Think of them as "Parameterized Views".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)