Stored procedure

[Nsan]

I cannot get the insert statement to run. I get a return value and the insert statement does not get executed. What am I doing wrong.


use database
go

create procedure dbo.ins

@tsename varchar(50),
@techid uniqueidentifier OUTPUT,
@casenum varchar(10),
@productid uniqueidentifier OUTPUT,
@entdate datetime,
@productname varchar(50)

as

select @techid = techid from techs where tsename = @tsename
select @productid = productid from products where productname = @productname

insert INTO cases (casenum, techid, productid, entdate)
values(@casenum, @techid, @productid, @entdate)

THX

 

[hmckillop]

looks ok to me.
Try adding the below changes

use database
go

create procedure dbo.ins

@tsename varchar(50),
@techid uniqueidentifier OUTPUT,
@casenum varchar(10),
@productid uniqueidentifier OUTPUT,
@entdate datetime,
@productname varchar(50)

as

declare @rowsinserted int, @ErrorNo int

select @techid = techid from techs where tsename = @tsename
select @productid = productid from products where productname = @productname

insert INTO cases (casenum, techid, productid, entdate)
values(@casenum, @techid, @productid, @entdate)
SELECT @rowsinserted  = @@ROWCOUNT, @ErrorNo = @@ERROR
SELECT 'Total Rows inserted are : ' + convert(varchar, @rowsinserted  ) + ' with error number ' + convert(varchar, @ErrorNo)

This should show you how many rows are being inserted i.e. 1 or 0. If you have 0, you should likely have an error with associated error number.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[amrita418]

I might be wrong here, but AFAIK you cannot assign a string or int value to a uniqueidentifier field.

Is there a specific reason you are using uniqueidentifier instead of scalar data types?

Regards,
AA

 

[amrita418]

Is your techId, product Id of the following format?

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF

If not then you cannot assign the string value to uniqueidetifier.