will it work?

Question

Post reply

will it work?

niha.736

Ten Centuries

Points: 1210
More actions
January 16, 2012 at 6:10 am

#255056

Hi all,
If a table contain no records and if I write the statement as insert in functions and I also written it in the stored procedure, so, what will be the result for both?
can anybody explain this?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Robert Murphy UK1 Default port Points: 1485 More actions · Answer 1

I dont understand your question. Can you rephrase please?

Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

niha.736 (1/16/2012)
Hi all,
If a table contain no records and if I write the statement as insert in functions and I also written it in the stored procedure, so, what will be the result for both?
can anybody explain this?

User-defined functions cannot be used to perform actions that modify the database state.

http://msdn.microsoft.com/en-us/library/ms186755.aspx

An error whilst attempting to create the function - unless it's a table variable.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 3

niha.736 (1/16/2012)
Hi all,
If a table contain no records and if I write the statement as insert in functions and I also written it in the stored procedure, so, what will be the result for both?
can anybody explain this?

If I'm reading your question correctly, then NO.

just to be clear, in SQL , a function can only return data. it cannot INSERT or UPDATE any data.

functions are limited to not allowing " "side affecting operations", which includes inserts and updates.

A procedure can do it, as you identified.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

niha.736 Ten Centuries Points: 1210 More actions · Answer 4

niha.736

Ten Centuries

Points: 1210

January 16, 2012 at 6:37 am

#1434041

:Wow: i got my clarification, many thanks mr.Lowell

Lowell SSC Guru Points: 323518 More actions · Answer 5

niha.736 (1/16/2012)
:Wow: i got my clarification, many thanks mr.Lowell

No problem, glad i could help;

If you are coming from a programming background, Functions and Subs are interchangable, and the only difference is whether you returna value or not.

In TSQL, the are a special, limited type of procedure.

I think Chris@Home provided a good detail link if you need to read up on it a bit more.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!