EXEC SP w/Function

22 Sep 2005 5:09 PM

jroozee@gmail.com

I have a stored proc called "dbo.SetSettlment" and a used defined
function called "dbo.GetSIFByType". The function works great, and so
does the sp. Problem is, I can't get them to work together.

See below, I have three statement. The first two work fine, the last
doesn't. How can I get around this?

EXECUTE dbo.SetSettlment @SettlementAmt = 66
go
SELECT dbo.GetSIFByType(1)
go
EXECUTE dbo.SetSettlment @SettlementAmt = dbo.GetSIFByType(1)

Thanks!
Jason Roozee

22 Sep 2005 5:14 PM

Aaron Bertrand [SQL Server MVP]

You can't pass a function as a parameter.

Try

DECLARE @sAmount INT
SELECT @sAmount = dbo.GetSIFByType(1)
EXEC dbo.SetSettlement @settlementAmt = @sAmount

<jroo***@gmail.com> wrote in message
Show quote

news:1127408994.622820.135290@g49g2000cwa.googlegroups.com...
>I have a stored proc called "dbo.SetSettlment" and a used defined
> function called "dbo.GetSIFByType". The function works great, and so
> does the sp. Problem is, I can't get them to work together.
>
> See below, I have three statement. The first two work fine, the last
> doesn't. How can I get around this?
>
>
> EXECUTE dbo.SetSettlment @SettlementAmt = 66
> go
> SELECT dbo.GetSIFByType(1)
> go
> EXECUTE dbo.SetSettlment @SettlementAmt = dbo.GetSIFByType(1)
>
> Thanks!
> Jason Roozee
>

22 Sep 2005 5:20 PM

jroozee@gmail.com

I would but I am using ADO 2.7 - it doesn't like it when I declare a
var like that.

Jason Roozee

22 Sep 2005 5:34 PM

Yosh

Jason,

Try making 2 seperate calls in ADO.

First, call the function and return the value in a recordset. Then grab the
value from the recordset and cal the stored proc.

Yosh

<jroo***@gmail.com> wrote in message
Show quote

news:1127409622.711723.133570@o13g2000cwo.googlegroups.com...
>I would but I am using ADO 2.7 - it doesn't like it when I declare a
> var like that.
>
> Jason Roozee
>

22 Sep 2005 5:38 PM

jroozee@gmail.com

Of course I could do that - but that's exactly what I am trying to
avoid doing.

Jason

22 Sep 2005 5:43 PM

Yosh

Looks like your gonna have to.

<jroo***@gmail.com> wrote in message
Show quote

news:1127410712.970994.235920@o13g2000cwo.googlegroups.com...
> Of course I could do that - but that's exactly what I am trying to
> avoid doing.
>
> Jason
>

22 Sep 2005 5:47 PM

Aaron Bertrand [SQL Server MVP]

> Of course I could do that - but that's exactly what I am trying to
> avoid doing.

Why? The way you've set it up (a stored procedure that uses the result of a
function), you're pretty much guaranteeing that you need to make two calls.

Why don't you consider passing 1 into the stored procedure, and letting IT
call the function locally.

A

22 Sep 2005 5:16 PM

SQL

try this

declare @intType
select @intType = dbo.GetSIFByType(1)

EXECUTE dbo.SetSettlment @SettlementAmt = @intType
or
EXECUTE dbo.SetSettlment @intType

http://sqlservercode.blogspot.com/

Show quote

"jroo***@gmail.com" wrote:

> I have a stored proc called "dbo.SetSettlment" and a used defined
> function called "dbo.GetSIFByType". The function works great, and so
> does the sp. Problem is, I can't get them to work together.
>
> See below, I have three statement. The first two work fine, the last
> doesn't. How can I get around this?
>
>
> EXECUTE dbo.SetSettlment @SettlementAmt = 66
> go
> SELECT dbo.GetSIFByType(1)
> go
> EXECUTE dbo.SetSettlment @SettlementAmt = dbo.GetSIFByType(1)
>
> Thanks!
> Jason Roozee
>
>

22 Sep 2005 6:24 PM

Trey Walpole

you could change the proc to take the function's parameter instead and
have the proc call the function...

exec dbo.SetSettlement 1

jroo***@gmail.com wrote:

Show quote

>I have a stored proc called "dbo.SetSettlment" and a used defined
>function called "dbo.GetSIFByType". The function works great, and so
>does the sp. Problem is, I can't get them to work together.
>
>See below, I have three statement. The first two work fine, the last
>doesn't. How can I get around this?
>
>
>EXECUTE dbo.SetSettlment @SettlementAmt = 66
>go
>SELECT dbo.GetSIFByType(1)
>go
>EXECUTE dbo.SetSettlment @SettlementAmt = dbo.GetSIFByType(1)
>
>Thanks!
>Jason Roozee
>
>
>