Create stored procedure

Question

Create stored procedure

Viewing 15 posts - 16 through 30 (of 33 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 1

Scott Arendt (3/16/2011)
ronmoses (3/16/2011)
paul s-306273 (3/16/2011)
I don't really see the value of this question...
The nearly 40% of respondents who got it wrong learned something today. That alone seems valuable enough to me, especially since that's the entire point of QotD.
Just because something is learned, does not necessarily mean that it has value. It only has value if it is useful. I would say that this is more interesting than useful.

It's valuable if you learn what not to do or what might cause a problem. It's not always what is positively learned. sometimes it's what is negative that you should avoid.

gregg_dn SSCommitted Points: 1596 More actions · Answer 2

gregg_dn

SSCommitted

Points: 1596

March 16, 2011 at 9:27 am

#1299445

Interesting.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Rose Bud Hall of Fame Points: 3147 More actions · Answer 4

There's always value in understanding the exceptions.

The young man knows the rules, but the old man knows the exceptions. ~Oliver Wendell Holmes Sr.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

Just don't forget to put the GO at the end, otherwise when you put it into an upgrade script you never know what you are going to get.

UMG Developer SSChampion Points: 13482 More actions · Answer 6

UMG Developer

SSChampion

Points: 13482

March 16, 2011 at 7:30 pm

#1299758

Thanks for the interesting question.

ziangij SSCrazy Eights Points: 8066 More actions · Answer 7

Another "funny" thing is that BEGIN...END does not define the body of the procedure. You can have multiple BEGIN...END in the body of a procedure, like this:
create proc test
as
begin
select 1
end
begin
select 2
endExecuting "test" will return two recordsets.
A procedure can have an empty body, but BEGIN...END cannot be empty.

This one is pretty interesting. I really didn't know that by adding Begin, End (twice or more), we can return 2 recordsets. But I dont think this type of proc can ever be used.

Has anybody tried using sp_spaceused (returns 2 recordsets) in a select query ? Its just not possible.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tejaswini.patil SSCommitted Points: 1812 More actions · Answer 9

I agree with Ron.

This question made us look into the basics of naming conventions.

Thanks for the question.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 10

Here's where it's useful. If you want an alternative to the DROP and CREATE method of maintaining stored procedure scripts - which would overwrite metadata about when a procedure was first created - you can use this:

IF OBJECT_ID ( '[dbo].[NewProcedure]', 'P' ) IS NULL

BEGIN

EXEC ('CREATE PROCEDURE [dbo].[NewProcedure] AS')

END

GO

ALTER PROCEDURE [dbo].[NewProcedure]

-- Procedure code goes here

Just an idea.

amit_adarsh Ten Centuries Points: 1273 More actions · Answer 11

You can not use any special character as a first later of procedure name except _ .

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 12

amit_adarsh (3/17/2011)
You can not use any special character as a first later of procedure name except _ .

That is because they are not supported by the naming conventions for identifiers. Please go back to the 1st page of the discussion where I have explained why the SQL Server allows you to create this particular stored procedure.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Toreador SSChampion Points: 11365 More actions · Answer 13

ziangij (3/16/2011)I really didn't know that by adding Begin, End (twice or more), we can return 2 recordsets.

You don't need multiple begin/end pairs to return 2 recordsets.

create proc test

as

begin

select 1

select 2

end

or

create proc test

as

select 1

select 2

will do just as well.

We make regular use of procedures returning multiple recordsets. Using ADO you just use NextRecordSet to get at the data.

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 14

Bala' (3/16/2011)
Its not really adding any value..

There is actually quite a lot of value. When creating a system in a team environment, it allows the db developer to create all the procedure stubs initiallly even though they may not return any values. In turn, this allows the front end coders get on with coding the data access layer to an extent. The db developer can then continue addig the function bodies without other developers screaming at him to hurry up.

You may not see the value, but it is there for somebody.

SanDroid SSChampion Points: 10068 More actions · Answer 15

rjohal-500813 (3/17/2011)
Bala' (3/16/2011)
Its not really adding any value..
You may not see the value, but it is there for somebody.

😎 I would have to agree with that. 😉 Somebody out there sees all kinds of value in SQL developers and DBA's know the minimum amount of code required to create Procedures and Functions. Most likely why it is on the Certification exams.