Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I add a parameter to a view

Posted on 2014-01-26
6
Medium Priority
?
236 Views
Last Modified: 2014-01-30
Hey guys,

I have a view that I need to filter by the record number.  There is no joy in Mudville.

Please help if you can!!

Jerry
View.txt
0
Comment
Question by:JDL129
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 500 total points
ID: 39811645
you cannot, you need to filter the results of the view:

select * from vwName where "no joy in Mudville"

You should also look at SQL UDF (User defined functions) to which you can pass arguments
0
 

Author Comment

by:JDL129
ID: 39812493
Eric thanks for the post!!!

I thought that was what I was trying to do in the file I attached to the question but I keep getting errors:

Msg 102, Level 15, State 1, Procedure vRxLabel, Line 2
Incorrect syntax near '@RecordNum'.
Msg 137, Level 15, State 2, Procedure vRxLabel, Line 14
Must declare the scalar variable "@RecordNum".

I am trying to use NiceLabel and I must select a recordset from sql server and it appears to only like tables and views.  I set it up to upload the data to a table and it worked ok when only one station was using the data.  When more than one was using it became difficult to ensure the correct information for each station.

What would be the best way to do this?

Thanks Eric!!

Jerry
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39812518
You cannot declare a variable into a View.

The only thing you can do is:

select * from [dbo].[vRxLabel]

I don't know NiceLabel. Can it use UDFs?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:JDL129
ID: 39812541
Thanks Eric!!  I'll give it a try.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39812545
Ok, to explain this better i use the below example

let us assume the below is the table that you want to create a view on

create table testTable
(
  A BIGINT IDENTITY(1,1), Data VARCHAR(100) NULL
)
INSERT INTO testTable(data) values('1');
INSERT INTO testTable(data) values('10');
INSERT INTO testTable(data) values('20');
INSERT INTO testTable(data) values('30');

Open in new window


Now below is how you can write a parametrized view on the table, actually by using the UDF

CREATE FUNCTION dbo.paramViewTestTable
(
 @paramData VARCHAR(1000)
)
RETURNS TABLE 
AS
RETURN
(
 SELECT * from testTable WHERE data= @paramData
)

Open in new window


You can use this view (actually it will be a UDF) as below

select * FROM dbo.paramViewTestTable('30')

Open in new window

0
 

Author Closing Comment

by:JDL129
ID: 39822579
Thanks guys!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question