Solved

Variables in a view

Posted on 2004-08-28
3
208 Views
Last Modified: 2010-04-23
I need to select records for a Crystal Report.  I thought the best way was to create a view - i need to select records based on their shipment number - my code is below and the question follows:
--------------
DECLARE @ship char(5);
SET              @ship = '1012';
                          SELECT     dbo.tblShipments.id AS ShipNum, dbo.tblShipments.Destination AS Destination, dbo.tblShipments.Date AS ShipDate,
                                                  dbo.tblShipmentData.palletid AS Pallet, dbo.tblShipmentData.tubcode AS Product, dbo.tblShipmentData.qty AS qty,
                                                  dbo.tblShipmentData.dateprod AS DateProd
                           FROM         dbo.tblShipments RIGHT OUTER JOIN
                                                  dbo.tblShipmentData ON dbo.tblShipments.id = dbo.tblShipmentData.shipnum
                           WHERE     (dbo.tblShipments.id = @ship)
---------------------
Now, how, in VB or Chrystal, so i pass the current @ship value to the view????
0
Comment
Question by:Dan Flood
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11922412
you wouldnt do that ...

you would either use a stored procedure and pass a parameter or use ...

view ...
                          SELECT     dbo.tblShipments.id AS ShipNum, dbo.tblShipments.Destination AS Destination, dbo.tblShipments.Date AS ShipDate,
                                                  dbo.tblShipmentData.palletid AS Pallet, dbo.tblShipmentData.tubcode AS Product, dbo.tblShipmentData.qty AS qty,
                                                  dbo.tblShipmentData.dateprod AS DateProd
                           FROM         dbo.tblShipments RIGHT OUTER JOIN
                                                  dbo.tblShipmentData ON dbo.tblShipments.id = dbo.tblShipmentData.shipnum

SELECT * from YourViewName where ShipNum = '1012'
0
 

Author Comment

by:Dan Flood
ID: 11922710
oh!
Thank you - I didn't understand how to use views.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11923052
no worries ...

Let me know if you require any further help.

Cheers,

Greg
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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