Post reply

Using CASE for branched INPUT

  • December 4, 2001 at 10:54 am

    #157758

    Hello,, I am learning and need some advice.

    This application is sending about 2000 variables to SQL server. It does not know which table they are going or any of that. I am passing this information to a stored procedure in this form

    Key 1

    Key 2

    Variable

    Value

    This stored procedure then dumps this incoming data into a Table as

    Key 1

    Key 2

    Variable

    Value

    Table it Belongs

    Data Type of this variable

    That table has a trigger for INSERT and supposed to take this incoming data and place it at the right place.

    There are 12 tables involved here and about 3000 variables all together. Primary key is same in 10 tables(key 1 + key 2) but different in 2 of them (just key1).

    This seems complicated, to me at least, but i would very much appreciate any better solution or input.

    I am try to use this

    CASE @TABLE

    WHEN NATALOG THEN

    IF NOT EXISTS (SELECT PatKey FROM NATALOG WHERE patkey = @patkey)

    BEGIN

    INSERT INTO NATALOG(PATKEY) VALUES(@PATKEY)

    END

    WHEN something else THEN

    but it wont work,, give me systax error.

    Sorry for long post.

  • December 4, 2001 at 11:01 am

    #420698

    np on the long post, but it doesn't make sense.

    This is one table that gets a bunch of information, but what does the table look like? What columns?

    Then something calls a stored procedure and passes what parameters?

    Your post is rather unclear about what you are trying to achieve. It looks like you want a trigger to insert data into some other table based on the data in the "inserted" table.

    I think there is a better way, but I'd like to know what you are trying to accomplish.Posting the actual code and script for the table will help alng with a sample of how the data needs to move.

    Steve Jones

    steve@dkranch.net

  • December 4, 2001 at 11:28 am

    #420703

    Thanks,,, let me explain.

    We scan about million pages of paper based surveys that we recieve in mail. Scanning application has to ability to turn this into raw data, it saves each input entry and we assign a name/variable to it. This application can export this data to any data source execpt it only does INSERTs and no updates. So if you happen to have data on multiple pages going to the same table, it will not do it as primary key is already there.

    We have been dumping data to Access as one Table for each page, thats about 29 Tables. Then we run another application to port it to the appropriate Tables in SQL, it is error prone and takes time.

    Data in SQL resides in about 12 Tables.

    What I am thinking to do is this.....

    Export data from each page to a stored procedure that takes three parameters.

    1) Key (more than one)

    2) Variable

    3) Value for this Variable

    More like this actually

    @iCount int,

    @Variable varchar(8000),

    @Values varchar(255),

    @key varchar(12)

    Where @iCount is the number of variables passed, @Variable and @Values are string that I parse to get the actual data, they are delimited by ~ .

    This stored procedure parse this incoming data and place that in a Table with the following columns

    Key

    Variable

    Value

    DataType of Variable

    TargeTable

    This Table have trigger for INSERT and should use this row of data to place it accordingly.

    My idea was to check the following

    If key does not exists in the target Table, add it.

    Update @Variable with @Value in TargetTable, covert the value to target datatype.

    But there is an issue here, well plenty more i guess. It is not wise to check the existance of Key in TargetTable on each insertion as it would be done millions of times, needlessly,, there have to be a better way of doing this.

    Hope it helps to clear up few things.

    Thanks

  • December 4, 2001 at 11:55 am

    #420709

    Thanks for the post and this is more clear.

    It looks like you will want to build dyamic sql, where your stored procedure looks something like:

    Set the variables:

    @key

    @Variable

    @Value

    @DataType of Variable

    @TargeTable

    select @cmd = 'update ' + @targettable + ' set ' + @variable + ' = cast( ' + @value + ' as ' + @datatype + ' where key = ' + @key

    then use exec( @cmd) to run this.

    It will be a little tricky to build the SQL based on whether to insert or update. You could just run both.

    Personally, I'd setup a procedure for each target table (ought ot be easy to script) and then run the appropriate one based on the value of @Target table. Creates lots more procedures, but you move the logic here for insert v update and you can resuse these procedures for other places where you need to insert data. Also, gets away from dynamic sql.

    Steve Jones

    steve@dkranch.net

  • December 4, 2001 at 12:12 pm

    #420710

    Thanks,,,

    I will follow your advice and I think i will get away from INSERTS,,, i would INSERT Key in each Table BEFORE dumping data to the stored procedure. That way i dont have to check for the existance of the Key.

  • December 4, 2001 at 12:18 pm

    #420711

    Steve,,, One quick question,, how would i find the correct data type to use in the CAST ,,, right now i am using my static data dictionary for that,,, but i want to know the better way of finding TargeTable, DataType of any given Variable on the fly

    Thanks

  • December 4, 2001 at 1:34 pm

    #420714

    select * from information_schema.columns

    Steve Jones

    steve@dkranch.net

  • December 4, 2001 at 2:11 pm

    #420721

    Steve,,, when i try that to find Variable 'DOB' -- i get 5 answeres. It pulls out DOB from Tables, Views etc... is there a way to limit the answers only to Tables and nothing else?

    Thanks

  • December 4, 2001 at 2:18 pm

    #420724

    SELECT C.*

    FROM INFORMATION_SCHEMA.Columns C

    JOIN INFORMATION_SCHEMA.Tables T

    ON C.Table_Schema = T.Table_Schema

    AND C.Table_Name = T.Table_Name

    WHERE T.Table_Type = 'BASE TABLE'

    K. Brian Kelley

    bk@warpdrivedesign.org

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • December 5, 2001 at 6:33 am

    #420772

    one more byte of help

    How would I make something like that

    select @cmd = 'update ' + @targettable + ' set ' + @variable + ' = cast( ' + @value + ' as ' + @datatype + ' where key = ' + @key

    I need to make it to have quotes for non-numeric values and no quotes for numeric values??

    Thanks

  • December 5, 2001 at 12:32 pm

    #420792

    You might take a look at the CASE statement to get divergent paths in the SELECT statement. There are some pretty good examples in Books Online... especially using CASE WHEN.

    K. Brian Kelley

    bk@warpdrivedesign.org

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • December 5, 2001 at 1:25 pm

    #420798

    Thanks,, is there a simple way to tell if a given variable/column need quotes or it does not --- or do i have to check all data types as in CASE statement?

  • December 5, 2001 at 1:28 pm

    #420799

    character and date types need quotes.

    Steve Jones

    steve@dkranch.net

  • December 5, 2001 at 1:59 pm

    #420800

    I am trying something like this but getting syntax erros ?

    CASE @DataType

    WHEN 'INT' THEN SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ' + @Patkey

    WHEN 'FLOAT' THEN SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ' + @Patkey

    ELSE SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ' + @Patkey

    END

    EXEC(@cmd)

    Thanks

  • December 5, 2001 at 2:10 pm

    #420801

    Its

    select @cmd = 'update ' + @targettable + ' set ' + @variable + ' = ' +

    case when @datatype = 'int'

    then 'cast( ' + @value + ' as ' + @datatype + ' where key = ' + @key

    case when @datatype = 'char'

    then ' ''cast( ' + @value + ' as ' + @datatype + ') '' where key = ' + @key

    end

    the case must be placed within a select statement, not the other way around.

    Steve Jones

    steve@dkranch.net

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply