August 20, 2013 at 5:48 am
Hi All,
I have created a function (Func_GetSeqValue) which is creating a Sequential number. Here is my Query
Create Function GetSeqValue (@limit int)
returns @TableVar table (GetSequecValue Varchar(20))
as
begin
declare @var int = 1
declare @var1 varchar (20)
while (@var < @limit)
begin
if @var < 10
set @var1= 'ABCD' + '00' +CAST(@var as varchar)
else if
@var < 100
set @var1= 'ABCD' + '0' +CAST(@var as varchar)
else
set @var1= 'ABCD' + CAST(@var as varchar)
insert into @TableVar values (@var1)
set @var = @var+1
end
return
end
When I am executing this function with a parameter ti is working fine. Now I have created another table (InsertedEmpdetails)
create table InsertedEmpdetails
(Emp_id Varchar,
Emp_Name varchar(30),
Emp_Age tinyint,
Emp_Course varchar (15),
Emp_Gender varchar,
Emp_Sal Float,
)
Now I want to insert the data in the table using an SP. Here is my SP
CREATE PROC usp_InsertDetails
(
@id varchar (20),
@name varchar(30),
@age tinyint,
@course char(21),
@gender char(1),
@empSal Float
)
AS
BEGIN
INSERT INTO
InsertedEmpdetails
VALUES(@id, @name, @age, @course, @gender, @empSal)
END
Now For ID Field I want to insert the data from my Function (Func_GetSeqValue).
How will I do that?
Please help!
August 20, 2013 at 6:09 am
You need to join the output of the function with a selection of the variables. Keep in mind that the variables will be the same for every row returned by the function.
CREATE PROC usp_InsertDetails
(
@id int,-- change to INT as input for function
@name varchar(30),
@age tinyint,
@course char(21),
@gender char(1),
@empSal Float
)
AS
BEGIN
INSERT INTO
InsertedEmpdetails
VALUES(id, name, age, course, gender, empSal)
select *
from GetSeqValue (@id)
cross join
(SELECT @name as 'name'
, @age as 'age'
, @course as 'course'
, @gender as 'gender'
, @empSal as 'empsal') sub
END
August 20, 2013 at 6:23 am
Here what I see that is wrong with your process.
1. Your function will return multiple rows with each invocation of the function
2. Your stored procedure is designed to enter a singe row (or record) at a time.
3. How do you control the value from the function that will be used by the sp to insert a single row?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply