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

Question

Post reply

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

john_chrome

Mr or Mrs. 500

Points: 504
More actions
May 1, 2009 at 4:51 pm

#208780

Insert 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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post some sample data and what the desired result is?

Wouldn't something like this work:

CREATE TABLE #customers

(

customer_Id INT IDENTITY(1,1),

customer_name VARCHAR(100)

)

INSERT INTO #customers

(

customer_name

)

SELECT

'Smith, Ryan'

UNION ALL

SELECT

'Doe, John'

UNION ALL

SELECT

'Doe, Jane'

SELECT

customer_id,

customer_name,

LEFT(customer_name, CHARINDEX(',', customer_name)-1) AS last_name,

LTRIM(RIGHT(customer_name, LEN(customer_name) - CHARINDEX(',', customer_name))) AS first_name

FROM

#customers AS C

Drop Table #customers

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I'm slightly confused as to what is being asked. In addition to data and expected results, the DDL for the tables involved and your current code would also be helpful. Also, when providing the data, be sure it is in a readily consumable format. For help with how to posts questions to get the best answers, both Jack and I have a very useful article referenced in our signature blocks, you may want to take some time reading the article.

john_chrome Mr or Mrs. 500 Points: 504 More actions · Answer 3

Thanks a lot jack, for that invaluable post, i tweaked around a bit but that was exactly what i was trying to achieve.

Thank you for posting the idea without making it over complicated asking for sample code,data so on i mean i understand the need for it but if there is some head on then it would be great instead of just asking another question.

Please i am not trying to fault anyone, thank you everyone for trying to help.

It was kind of lynn,Jack for replying.

Thank you.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Well, if you really want to know, I think you should read this The Flip Side.

It will give you a very good idea of why you should provide as much information as possible up front.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

John,

I'm glad the code I provided was helpful, but I did ask for more information the same as Lynn

Can you post some sample data and what the desired result is?

The difference is that I thought your problem simple enough to make a wild guess before you posted more information. I could have posted a more complete solution had you done as Lynn and I requested. Please read the links Lynn has provided for future reference.

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

john_chrome Mr or Mrs. 500 Points: 504 More actions · Answer 6

Lynn and Jack,I deeply apologize if by any means i sounded rude, it was not my intention, i am very greatful for the replies and for the time you took to read the post.

I just was explaining myself i never tried to say that anyone was wrong, i hope you see the better side.

Thank you.