Solved

How do I add a parameter to a view

Posted on 2014-01-26
6
225 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

22 Experts available now in Live!

Get 1:1 Help Now