Post reply

Alternatives to temporary table which takes Procedure Return and a primary key and a primary key from a table

  • May 2, 2009 at 7:27 am

    #131858

    nsert into customer_table(customer_id,first_name,last_name,..

    select (customer_id,there is only customer_name which needs to be passed to the procedure to obtain first_name , last_name

    but

    we cant call a procedure in a select statement

    It would be great if somebody can suggest alternatives to using a row by row fetch

    Either this procedure can be converted to function or if there is any other opinion

    I have a situation where in say the input is

    customer name, currently using a procedure to break it into first name and last name by a procedure which takes in customer_name and first_name out,last_name out

    so This is done something like

    declare @count int;

    declare @first_name varchar(100);

    declare @last_name varchar(100);

    declare @i int;

    create #temp_table(@@identity as iden,cust_id int,first_name varchar(100),last_name varchar(100),customer_name);

    insert into #temp_table

    select customer_id,null,null,customer_name from load_table;

    set @i =1;

    set @count=select count(customer_id) from load_table;

    while(@i<=@count)
    {
    set @customer_name=select customer_name from load_table where @@identity=@i;
    update #temp_table set first_name,last_name exec customer_name_split @customer_name @customer_name,@first_name out,@last_name where iden = @i;
    }

    There are many mistakes but basically my idea is

    create a temporary table(@@identity,customer_id,first_name,last_name) and load it initially with values from the load_table then again perform a row by row insert where we execute the procedure and insert values into the table using the customer_id or identity.

    I could convert the procedure itself into a function and use case to extract first_name and last_name into the respective fields of the customer table.

    But the procedure is linked to various places and the changes need to be done in all those places.

    Thank you

  • May 4, 2009 at 9:42 am

    #988161

    Duplicate post, please reply here

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 1 (of 1 total)

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