Solved

How do I add a parameter to a view

Posted on 2014-01-26
6
232 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 125 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

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

Accepted Solution

by:
Surendra Nath earned 125 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 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