How to use parameters in a view

Question

I tried to assign the values to a view using parameters but I'm getting the error:

'ALTER VIEW' must be the first statement in a query batch.

When using GO statement, its unable to access the variables.

How can I assign parameters to a view.

USE [DBNAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CA_Removal varchar(max)
SET @CA_Removal=(SELECT value FROM spider.Configuration WHERE id='Removal')
GO

ALTER view [spider3].[View] WITH SCHEMABINDING
as
select
asa.id,
ase.common,
from
[spider].Activity asa
inner join
[spider].External ase
on
asa.primaryKey = ase.owner
where
asa.type = TRY_CONVERT(int, @CA_Removal)

When asa.type = 10 it works properly.

You cannot use a local variable as some sort of kludge to parameterize a view. But you don't appear to need a parameter. Just change the where clause to where asa.type = (select value FROM spider.Configuration WHERE id='Removal'); — SMor
– SMor, Commented Aug 4, 2021 at 6:00
@Zhorov im getting error as -->Must declare the scalar variable "@CA_Removal". — Codeninja
– Codeninja, Commented Aug 4, 2021 at 6:01
You can't use a parameter in a view. If you can't inline the 10 sub-queries (which is perfectly reasonable to do using cross apply), you need a table valued function. You need to ensure your question accurately reflects the problem you are trying to solve simplify yes, but don't oversimplify. — Dale K
– Dale K, Commented Aug 4, 2021 at 6:12
@Codeninja as I said above You can't use a parameter in a view - never - ever. The fact that you have a GO between your parameter definition and your view definition means they are in 2 completely separate batches and have no knowledge of each other. — Dale K
– Dale K, Commented Aug 4, 2021 at 6:39
Check my answear - there is a method how to use parameter in view — Adamszsz
– Adamszsz, Commented Aug 4, 2021 at 12:45

Gordon Linoff · Accepted Answer · 2021-08-04 11:44:35Z

2

You don't want a view. You want a table-valued function:

create function spider3.udf_spider (
    @CA_Removal varchar(max)
) returns table
as return(select asa.id, ase.common
           from [spider].Activity asa join
                [spider].External ase
                on asa.primaryKey = ase.owner
           where asa.type = TRY_CONVERT(int, @CA_Removal)
          );

It seems really awkward to pass in a string that you just convert to an integer. I would strongly recommend that you pass in the value as an integer:

create function spider3.udf_spider (
    @CA_Removal int
) returns table
as return(select asa.id, ase.common
           from [spider].Activity asa join
                [spider].External ase
                on asa.primaryKey = ase.owner
           where asa.type = @CA_Removal
          );

answered Aug 4, 2021 at 11:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-08-04 12:04:03Z

As mentioned by others in the comments, a view cannot have parameters. Furthermore, the two batches are entirely separate, and cannot reference variables from one to the next anyway.

You have two options:

Create the view with a join

CREATE OR ALTER view [spider3].[View] WITH SCHEMABINDING
as
select
  asa.id,
  ase.common,
from
  [spider].Activity asa
inner join
  [spider].External ase
  on asa.primaryKey = ase.owner
where asa.type = TRY_CONVERT(int,
    (SELECT value FROM spider.Configuration WHERE id='Removal')
);

GO

Create an inline Table Valued Function

CREATE OR ALTER FUNCTION [spider3].[iTVF]
(@typeId int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (

select
  asa.id,
  ase.common,
from
  [spider].Activity asa
inner join
  [spider].External ase
  on asa.primaryKey = ase.owner
where asa.type = @typeId

);

GO

Adamszsz · Accepted Answer · 2021-08-04 12:43:34Z

1

Normally views are not parameterized. But you could always inject some parameters. For example using session context:

CREATE VIEW my_view
AS
SELECT *
FROM tab
WHERE num = SESSION_CONTEXT(N'my_num');

Invocation:

EXEC sp_set_session_context 'my_num', 1; 
SELECT * FROM my_view;

And another:

EXEC sp_set_session_context 'my_num', 2; 
SELECT * FROM my_view;

DbFiddleDemo

edited Aug 4, 2021 at 12:43

answered Aug 4, 2021 at 8:19

Adamszsz

5814 silver badges15 bronze badges

1 Comment

Charlieface Over a year ago

@ThorstenKettner Not me, but seems to be a hack to me, why not just use a TVF like you're supposed to?

Collectives™ on Stack Overflow

How to use parameters in a view

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related