Exec sproc in a update function

Folks

Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity ....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.

update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID

FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.

ThanxUse (create) function instead of sp in this case.|||Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx|||Originally posted by kir441
Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx

What about this draft?

drop table test
drop table test2
create table test(id int)
create table test2(id int, code varchar(10))
go
insert test values(1)
insert test values(2)
insert test values(3)
insert test2 values(1,'a')
insert test2 values(2,'b')
insert test2 values(3,'c')
go
CREATE FUNCTION getit(@.id int)
RETURNS varchar
AS
BEGIN
declare @.ret varchar(10)
select @.ret=code from test2 where id=@.id
RETURN @.ret
END
GO
select *,dbo.getit(id)
from testsql

EXEC SP w/Function

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 RoozeeYou can't pass a function as a parameter.
Try
DECLARE @.sAmount INT
SELECT @.sAmount = dbo.GetSIFByType(1)
EXEC dbo.SetSettlement @.settlementAmt = @.sAmount
<jroozee@.gmail.com> wrote in message
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
>|||try this
declare @.intType
select @.intType = dbo.GetSIFByType(1)
EXECUTE dbo.SetSettlment @.SettlementAmt = @.intType
or
EXECUTE dbo.SetSettlment @.intType
http://sqlservercode.blogspot.com/
"jroozee@.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
>|||I would but I am using ADO 2.7 - it doesn't like it when I declare a
var like that.
Jason Roozee|||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
<jroozee@.gmail.com> wrote in message
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
>|||Of course I could do that - but that's exactly what I am trying to
avoid doing.
Jason|||Looks like your gonna have to.
<jroozee@.gmail.com> wrote in message
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
>|||> 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|||you could change the proc to take the function's parameter instead and
have the proc call the function...
exec dbo.SetSettlement 1
jroozee@.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
>
>

exec in user-defined function

Hi,

How can I do dynamical exec to query in user-defined function? At the end I need to return the result.

Thank's
Alexei

You are not allowed. That's by design. Refer to BOL -> CREATE FUNCTION for more details.

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florenceSQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florence
SQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure

sql

EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure

exec in a function

I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks muchI don't think you can do Dynamic SQL in a user defined fuction.

Tim S

Exclude Words

I have built a functioning full text search tool on my companies
extranet site. I use the containstable function. I was curious what
the syntax or process would be to do a "without these words" box to
allow users to filter their search results better.
Thanks,
Daniel Hirsch
Daniel,
You can use the NOT keyword in either your CONTAINS or CONTAINSTABLE query,
however, there are some restrictions on how it can be used, from the BOL
title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains
search conditions.
When <contains_search_condition> contains parenthesized groups, these
parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using these
logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not
allowed.
NOT cannot be specified before the first term (for example, CONTAINS
(mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can
therefore be applied in any order.
Below are two examples CONTAINSTABLE:
use pubs
-- returns 2 rows when NOT, not included and 0 rows when NOT is included
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
-- another example of using Mutiple columns (same table) with a NOT
condition:
SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city,
KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY]
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dan Hirsch" <daniel.hirsch@.mckesson.com> wrote in message
news:1114200575.445065.76320@.f14g2000cwb.googlegro ups.com...
> I have built a functioning full text search tool on my companies
> extranet site. I use the containstable function. I was curious what
> the syntax or process would be to do a "without these words" box to
> allow users to filter their search results better.
> Thanks,
> Daniel Hirsch
>
|||Thanks Allot John. the NOT is exactly what I was looking for. I
figured there was syntax like that, but I wasn't sure exactly.
Thanks Again,
Daniel Hirsch
sql

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris