New to Stored Procedure

[sdh]

New to stored procedures I have just created this to
update two tables in my db

Problem is when the procedure is executed
the first table has the records inserted but the second is not

Any pointers as to why would be very helpful

CREATE PROCEDURE [dbo].[sp_AddNewUser]
(
@tbUsr_BadgeNo varchar(50), @tbUsr_First char(50), @tbUsr_Last char(50), @tbUsr_Company char(50), @tbUsr_Address1 char(50), @tbUsr_Address2 char(50), @tbUsr_Town char(50)
, @tbUsr_County char(50), @tbUsr_Country char(50), @tbUsr_PostZip varchar(50)
,@tbUsr_Telno varchar(20), @tbUsr_FaxNo varchar(20), @tbUsr_Email varchar(50)
,@tbLog_passphrase char(10)
)
AS

Begin

INSERT INTO [dbTophorse].[dbo].[tbUser]( [tbUsr_BadgeNo], [tbUsr_First], [tbUsr_Last], [tbUsr_Company], [tbUsr_Address1], [tbUsr_Address2], [tbUsr_Town], [tbUsr_County], [tbUsr_Country], [tbUsr_PostZip], [tbUsr_Telno], [tbUsr_Fax], [tbUsr_Email])
VALUES(@tbUsr_BadgeNo, @tbUsr_First,@tbUsr_Last, @tbUsr_Company, @tbUsr_Address1, @tbUsr_Address2, @tbUsr_Town, @tbUsr_County, @tbUsr_Country, @tbUsr_PostZip, @tbUsr_TelNo, @tbUsr_FaxNo, @tbUsr_Email)



Declare @Usr_Id int

Set @Usr_Id=@@IDENTITY


Declare @LoginSql varchar(8000)

Set @LoginSql='INSERT INTO [dbTophorse].[dbo].[tbLogin]( [tbUsr_id], [tbUsr_BadgeNo], [tbLog_passphrase])
VALUES(' + Convert(int,@Usr_Id) + ',' + Convert(int, @tbUsr_BadgeNo)+','+ Convert(varchar, @tbLog_passphrase)+')'


exec(@LoginSql)



End

 

[nigelrivett]

Why is the second in dynamic sql?

Do you get any errors?

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

Disclaimer: This is only my opinion, but.....

This is poor programming. You DECLARE @User_ID as an Integer, but in your script you CONVERT it to an INT. WHY? It already is INT, Convert isn't needed.

Why are you accepting (in the DECLARE) the value @tbLog_Passphrase as a CHAR when you are inserting it as a VARCHAR?

I can 'kind of' understand the conversion of @tbUser_BadgeNo. You accept it as a VARCHAR in the DECLARE and you don't convert it in the first INSERT, so it must be VARCHAR in tbUser. But WHY is it then an INT in the second table (tbLog)????

Change your DECLARE to require @tbLog_Passphrase as a VARCHAR.

Drop the two Converts (for @tbLog_UserID and @tbLog_Passphrase).

I suggest deciding what datatype you really want @tbUser_BadgeNo to be and making that field in both tables the same datatype. Then fix the DECLARE to the correct datatype. That will get rid of the third convert.

Lastly, I agree with Nigel....what is the purpose of having dynamic SQL with 'regular' SQL?

-SQLBill

 

[sdh]

Thanks for the comments
Bill all fair comments we all have to learn

I have modified it accordingly no dynamic sql now
and appropriate type declaration

I get no error messages but this does still not work
the second insert just does not happen help much appreciated

 

[SQLSister]

First, GEt rid of the @@identity and use scope_identity instead. @@identity should never be used for this purpose as a trigger on the table inserting into another table with an identity columnwill break it.

In order to find out what is happening, you need to first comment out the second insert and instead return a select statement or rpint statment which tell you the variable values you are using for the insert. More than likely this will how you what the problem is.

Next go to query analyzer and run the insert statement as you have it written only substitutiing the actual values for the variables. If you get an error, come back and tell us exactly what the error is.

Most likely your problem is that the variables don't contain the information they should, or you are not inserting data into all the fields in the table and one of the fields not inserted is one which requires data. Or There is a trigger onthe table which is failing. Or there is some kind of referential integrity issue.

Questions about posting. See faq183-874