Solved

Variables in a view

Posted on 2004-08-28
3
207 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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