Solved

How do I add a parameter to a view

Posted on 2014-01-26
6
228 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
  • 3
  • 2
6 Comments
 
LVL 69

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 69

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 46
Getting the 3 middle digits 4 36
execute a MS SQL script as a schedule SQL job 72 123
Sql Query join multiple table and distinct records 7 29
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now