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

EXCEPTION_ACCESS_VIOLATION when I want to update

Hi,

I want to update a db-column with text-format.
The order is simple: Update tblatt set textattvalue = '...' where Attid= ...

... stands for a text and an ID.

I get the exception I wrote in the subject and I don't know, what I can do.
On 3 other Databases, which I create with the same script(=they are all equal), everything works fine.

I hope anyone has an idea.if the transaction is stuck in the log the table will be locked and then you are stuck! Does this still happen if you stop and re-start the sevices?

(Of course you could always look at the table locks :rolleyes: )|||I had restarted the Services before, but it didn't change anything. I deleted the database (only this one, because there are 2 or 3 other important dbs) and recreated it, but nothing changed.

EXCEPTION_ACCESS_VIOLATION using ntext

I get the following error when i execute the SQL below.
It works if the sp parameter is text rather than ntext or if the update uses
the full key.
(Windows 2K sp4, sql 2k sp3 no hot fixes)
================================================== === BugCheck Dump
================================================== ===
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 11:23:50 09/21/04.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 80%
Total Physical = 1021 MB
Available Physical = 196 MB
Total Page File = 1326 MB
Available Page File = 645 MB
Total Virtual = 2047 MB
Available Virtual = 948 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0170.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 09/21/04 11:23:50 spid 54
*
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 82 bytes -
* exec spStoreCompanyTest 1, 2, 3, N'test'
================Test.sql=========================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblCompanyTest]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spStoreCompanyTest]
GO
CREATE TABLE [dbo].[tblCompanyTest] (
[source] [int] NOT NULL ,
[feed] [int] NOT NULL ,
[code] [int] NOT NULL ,
[sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
(
[source],
[feed],
[code]
) ON [PRIMARY]
GO
CREATE PROCEDURE dbo.spStoreCompanyTest
@.source int,
@.feed int,
@.code int,
@.sourceData ntext --text
AS
IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND feed
= @.feed AND code = @.code)
BEGIN
INSERT INTO tblCompanyTest
VALUES(@.source, @.feed, @.code, @.sourceData)
END
ELSE
BEGIN
UPDATE tblCompanyTest
SET sourceData = @.sourceData
WHERE source = @.source
AND feed = @.feed
--and code = @.code
END
RETURN
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
> I get the following error when i execute the SQL below.
> It works if the sp parameter is text rather than ntext or if the update
uses
> the full key.
> (Windows 2K sp4, sql 2k sp3 no hot fixes)
> ================================================== === BugCheck Dump

> ================================================== ===

> This file is generated by Microsoft SQL Server 8.00.760

> upon detection of fatal unexpected error. Please return this file,

> the query or program that produced the bugcheck, the database and

> the error log, and any other pertinent information with a Service
Request.
>

> Computer type is AT/AT COMPATIBLE.

> Current time is 11:23:50 09/21/04.

> 1 Intel x86 level 15, 3 Mhz processor(s).

> Windows NT 5.0 Build 2195 CSD Service Pack 4.

>
> Memory
> MemoryLoad = 80%
> Total Physical = 1021 MB
> Available Physical = 196 MB
> Total Page File = 1326 MB
> Available Page File = 645 MB
> Total Virtual = 2047 MB
> Available Virtual = 948 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0170.txt

> *
>
************************************************** **************************
*
> **

> *

> * BEGIN STACK DUMP:

> * 09/21/04 11:23:50 spid 54

> *

> * Exception Address = 00000000

> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

> * Access Violation occurred reading address 00000000

> * Input Buffer 82 bytes -

> * exec spStoreCompanyTest 1, 2, 3, N'test'

>
> ================Test.sql=========================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id,
N'IsUserTable') =
> 1)
> drop table [dbo].[tblCompanyTest]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[spStoreCompanyTest]
> GO
> CREATE TABLE [dbo].[tblCompanyTest] (
> [source] [int] NOT NULL ,
> [feed] [int] NOT NULL ,
> [code] [int] NOT NULL ,
> [sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
> (
> [source],
> [feed],
> [code]
> ) ON [PRIMARY]
> GO
>
> CREATE PROCEDURE dbo.spStoreCompanyTest
> @.source int,
> @.feed int,
> @.code int,
> @.sourceData ntext --text
> AS
> IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND
feed
> = @.feed AND code = @.code)
> BEGIN
> INSERT INTO tblCompanyTest
> VALUES(@.source, @.feed, @.code, @.sourceData)
> END
> ELSE
> BEGIN
> UPDATE tblCompanyTest
> SET sourceData = @.sourceData
> WHERE source = @.source
> AND feed = @.feed
> --and code = @.code
> END
> RETURN
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
>
Paste the whole sqldmpxxxx.txt and I'll give it a go.
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.