insert/update/delete and functions

Question

insert/update/delete and functions

arturm_007

SSC Enthusiast

Points: 191
More actions
October 31, 2013 at 8:15 am

#281875

How to use Insert/Update/Delete statement with SQL Server Functions. I must use function. I can not use the procedure.
https://www.youtube.com/user/learnwithtutorials/playlists

Viewing 4 posts - 1 through 3 (of 3 total)

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

learnwithvideotutorials SSC Veteran Points: 251 More actions · Answer 1

In T-SQL, you cannot modify any data in a function. There is no straightforward way around it. There are some obscure hacks, but I would not use them.

Example:

CREATE FUNCTION sp_bad_function () RETURNS INT AS

BEGIN

DECLARE @sql varchar(4000),

@cmd varchar(4000)

SELECT @sql = 'INSERT INTO table_name (value1, value2)'

SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() +

' -Q "' + @sql + '"'

EXEC master..xp_cmdshell @cmd, 'no_output'

RETURN 1

END

www.learn-with-video-tutorials.com - video tutorials

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you want to change data, you use a procedure. Functions have a requirement of having no side effects.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

Why can't you use a stored procedure or TSQL? What are your restrictions and why are they imposed?

It would appear to me that there is some misunderstanding somewhere.