SQL Server Performance

Get values and insert every data continuously using cursor

Discussion in 'SQL Server 2005 General Developer Questions' started by sasivashok, Oct 6, 2009.

  1. sasivashok New Member

    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
  2. davidfarr Member

    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.
  3. sasivashok New Member

    Hi david,

    Thanks for your help. Its working fine.
    Thanks

Share This Page