How to execute stored proc during update stmt? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to execute stored proc during update stmt?

Author

Topic

hearnie
Starting Member

49 Posts

Posted - 2005-04-14 : 07:58:31

Hi there guys & gals,

Im just wondering,
I have a statement like the following

Insert Real1 (<colst>)
SElect a.<cols> (I WANT TO CALL SP HERE)from temp1 a
left join Real1 b ON a.<keys> = b.<keys>
Where b.<key> is NULL

where I have indicated, for each value I insert into the table one of the values is a value returned from a stored procedure. How do make the statement above so that the stored procedure gets called on each insert??

any help greatly appreciated.

H.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-14 : 09:51:05

call a function instead

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-14 : 09:53:32

declare @foo nvarchar(100)

exec @foo = <YourSP>
declare @bar varchar(500)
select @bar =
'Insert Real1 (<colst>) SElect ' + @foo + ' from temp1 a left join Real1 b ON a.<keys> = b.<keys> Where b.<key> is NULL'

hearnie
Starting Member

49 Posts

Posted - 2005-04-14 : 10:10:25

I think you misunderstand slightly, the SP needs to get its INPUT value from the column value of the Insert....Select

So as such

Insert Mytable(field1,field2,field3)
select tmpT.field1, tmpT.field2, tmpT.field3***
from MyTable2 tmpT
LEFT JOIN MyTable tmpT2 ON tmpT.field1 = tmpT2.field1

***(it is here, field 3 for each matching row of the join needs to be passed to a SP and the return value of the SP needs to be inserted rather then tmpT.field3)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-14 : 10:27:13

did you tried this???

quote:
Originally posted by tuenty

call a function instead

I have a function to convert a strange date with format CYYMMDD to datetime where C should have 1 for 19YY and 2 for 20YY

CREATE FUNCTION conv2ValidDate (@dateString char(7))
RETURNS datetime AS  
--Convierte la fecha que nos envían a una fecha valida
BEGIN 
Return(case 
    When isDate(right(@dateString,6))=1 then
        Case left(@dateString,1)
        When '1' then
            Convert(datetime, '19' + right(@dateString,6))
        When '2' then
            Convert(datetime, '20' + right(@dateString,6))
        Else
            '1/1/1900'
        End
    else
        '1/1/1900'
    End)
END

I use this funtion for inserts
something like...

INSERT INTO tblworkingTable(birthdate, creationDate)
    SELECT myDB.dbo.conv2ValidDate(birthdate), getdate()
    FROM tblstagingTable
    WHERE @key1=key1

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-14 : 10:47:53

Ah, i now see what you mean. I would then suggest using a function, as the others have said.

hearnie
Starting Member

49 Posts

Posted - 2005-04-14 : 11:00:10

Thank you both very much for your time and effort.
I now see what you meant about the function.

Merci mon ami

H.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources