[RESOLVED] Get values and insert every data continuously using cursor

[Guvera]

Hi,

I am using sql server2005. I have an error while executing this sp.
I get the values that @featureoperationmapid and @user id from Users table and need to insert UserPermission table. I am getting error.My error is

"An INSERT statement cannot contain a SELECT statement that assign values to a variable.
Incorrect Syntax near "END". "


DECLARE @userid int
DECLARE @usertypeid int
DECLARE @userpermissionmap varchar(100)
DECLARE @featureoperationmapid int
--Query to get feature name and operation name based on user type

CREATE TABLE #UserPermissionTypes(FK_FeatureOperationMapID int,PK_UserID int)

INSERT INTO #UserPermissionTypes(FK_FeatureOperationMapID,PK_UserID)

SELECT @featureoperationmapid=FeatureUserTypeMapping.FK_FeatureOperationMapID,@userid=Users.PK_UserID
FROM Users INNER JOIN
UserTypes ON Users.FK_UserTypeID = UserTypes.PK_UserTypeID INNER JOIN
FeatureUserTypeMapping ON UserTypes.PK_UserTypeID = FeatureUserTypeMapping.FK_UserTypeID
WHERE (Users.EmailID = @emailid)

DECLARE curInsertUserPermission CURSOR
FOR SELECT * FROM #UserPermissionTypes
OPEN curInsertUserPermission
FETCH NEXT FROM curInsertUserPermission INTO @featureoperationmapid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO UserPermission (FK_UserID,FK_FeatureOperationMapID)
VALUES (@userid,@featureoperationmapid)
END
CLOSE curInsertUserPermission
DEALLOCATE curInsertUserPermission
END

Please suggest me.

Hope yours reply soon.

Thanks

[dee-u]

Instead of using a cursor you can use something like this.

INSERT INTO UserPermission (FK_UserID,FK_FeatureOperationMapID)
SELECT @userID,FK_FeatureOperationMapID FROM #UserPermissionTypes

[Guvera]

hi,

thanks for your reply. i got it