How to use @in SQL SERVER?

Asked

Viewed 74 times

1

I need to recover the quantity of product with price between 0 and 10. Error in variable @W_NUM of select command.

BEGIN TRANSACTION;
DECLARE @W_P1 int;
DECLARE @W_P2 int;
DECLARE @W_NUM int;
set @W_P1=0;
SET @W_P2=10;
SET @W_NUM=0;

BEGIN 
WHILE (@W_P2<300)
SELECT COUNT(P_CODE)INTO @W_NUM FROM PRODUCTT WHERE P_PRICE>@W_P1 AND P_PRICE<@W_P2;
PRINT('EXISTEM'+@W_NUM+'PRODUTOS COM O PRECO ENTRE'+@W_P1+' E '+@W_P2)
@W_P1=@W_P1+1;
@W_P2=@W_P2+50;
END
COMMIT
sql-server

  • what error you receive?

  • 1

    Also check if your table calls even PRODUCTT with 2 T

  • IN ENCRYPTION @W_P1=@W_P1+1; @W_P2=@W_P2+50;

  • But what is the error message/code?

  • @OrlandoCawende http://answall.com/help/question-bans

2 answers

3

Try to do:

SELECT @W_NUM = COUNT(P_CODE) FROM PRODUCTT WHERE P_PRICE>@W_P1 AND P_PRICE<@W_P2;

  • IN THIS ENCRYPTION @W_P1=@W_P1+1; @W_P2=@W_P2+50;

  • what has the increment?

2


Try

-- código #1 v2
DECLARE @W_P1 int;
DECLARE @W_P2 int;
DECLARE @W_NUM int;
set @W_P1= 0;
set @W_P2= 10;
set @W_NUM= 0;

WHILE (@W_P2 < 300)
  BEGIN
  SET @W_NUM= (SELECT COUNT(P_CODE) FROM PRODUCTT 
                 WHERE P_PRICE > @W_P1 AND P_PRICE < @W_P2);
  PRINT 'EXISTEM ' + Cast(@W_NUM as varchar(10)) +
         ' PRODUTOS COM O PREÇO ENTRE ' + Cast(@W_P1 as varchar(10)) +
         ' E ' + Cast(@W_P2 as varchar(10));
  --
  set @W_P1= @W_P1 + 1;
  set @W_P2= @W_P2 + 50;
  END;

  • Thanks worked out great. However I would like to know if there is a form of loan without using this and why to convert to varchar SELECT 'THERE ARE' + Cast(@W_NUM as varchar(10)) + ' PRODUCTS WITH THE PRICE BETWEEN ' + Cast(@W_P1 as varchar(10)) + ' AND ' + Cast(@W_P2 as varchar(10))

  • @Orlandocawende, since the message is a string expression, variables that are not of the string type (char, varchar, etc.) must be converted to string. There are cases where implicit conversion is generated by SQL Server itself, but to avoid errors it is better to implement the conversion.

  • Thank you. could help in this case I have a table in which when the quantity in stock(P_QOH) is less or equal to the minimum quantity(P_MIN) should put in P_REORDER=1 otherwise P_REORDER=0 I was able to do it in Oracle more precise it in sql server 2012 how to do this. CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE OF P_QOH ,P_MIN ON PRODUCTT FOR EACH ROW BEGIN IF:NEW.P_QOH<=NEW.P_MIN THEN :NEW.P_REORDER:=1; ELSE :NEW.P_REORDER:=0; END IF; END; /

  • @Orlandocawende, regarding this new request, I suggest you create a new topic, where you can properly format the code and describe what you need.

  • I’m no longer allowed to ask questions

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.