Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Apr 2004
    Posts
    18

    Unanswered: Get Data From half-Variable Table Name

    Im really new to this and Im really bad.

    but im trying to get the userID From a table,

    but the table is named "Table"+the productID

    ie Table23
    or Table54

    has 2 columns, UnitId and UserID


    heres my code

    CREATE Procedure UserDetail
    (
    @UnitId int,
    @ProductID int,
    @UserID int OUTPUT
    )
    AS

    declare @TSQL varchar(1000)

    SET @TSQL= 'SELECT @UnitId , @ProductID , @UserID ' +
    'FROM Table'+CAST(@ProductID AS char(20))+
    ' WHERE UnitId=@UnitId '


    execute(@TSQL)
    GO



    and i get this error

    Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@UnitId'



    anyhelp would be aprciated,. thanks so much

  2. #2
    Join Date
    Apr 2004
    Posts
    2
    Hi.

    I think, you have to delete the "@" from your target fileds in the SELECT-statement:

    SET @TSQL= 'SELECT UnitId , ProductID , UserID ' +
    'FROM Table'+CAST(@ProductID AS char(20))+
    ' WHERE UnitId=@UnitId '

    Greetings
    LuckyLights

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wouldn't it be a lot simpler to just have one table for all products, then add productId as a column along with unitId and userId?

    -PatP

  4. #4
    Join Date
    Apr 2004
    Posts
    22

    Smile Re: Get Data From half-Variable Table Name

    hi

    set @TSQL= 'select UnitId,' + cast(@ProductID as varchar) + ' as ProductID,UserID from Table' + cast(@ProductID as varchar) + ' where UnitId=' + cast(@UnitId as varchar)

    or try to use sp_executesql with output parameters

  5. #5
    Join Date
    Apr 2004
    Posts
    18
    OK i tried A1ex's method, and its running ok for ProductID, and UnitId, but the thing i really need is UserID, and thats the one thing i really need


    and i get a specified cast is not valid error

    myDetails.UnitId = ((int)parameterUnitId.Value);
    myDetails.ProductID = ((int)parameterProductID.Value);


    myDetails.UserID = ((int)parameterUserID.Value); <-error line

    this is the only one i am calling as an output, could that be why?

  6. #6
    Join Date
    Apr 2004
    Posts
    18
    ok basicially i think i concluded that i dont know what im doing. I dont know how to call the function from my aspx C# page, and i dont know how to use the information correctly.

    Iv implemented many other SQL funcions, but i cant get this one, Ive never really creadted my own procedure to retrieve a value from a table, i have only realy added things to a table, do i use sqlreader or whatever, i dont even know, ive basicially been using the IBUYSPY thing from microsoft as a template to look what they did, but because my table's name is part variable, i have to use this exec() funtion or whatever, and from there i dont know what to do


    so any help would be appreciated

  7. #7
    Join Date
    Apr 2004
    Posts
    18
    just to let everybody know, i figured out a wy to do this, i dunno if this is good coding procedures but i bypassed using a stored procedure all together

    public int GetUserIDFormLamoID(int UnitId,int ProductID)
    {
    SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    string PID=Convert.ToString(ProductID),LID=Convert.ToStri ng(UnitId);

    int UID=0;


    SqlCommand comm = new SqlCommand("SELECT UserID FROM bidTable"+PID+" Where UnitId="+LID,myConnection);
    comm.Connection.Open();
    SqlDataReader r= comm.ExecuteReader(CommandBehavior.CloseConnection );

    while(r.Read())
    {
    UID=r.GetInt32(0);
    }
    r.Close();
    myConnection.Close();
    return UID;
    }






    and now it works perfectly. thanks everyone for your help.

  8. #8
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    I'm curious. What happens when a new item is created? You have to create a new table? I think the design is going the wrong way. I think Pat Phelans Idea is much better. Otherwise, you end up with (say you had 50,000 products) 50,000 tables.

  9. #9
    Join Date
    Apr 2004
    Posts
    22

    Talking

    of cource Pat Phelan & AnSQLQuery have correct solutions of this problem, but if somebody asks me - i`ll try to help, without any hasitations, so:

    create procedure UserDetail
    @UnitId int,
    @ProductID int,
    @UserID int output
    as
    declare @TSQL nvarchar(1000)

    set @TSQL = 'select @iUID = UserID from Table' + cast(@ProductID as varchar) + ' where UnitId=' + cast(@UnitId as varchar)

    exec sp_executesql @TSQL, N'@iUID int output', @iUID = @UserID output
    go

    and example of execution:

    declare @MyTempVar int

    exec UserDetail
    @UnitId = 10,
    @ProductID = 1000,
    @UserID = @MyTempVar output

    select @MyTempVar
    go

    but if field UnitId is not uniq - u`ll have only one last value, no more ...

    good luck

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by a1ex
    of cource Pat Phelan & AnSQLQuery have correct solutions of this problem, but if somebody asks me - i`ll try to help, without any hasitations, so:
    I'm usually gung-ho to help, but I have to weigh the short term benefit of providing people with the answer to their question (as they asked it), against the potential long term harm. That's an issue for my conscience, you can certainly do as you wish!

    -PatP

  11. #11
    Join Date
    Apr 2004
    Posts
    18
    is it really that bad of an idea to have a table for every product,

    im trying to keep very detailed information about every item, like who bought them, how many they bought, if they bought them in a certain order, i couldnt think of anyother way to get all this information, without having a table for eveyproduct.

    what are some of the negative effects of having a table for every product,


    thanks everyone so much for helping me out, and goign above and beyond what i would expect, thanks

    mike

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think 50K tables is a pretty good example of a negative effect.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Apr 2004
    Posts
    18
    yes i get taht, but basiclly what im asking is why is that such a bad thing,

    thanks mike

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    With a question like that it is hard to find a place to begin!

    First and foremost, that design makes a simple "one liner" against a cleaner (normalized) design into a major ordeal using your design. That alone would be enough to convince me.

    Having to write iterative code to scan all of the tables is both cumbersome and error prone. That makes it dangerous in my book, because even if you get it right 99% of the time, what happens in the places where you code something wrong and get the wrong answer?

    I'm lazy, in a big way. I'll do what is necessary to get the correct answer, but I won't do more work than I need to!

    -PatP

  15. #15
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Information relating to products belongs in a Products table. Info about a user goes in a Users table and Info about transactions (who bought what, how many, and when) belongs in a Transaction table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •