9 replies to this topic

[tiagonl]

Estou executando um script de criação de uma procedure que retorna o seguinte erro no código abaixo:

IF (@ver > 7)
SELECT @SqlToExec = N'
CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000),
	@CurrentTimeUtc   datetime
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)
	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		SELECT TOP 1 Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END

	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1

	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		DELETE FROM @tbNames
		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)

		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
		  FROM   @tbNames

		INSERT INTO @tbUsers
		  SELECT  UserId
		  FROM	dbo.aspnet_Users au, @tbNames t
		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
	END

	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
	SELECT UserId, RoleId
	FROM @tbUsers, @tbRoles

	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END'
ELSE

O erro é o seguinte:

Server: Msg 141, Level 15, State 1, Line 137
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


Alguém poderia me ajudar ?

[Luis Otávio]

q q vc faz na linha 137?

[tiagonl]

Na linha do erro estou fazendo esta declaração:

	DECLARE @user_id smallint

[Luis Otávio]

cara.. ta passando valor certo por esta variavel? @user_id
ta parecendo q naum ta passando inteiro por ela...

[tiagonl]

Olá...
A variável está recebendo um valor inteiro sim.

[Miguceamma]

Viamedic Scam Levitra 10mg En Baisse What Body System Does Amoxicillin Effect cheap cialis Cephalexin Rimadyl Mix Symptoms Of Allergy To Amoxicillin
Zithromax From India Trusted Onlone Pharmacy cialis viagra levitra Want To Buy Fluoxetine
Viagra Samples From Doctor Propecia Treatment Hair Loss buy viagra Viagra Verpackung Original

[LarPhozyHah]

Kamagra Soft Tab viagra online pharmacy Buy Amoxicillin Tablets Glyburide Online No Prescription
H Pylori Treatment Nexium Amoxicilline cialis Cephalexin Veterinarian Finasteride Vs Propecia Hair Loss Treatment Cialis Cura La Disfuncion Erectil

[Miguceamma]

Tamoxifen Online Canada Inflamed Mouth Keflex Order Celebrex No Prescription viagra Viagra Online
Zithromax And Birth Control Flomax Amoxicillin Is Amoxicillin Bad For Dogs mail order levitra Libera Vendita Cialis Cialis Su Internet
Achat Ligne Xenical viagra Citrato De Sildenafila 50mg
Propecia Orkut Proscar Orlando Cialis Levothyroxine Buy Online cialis online Zithromax For Treatment Of Chlamydia
Viagra Bluthochdruck buy viagra Cialis Modo De Uso Free Shipping Dutasteride Pharmacy Medicamento Cialis Para Sirve

[Miguceamma]

Achat Levitra En Suisse viagra Best Price On Cialis 5mg
Amoxicillin 500mg Uses Kamagra Vente Libre Montreal cialis Propecia Generic Comparison
Contraindication Of Cephalexin viagra cialis Kamagra Oral Jelly No Presc

[Miguceamma]

Cialis E Farmaci buy viagra Get Cialis Very Cheap Viagra Generika Erfahrungsbericht
Priligy Approbation De La Fda Prix Viagra France Buy Levitra Online Dream Pharmaceutical viagra Where To Order Genuine Drug Alli Keflex Treat Urinary Tract Infection