Execute a stored procedure inside a user defined function

Question

Execute a stored procedure inside a user defined function

Viewing 15 posts - 46 through 60 (of 63 total)

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

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 1

Would you like me to call you on the phone?

You are more than welcome to call me 949.973.5234

either way, I think we can save some time this way and post our finding from the phone conversation back into the forum later.

what do you think?

thx

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 2

That's alright if you can not do the phone deal... well, I guess this is going to be a T-Shirt winner after all and after I post the complete scenario and + solution if there will be one soon.. 🙂

Cheers,
John Esraelo

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

John Esraelo (5/11/2009)
It looks good, but there is no way that I can get away with multiple query structure trying to run in a view. This will require some dynamic string building process + a few "declare" statements.. and that is not possible in a view structure.. As "view" has a select statement and occasionally we can squeeze a "case" statement or "IF" statement in there.. but nothing like we are doing.. do you agree on that... please correct me as I am in need of a solutions badddddd..
The thing is that views can not call stored procedures either, and, that is a serious limitation in late-binding world.

Please see your original thread, I just posted a question there regarding your code.

prakash.kmr Valued Member Points: 61 More actions · Answer 4

Hi

I also agree we can execute store procedure within the user define function but store procedure do not have any insert,delete,update or we can say that the DDL command

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 5

You sure can use a function to action a DELETE.

In this example, the function TestTempFunction calls the stored procedure TestTempProc and deletes 80 rows.

USE tempdb

GO

CREATE TABLE TestTemp (IDINT INT IDENTITY(1,1))

GO

INSERT TestTemp DEFAULT VALUES

GO 100

CREATE PROC TestTempProc @IDINT INT AS

DELETE TestTemp WHERE IDINT > @IDINT

SELECT IDINT FROM TestTemp

GO

SELECT * FROM TestTemp

GO

CREATE FUNCTION TestTempFunction()

RETURNS TABLE

AS

RETURN

SELECT * FROM OPENROWSET('SQLNCLI','SERVER=.;TRUSTED_CONNECTION=YES','SET NOCOUNT ON SET FMTONLY OFF EXEC tempdb..TestTempProc 20')

GO

SELECT * FROM TestTempFunction()

GO

SELECT * FROM TestTemp

GO

himanshufetagra SSC Enthusiast Points: 105 More actions · Answer 6

Only functions and extended stored procedures can be executed from within a function.You cannot execute user defined sotred procedures from a function.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 7

Hi ND

Thanks for sharing this with us. I was always under impression that you can never call a proc from a function, and I was wrong.

For those who want to try it out,here is the code snippet:

CREATE TABLE DBO.EMPLOYEE

(

EMPLOYEEID INT IDENTITY(1,1),

FIRSTNAME VARCHAR(50) NOT NULL,

LASTNAME VARCHAR(50) NOT NULL,

DATE_HIRED DATETIME NOT NULL,

IS_ACTIVE BIT NOT NULL DEFAULT 1,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID),

CONSTRAINT UQ_EMPLOYEE_LASTNAME UNIQUE (LASTNAME, FIRSTNAME)

)

GO

create proc myprc

as

INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)

SELECT 'George3', 'Washington', '1999-03-15'

sqlcmd -E -S GLOSAPPQ12 -d test -Q "EXEC dbo.myprc"

create function dbo.myfun()

returns int

as

begin

declare @i int

exec master..xp_cmdshell 'c:\runproc.bat'

set @i=2

return @i

end

select dbo.myfun()

--code for batch file. save txt file as runproc.bat with this code

sqlcmd -E -S servername -d test -Q "EXEC dbo.myprc"

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 8

I didnt see that OPENQUERY is another way......thanks fo rthat too...

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

vivek_arora83 54049 Newbie Points: 7 More actions · Answer 9

can you please decribe it more or more example on this asctually i am not much clear with this how we can do this. As i know we can't use the exec command in the function so please describe it.

Thanks

Vivek Arora

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 10

Vivek

You can use exec in funtion but only for extended procs. Here I have created a batch file which I executed using:

exec master..xp_cmdshell 'c:\runproc.bat'

runproc.bat is the batch file here...

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

nrajank SSC Enthusiast Points: 112 More actions · Answer 11

hi.. this s naga..

i cant able to put insert statement with in the function. how can i do that? please tell me with example..

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 12

at top level, you can say Insert,update,delete are not allowed in funtion.

Use stored proc instead.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

nrajank SSC Enthusiast Points: 112 More actions · Answer 13

hi kumar..

can you give some example for execute select statement wihtin the function.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 14

Here is an example:

ALTER FUNCTION [dbo].[fn_abc]

(

@a int = 0

, @b-2 int = -1

)

RETURNS int

AS

BEGIN

DECLARE @Result int

SELECT @Result = col1

FROM dbo.tab1

WHERE Col2 = @a

AND Col3=@b

-- Return the result of the function

RETURN @Result

END

You should learn to use google for basic concepts clarifications.....no offence meant

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

nrajank SSC Enthusiast Points: 112 More actions · Answer 15

dear kumar..

i knew the basics in SQL. what i m asking is some example for executing sql query which is passed thru another function like follows:

select dbo.test('select colname from table')

so this test() function pass the select query as string to another function and that second function has to return the result for the select statement.

Is it possible?