Get values and insert every data continuously using cursor

Question

Get values and insert every data continuously using cursor

ashok-831066

Right there with Babe

Points: 778
More actions
October 6, 2009 at 10:56 pm

#139235

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sanoj_av2001 SSChasing Mays Points: 601 More actions · Answer 1

/*

You cannot assign Values to a variable like @featureoperationmapid=FeatureUserTypeMapping.FK_FeatureOperationMapID,@userid=Users.PK_UserID in an Insert Statement.

*/

CReate Proc YourProcName(@emailid varchar(50)) as

BEGIN

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

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO UserPermission (FK_UserID,FK_FeatureOperationMapID)

VALUES (@userid,@featureoperationmapid)

END

CLOSE curInsertUserPermission

DEALLOCATE curInsertUserPermission

END

..

ashok-831066 Right there with Babe Points: 778 More actions · Answer 2

Hi,

thanks for yours reply. I am not assigned in insert statement. I need to get @featureoperationmapid and @userid and insert into another table.

Without assigning how i select these values and insert into userpermission table.

Thanks

sanoj_av2001 SSChasing Mays Points: 601 More actions · Answer 3

I think you have not checked the code I posted. I had removed the variable assignment.

Again why do you use a temporory table and a cursor for a simple insert form one table to another?

You can use an insert stament like

INSERT INTO UserPermission (FK_FeatureOperationMapID,FK_UserID)

SELECT

FeatureUserTypeMapping.FK_FeatureOperationMapID,

@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)

..

ashok-831066 Right there with Babe Points: 778 More actions · Answer 4

yeah you are right. But i am not insert single row i inserting multiple rows at a time.

So only i am using cursor.

Thanks

ashok-831066 Right there with Babe Points: 778 More actions · Answer 5

ashok-831066

Right there with Babe

Points: 778

October 7, 2009 at 12:43 am

#1062599

Hi sanoj

Thanks i got it. 🙂

sanoj_av2001 SSChasing Mays Points: 601 More actions · Answer 6

But i am not insert single row i inserting multiple rows at a time.
So only i am using cursor.

You need not use a cursor for inserting multiple rows at a time.

Insert into <Target Table> Select * from <source Table> pattern can insert multiple rows...::

..