store procedur for insert value problem

Question

0.00/5 (No votes)

See more:

Hello friends
Below is my store procedure code

SQL

create procedure sp_branchinsert(@branchid int,@schoolname varchar(100),@branchname varchar(50),@createddateandtime datetime)
as
begin
declare @schooid int
select School_id from School where School_name=@schoolname;
set @schooid=School_id;
insert into Branch(Branch_id,School_id,Branch_name,Created_date_time)values(@branchid,@schooid,@branchname,@createddateandtime)
end

The error is invalid column school_id in the set line.what is my task is i want to select school_id from school using store procedure local variable @schoolname.and then this Schoo_id value is need to inserted into the new table branch. if my qurey is wrong, how to write query for the above task.

Posted 25-Jul-12 19:43pm

baskaran chellasamy

Add a Solution

4 solutions

Solution 2

SQL

select School_id from School where School_name=@schoolname;
set @schooid=School_id;

change this lines with,

SQL

select @schooid=School_id from School where School_name=@schoolname;

Happy Coding!
:)

Posted 25-Jul-12 20:06pm

Aarti Meswania

Updated 25-Jul-12 20:07pm

v2

Solution 3

try this

You have to set value for @schoolId from the select query. You cant set it separately.

Incorrect:

SQL

select School_id from School where School_name=@schoolname;
set @schooid=School_id;

Correct one:
select @schooid=School_id from School where School_name=@schoolname

SQL

create procedure sp_branchinsert(@branchid int,@schoolname varchar(100),@branchname varchar(50),@createddateandtime datetime)
as
begin
declare @schooid int
select @schooid=School_id from School where School_name=@schoolname
insert into Branch(Branch_id,School_id,Branch_name,Created_date_time)values(@branchid,@schooid,@branchname,@createddateandtime)
end

Posted 25-Jul-12 20:32pm

Santhosh Kumar Jayaraman

Solution 4

Hi
What About This

SQL

create procedure sp_branchinsert(@branchid int,@schoolname varchar(100),@branchname varchar(50),@createddateandtime datetime)
as
begin
declare @schooid int
set @schooid = isnull(( select School_id from School where School_name=@schoolname),0);
insert into Branch(Branch_id,School_id,Branch_name,Created_date_time)values(@branchid,@schooid,@branchname,@createddateandtime)
end

I Hope That Help You

Posted 25-Jul-12 21:30pm

Mustafa Salman

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-07-25T19:59:00

Solution 1

Try something like this:

SQL

INSERT INTO Branch (schoolId, branchId) VALUES ((SELECT schoolId FROM School WHERE schoolName=@schoolname), @Branchid)

Posted 25-Jul-12 19:59pm

OriginalGriff