Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 2nd, 2005, 02:22 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Assigning values to variables in Stored Procedures

I am retrieving about 15 fields that I need to convert into a single plain English Description.

Here is the Select Statement:
    SELECT dbo.TblRIInventory.DocFeeder AS DocFeeder, dbo.TblRIInventory.FeederModel AS FeederModel, dbo.TblRIInventory.Duplex AS DupLex,
        dbo.TblRIInventory.LCT AS LCT, dbo.TblRIInventory.Cassette AS Cassette, dbo.TblRIInventory.PrintOpt AS PrintOpt,
                     dbo.TblRIInventory.NICOpt AS NICOpt, dbo.TblRIInventory.FaxOpt AS FAXOpt, dbo.TblRIInventory.PrintModel AS PrintModel,
                      dbo.TblRIInventory.FaxModel AS FaxModel, dbo.TblRIInventory.ScanOpt AS ScanOpt, dbo.TblRIInventory.ScanModel AS ScanModel,
                      dbo.TblRIInventory.SorterFinisher AS SorterFinisher, dbo.TblRIInventory.SorterModel AS SorterModel
    FROM dbo.TblRIInventory
     WHERE (dbo.TblRIInventory.ItemNumber = @ItemNumber)

(Originally I did this without the AS Statements)

Now based on the value of the entry, I wnat to include it into a variable @Description Declared nvarchar(255).

Using:
SELECT @Description = DocFeeder FROM dbo.tblRIInventory
 I received the first columns data, however
Using:
    SELECT @TempString = FeederModel FROM dbo.TblRIInventory
@TempString nvarchar(255)
I received Null Data.
I know that the second column has data in it.
I cannot beleive that the only way for me to get the data I need and to manipulate it is by:
    SELECT @Description = DocFeeder FROM dbo.tblRIInventory WHERE dbo.TblRIInventory.ItemNumber = @ItemNumber
    SELECT @TempString = FeederModel FROM dbo.TblRIInventory WHERE dbo.TblRIInventory.ItemNumber = @ItemNumber

I cannot beleive the overhead that this approach would cost. A read for every field.

Let me know if their is an alternative.

Alan


Reply With Quote
  #2 (permalink)  
Old May 2nd, 2005, 11:26 PM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The question is not clear to me, Are looking for the "COALESCE" function which Returns the first nonnull expression among its arguments.

Reply With Quote
  #3 (permalink)  
Old May 3rd, 2005, 09:07 AM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Actually, I am parsing the data to develop a description of the itme.
We are in the Copier Lease resale market, so each unit has a unique description.
What type of Feeder, does it Duplex, does it have a printer interface, etc.

The data is either a Bit type or a nvarchar depending on whether it is a description of an option, or whether a certain option exists on this unit.
I realize now that I am running subqueries on a query for the specific item, to determine the status or description of an option.
It's alot of code, but it appears to be working.
In VBA I could do it in less then half the statements I am using for the sproc.
I want to move as much of the coding to sprocs, triggers, and user functions that I can. Making it easier to support and modify.
Thanks for the reply.

Reply With Quote
  #4 (permalink)  
Old May 4th, 2005, 12:37 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:In VBA I could do it in less then half the statements
We could help you , Seeing the vba code how to convert it to SQL.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem assigning my Array values to rows - Please Edoloto Excel VBA 1 August 18th, 2008 04:13 AM
Automate assigning the values from one table’s tex pkaptein1 Access 1 May 14th, 2005 06:57 AM
Assigning Session Variables morpheus Classic ASP Basics 2 November 21st, 2003 01:55 PM
Assigning Values to Variables shabboleth Beginning PHP 2 September 4th, 2003 07:17 AM



All times are GMT -4. The time now is 01:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.