Get values and insert every data continuously using cursor

Hi,

I am using sql server2005. I have an error while executing this sp.
Iget the values that @featureoperationmapid and @user id from Userstable and need to insert UserPermission table. I am getting error.Myerror 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

There are a few things wrong with that T-SQL paragraph, I have highlighted the problem areas in bold below:
-----------------------------------------------------------------------
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
-----------------------------------------------------------------------
The problems explained:
(1) SELECT @featureoperationmapid=FeatureUserTypeMapping.FK_FeatureOperationMapID,@userid=Users.PK_UserID
This is the one causing your error message.
This is incorrect syntax, you do not need to use variables in this part of the statement.
You should write this as:
SELECT FeatureUserTypeMapping.FK_FeatureOperationMapID,Users.PK_UserID
(2) @emailid
This @emailid variable is not declared and has no value set. If this variable is passed to the SP as a parameter then it's OK.
(3) @userid
The @userid variable has no value set here.
The line:
FETCH NEXT FROM curInsertUserPermission INTO @featureoperationmapid
should be changed to:
FETCH NEXT FROM curInsertUserPermission INTO @featureoperationmapid,@userid
(4) You are missing an additional
FETCH NEXT FROM curInsertUserPermission INTO @featureoperationmapid,@userid
from your WHILE condition.
@@FETCH_STATUS will remain at 0 if you do not FETCH NEXT from inside the condition block.
Below is the whole statement with some corrections.
----------------------------------------------
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 FeatureUserTypeMapping.FK_FeatureOperationMapID,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,@userid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO UserPermission (FK_UserID,FK_FeatureOperationMapID)
VALUES (@userid,@featureoperationmapid)
FETCH NEXT FROM curInsertUserPermission INTO @featureoperationmapid,@userid
END
CLOSE curInsertUserPermission
DEALLOCATE curInsertUserPermission
----------------------------------------------
Hope this helps.

Hi david,

Thanks for your help. Its working fine.
Thanks