Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Stored Procedure_SQL SERVER

Author  Topic 

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 16:14:02
Hi Folks,

I have some doubt in stored procedure, will be greatful if you guys resolve my query.

Here is the details of my query.

1) At 1st I created a table with columns
create table(custname char(10), custsize int, cid int)

2) Then I used Stored Procedure for the existing table.

3) Now I wrote the query, query is this.

create procedure procedure_class(@custname char(10), @custsize int, @cid int)
as
begin
insert into class values(@custname,@custsize,@cid)
END

after writing this query, I executed the query, the result I got is Command(s) completed successfully.

4) Thenafter I tryed to insert a value in class table by using exec syntax

exec procedure_class ('MCSE',15,103) this is the syntax I used to insert a values in to class table

when I executed this exec procedure_class ('MCSE',15,103), Iam getting error message as

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MCSE'.

so please help me in resolving this issue.
Thanks and look forward for your reply.

Thanks,
M.Harinath Gupta.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-16 : 16:18:12
Remove the parenthesis around the values for the exec command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-03-16 : 16:23:40
You don't put parentheses around the parameters. Try this:

Execute procedure_class 'MCSE', 15, 103;

Jeff
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 16:31:31
Hey guys, the given solution is worked out for me

Thanks,
M.Harinath Gupta.
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 16:34:21
I have one more doubt in stored procedure,

how to use update command in existing stored procedure(procedure_class).
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-16 : 16:35:16
Just add it in there. To modify the sproc, use ALTER PROC.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 16:44:02
sorry I did not get you.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-16 : 16:46:48
I'm not clear on what part you didn't get.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 16:53:42
ok will explain you in detail.

1) At 1st I created a table with columns, create table(custname char(10), custsize int, cid int)

2) After creating table with columns I inserted data in to those columns, here is the data.

custname custsize cid

MCA 25 100
MTECH 10 101
BTECH 20 102
BCA NULL NULL

now I what to update NULL values to someother values (custsize and cid) in existing stored procedure(procedure_class).

so please let me know how to use update command for existing stored procedure.

Now I want to update those NULL values using existing stored procedure.
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 17:12:46
guys please reply.
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-16 : 17:28:47
Jeff can you please help me.
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-17 : 05:41:37
quote:
Originally posted by gupta

ok will explain you in detail.

1) At 1st I created a table with columns, create table(custname char(10), custsize int, cid int)

2) After creating table with columns I inserted data in to those columns, here is the data.

custname custsize cid

MCA 25 100
MTECH 10 101
BTECH 20 102
BCA NULL NULL

now I what to update NULL values to someother values (custsize and cid) in existing stored procedure(procedure_class).

so please let me know how to use update command for existing stored procedure.

Now I want to update those NULL values using existing stored procedure.


To update the table why do you need procedure.you can do it with a simple update statement.
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-17 : 08:29:21
Ya we can do like that also as you said,

But I want know and how to use update command in existing stored procedure.

so can anybody help me in resolving this issue.
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-17 : 14:18:26
yes success I got it, how to apply update command in existing stored procedure.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-17 : 17:55:51
Generate the alter script for the procedure (from management studio right click -> Modify
Add in the update statement
Run the script

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -