Insert value not happy, why?

How am I doing so far? I am trying to Insert the user_id if it does not allready exist in the table. But it does not like the value I am giving it in the INSERT statement.

What can I do?

ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data-- define parameters--@.passUserId intAS--Set @.passUserId = UserIdSELECT [user_id]As UserIdFROM CC_host.dbo.usr_tableAs t1Where(emailLIKE N'%@.%'And emailLIKE N'%usa.com')Order By UserIdIfNot Exists (Select [user_id]As UserId2From Usr_Type_DataWHERE UserId2 = UserId)INSERT INTO Usr_Type_Data (user_id, CustomerTypeId)VALUES(t1.UserId, 7)GO

The error I am getting in Query Analyzer (SQL SERVER 2000) is:

The name 'UserId' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Is this going to require a DO Loop inside the stored procedure?

I am not used to this kind of Stored Procedures :)

Is Looping in T-SQL done with BEGIN and END?

|||

You have it all messed up. Assuming you get some parameters, here's how it would look:

ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data-- define parameters@.passUserIdintASBeginIfNOT EXISTS (Select *FROM CC_host.dbo.usr_tableWhere UserID = @.passUserIdAND emailLIKE N'%@.%usa.com')INSERT INTO Usr_Type_Data (user_id, CustomerTypeId)VALUES(@.passUserId, 7)End

|||

Thanks, that is the direction I was trying to head, as indicated in my messages so not sure I am all too messed up. Just have not done it before. :)

I don't want to pass a value from an outside source though, which I think this procedure you have written would require?

Can something be done to pass the value from the first select statement? There are about 1500 records that need to be checked...

DECLARE @.passUserId int

SET @.passUserId = (Select *, user_id As UserId FROM CC_host.dbo.usr_table Where(email LIKEN'%@.%usa.com'))

Begin
If NOT EXISTS (Select * FROM CC_host.dbo.usr_table Where UserID = @.passUserId AND email LIKEN'%@.%usa.com')
INSERT INTO Usr_Type_Data (user_id, CustomerTypeId) VALUES(@.passUserId, 7)
End

Thank you.

Dan

|||

Try this:

Declare @.UserId int

Set @.UserId = 0

SELECT @.UserId =user_idFROM CC_host.dbo.usr_tableWhere(emailLIKE N'%@.%'And emailLIKE N'%usa.com')Order By user_id --(I think you should use TOP 1 to get only one user_id. But, this is OK.

 --You will only insert one in your later query)IfNot Exists (Select [user_id]From Usr_Type_DataWHERE UserId2 = @.UserId)INSERT INTO Usr_Type_Data (user_id, CustomerTypeId)VALUES(@.UserID, 7)GO

|||

Ahh very cool, but it only inserted 1 of the 1500 records it should have updated :) When I executed it.

ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data

AS
DECLARE @.passUserId int
Set @.passUserId = 0

Select @.passUserId = user_id FROM CC_host.dbo.usr_table Where(email LIKEN'%@.%usa.com')

If Not Exists (Select [user_id] From Usr_Type_Data WHERE [user_id] = @.passUserId)
INSERT INTO Usr_Type_Data (user_id, CustomerTypeId) VALUES(@.passUserId, 7)
GO

Probably it was the LAST record in the original query that got inserted. :)

That is why I am wondering If there is not some sort of looping method that could have taken place, or alternatively should the query be put into a derived table?

|||

Perhaps it needs to be done totally different aka:

INSERT INTO Usr_Type_DataSELECT user_id, 7FROM CC_host.dbo.usr_table usrWHERE (usr.emailLIKE N'%@.%usa.com')AND usr.user_idnot in (SELECT user_idfrom Usr_Type_Data)