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 procedure on database create/rename/delete ?

Hello,

I need to execute certain procedures automatically when databases are create, renamed or deleted. I looked around in this forum and Internet but couldn't find anything. This is for SQL 2000.

Well, in SQL 2000 it's not as easy as in SQL 2005 where you could use DDL triggers to achieve this.

You could configure different Alerts in SQL Server 2000 to fire when certain messages are logged to the error log, in this case you could try possibly firing an alert when the error message # 1805 fires ("The CREATE DATABASE process is allocating 1.00 MB on disk 'blah'), but that would only capture newly created DBs. You could use the same method for databases that are attached, restored, deleted, etc., you'd just have to be sure to get error messages that are fired for each 'event'. Once you have the appropriate Alert configured, just set the alert to execute a SQL Agent job when it fires, and setup the Agent job to run the SQL code you want.

If you don't need the code to run exactly when the DB is created/deleted/attached/etc., you could run a trace that captures the appropriate events, then load the trace file nightly and scan for the appropriate event classes, then executing code appropriately as needed.

HTH,

|||

Thanks a lot Chad. This is very helpful. I didn't think about alerts.

sql

EXEC @SQLString with Output Results

Hello, I have been working around this issue, but couldn't yet find any solution.
I have a stored procedure that calls a method to do a certain repetitive work.
In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.
for example, there is an input for a function called "Id"
Inside the function,
if Id = 111
I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.
Now, inside the function, I need to return a value by executing the above @.SQLString as follows:
EXEC @.SQLString
When I need is something like
EXEC @.SQLString, @.Total Output
Return (@.Total)
Are there any ideas ?
regardsProblem Solved
regardssql

excel source not reading newly/appended rows

I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.

Any idea why ?

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.
|||

JayH wrote:

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.

No hidden rows... I m experiencing this with couple of packages having excel as source.

|||Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?|||

Ted Lee - MSFT wrote:

Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?

Yes it works if I do that...

|||

My problem is solved. Under name of the excel sheet in excel source editor two items appear one with original name of the excel file and another one with $ sign appended to excel file name. If I select the one with $ sign it worked for me.

Any idea what both items mean?

Thanks

|||

See the following in Books Online for the Excel Source:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_0evalplan/html/e66349f3-b1b8-4763-89b7-7803541a4d62.htm

"In Excel, a worksheet or range is the equivalent of a table or view. The list of available tables in the Excel Source and Destination editors displays existing worksheets (identified by the $ sign appended to the worksheet name, such as Sheet1$) and named ranges (identified by the absence of the $ sign, such as MyRange). For more information, see the Usage Considerations section."

|||

Thanks Ted...