December 2, 2008 at 7:57 pm
Hi all,
I am new to SQL Server and i am now looking into stored procedures, when i started some test scripts, i got some doubts
I am writing a procedure like inserting the values for a table with 3 parameters
1) for the first parameter i need to get the count value and increment it by 1
2) for the second and third parameter i will give the values while executing the stored procedure...
December 2, 2008 at 9:31 pm
It's not clear what you are trying to do or what you want. Please give some examples.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 2, 2008 at 11:33 pm
I am trying to write a stored procedure to insert values into a table which is already created.
my code is
Create procedure name
@value varchar(50),
@value2 varchar(150)
as
begin
declare @myValue int
Select @myValue = 'Select count(*)+1 from myTable'
Insert into myTable values( @myValue, @value, @value2 )
end
i am getting error at Select statement line
December 3, 2008 at 12:04 am
rajesh_281984 (12/2/2008)
I am trying to write a stored procedure to insert values into a table which is already created.my code is
Create procedure name
@value varchar(50),
@value2 varchar(150)
as
begin
declare @myValue int
Select @myValue = 'Select count(*)+1 from myTable'
Insert into myTable values( @myValue, @value, @value2 )
end
i am getting error at Select statement line
this is the correct one
Select @myValue = count(*)+1 from myTable
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 3, 2008 at 12:05 am
Create procedure name
@value varchar(50),
@value2 varchar(150)
as
begin
declare @myValue int
SET @myValue=0
Select @myValue = isnull(count(*),0) + 1 from myTable
Insert into myTable values( @myValue, @value, @value2 )
end
December 3, 2008 at 12:06 am
Create procedure name
@value varchar(50),
@value2 varchar(150)
as
begin
declare @myValue int
--wrong statement
--Select @myValue = 'Select count(*)+1 from myTable'
Select @myValue = count(*)+1 from myTable
Insert into myTable values( @myValue, @value, @value2 )
end
Here is the correct code. Try the above code it should work.
The select statement which you have used was wrong. refer the above code.
If you still get any problem let me know.
December 3, 2008 at 12:54 am
I'd suggest doing it as one statement. If you do the count first and then the insert, there's a chance that another insert could occur in between and make the count wrong.
This is probably going to cause locking problems if multiple users are running this at the same time. Is this for a sequential counter column?
CREATE PROCEDURE procname
@value varchar(50),
@value2 varchar(150)
as
INSERT INTO myTable (< Column list goes here > )
SELECT COUNT(*)+1, @value, @value2 FROM myTable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2008 at 1:23 am
CREATE PROCEDURE procname
@value varchar(50),
@value2 varchar(150)
as
INSERT INTO myTable ( )
SELECT COUNT(*)+1, @value, @value2 FROM myTable
The above code which was posted by Gail Shaw is better than what I had posted earlier.
I did only syntax correction... but this is better.
December 3, 2008 at 1:25 am
Thanks to all of you guys for giving me good information and the code is working fine now. Can u guys suggest some stored procedure samples that i can try on my own which will help me in familiarizing stored procedure
December 3, 2008 at 1:35 am
I can suggest you to search on the net or in sqlservercentral for store procedures. You will get lot of codes and sample. This will also help you to find where and how you can search for code and solutions. Just search yourself.
December 3, 2008 at 1:59 am
rajesh_281984 is a lucky guy, as gets lots of updates regarding his problem.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 3, 2008 at 4:52 am
rajesh_281984 (12/3/2008)
Thanks to all of you guys for giving me good information and the code is working fine now. Can u guys suggest some stored procedure samples that i can try on my own which will help me in familiarizing stored procedure
Yes, but you should have answered Gilamonster's question, because it is important.
What you are doing here will technically work, but is a bad approach because it will start to have locking and concurrency problems when multiple people try to do it at the same time. A better answer would be to change the counter column of MyTable to be an Identifier. This is ultimately faster, easier and less error-prone.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 3, 2008 at 5:53 am
rajesh_281984 (12/3/2008)
Thanks to all of you guys for giving me good information and the code is working fine now. Can u guys suggest some stored procedure samples that i can try on my own which will help me in familiarizing stored procedure
I'd start with taking a look through AdventureWorks. There are only a few procedures and functions, but they're reasonably well written. After that, time to hit the books. There are two I can't recommend enough, both by Itzik Ben-Gan: "Inside SQL Server TSQL Querying" and, possibly better for you, even though it's for 2008, "T-SQL Fundamentals."
After that... reading and posting here at SSC is one the best ways I've found to learn.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2008 at 8:16 am
if you are at a shop with applications written and implemented look at the stored procedures that were written for them
the downside to that is that you may pick up some bad habits if your workplace has no standards
you will, however, get a feel at how complicated stored procedures can be
December 4, 2008 at 8:28 am
Learning about stored procs.
I am greatly simplifying things here: you could consider stored procedure as containers where can you store SQL code in the database to let the database server host do the work and only return the results you need through the network.
So your question should be taken as how to code in sql. However, the sql language is much more simpler to learn than human languages.
The trick in sql is not to just know how to use the very limited set of action verbs such as SELECT, UPDATE, etc. The trick is to understand the environment and do a proper design before plunging ahead and starting coding immediately.
For instance, sql is intended to deal with SETS of records and yet there still people who write code that loops on a single record at a time and repeat the same process on the next record and so on. This approach does not respect the whole concept of sql which is designed to retrieve/insert/delete NUMEROUS records at the same time.
(Yes there are exceptional cases where looping is necessary, but this should be a last-resort solution instead of the starting point.)
For my part, I find that any article posted by Jeff Moden is extremely valuable. It generally starts with a quick and dirty piece of code that works and then proceeds with showing how to obtain dramatic performance gains (speed). Robyn Page and Phil Factor are also on my list. (I hope others whom I have left out will not be sore over it.)
And yes, SQL Server Central is a great place to be if you really want to learn and are willing to spend time studying what appears at first to be difficult-to-understand code. It will not be very long before your efforts are rewarded and that you find yourself less and less intimidated by the code, even though on occasions you will go OOOH ! and AAAAH! over how clever some the solutions proposed are.
Have fun.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply