Solved

How do I add a parameter to a view

Posted on 2014-01-26
6
230 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

828 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