Stuck again! - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Stuck again!

Author

Topic

sqlneofito
Starting Member

16 Posts

Posted - 2010-03-29 : 18:15:30

Hello all,
I posted some questions earlier, maybe I wasnt that clear, but I got it running, It inserts new records into an existing table.
My question is, how can I state in the same procedure to print onto the screen the new inserted row?
Thank you again, from the beginner guy!!

create proc insvalue1
@CurrencyRateDate datetime,
@FromCurrencyCode nvarchar(10),
@ToCurrencyCode nvarchar(10),
@AverageRate money,
@EndOfDayRate money

as
if @AverageRate = '' set @AverageRate = 1.00
if @CurrencyRateDate = '' set @CurrencyRateDate = getdate()

if ( select count(*) from Sales.Currency where CurrencyCode = @FromCurrencyCode ) = 0
begin
print 'Wrong "From" currency Code'
return
end

if ( select count(*) from Sales.Currency where CurrencyCode = @ToCurrencyCode ) = 0
begin
print 'Wrong "To" currency Code'
return
end

insert into Sales.CurrencyRate(CurrencyRateDate, FromCurrencyCode, ToCurrencyCode, AverageRate, EndOfDayRate)
values (@CurrencyRateDate, @FromCurrencyCode, @ToCurrencyCode, @AverageRate, @EndOfDayRate)

exec insvalue1 @CurrencyRateDate = '', @FromCurrencyCode = 'AUD', @ToCurrencyCode = 'USD', @AverageRate = 1.5, @EndOfDayRate = 3.2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 18:18:33

Can't you just PRINT the variables or use a SELECT?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlneofito
Starting Member

16 Posts

Posted - 2010-03-29 : 18:25:36

Yeah I could do that, sorry I wasn't the clear again, the request I have says, the autogenerated CurrencyRateID field from theINSERT command should be returned back to the calling location and the new record shoul be output onto the the screen using a SELECT statement.
Thank you again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 18:32:59

After the INSERT, you'll need to capture the identity value.

DECLARE @i int

INSERT...

SET @i = SCOPE_IDENTITY() --your instructor might want you to use @@IDENTITY here, but that's not a good idea

SELECT @i

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlneofito
Starting Member

16 Posts

Posted - 2010-03-29 : 18:38:27

Thank you very much Tara, you are a real goddess!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 18:39:49

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources