How to use SQL Variables inside a query ( SQL Server )?

Question

I have written the following SQL Stored Procedure, and it keeps giving me the error at @pid = SELECT MAX(... The whole procedure is:

Alter PROCEDURE insert_partyco
@pname varchar(200)
AS
BEGIN
    DECLARE @pid varchar(200);

    @pid = SELECT MAX(party_id)+1 FROM PARTY;
    INSERT INTO party(party_id, name) VALUES(@pid, @pname)
    SELECT SCOPE_IDENTITY() as PARTY_ID
END
GO

Can anyone please tell me what I'm doing wrong here?

As @jyparask has said - you can make the id column auto-increment, though I'm not sure why you've declared pid as a varchar when it appears to be an int value... — Charleh
– Charleh, Commented Oct 5, 2013 at 13:52

Charleh · Accepted Answer · 2013-10-05 13:49:03Z

5

Alter PROCEDURE insert_partyco
@pname varchar(200)
AS
BEGIN
    DECLARE @pid varchar(200);

    SELECT @pid = MAX(party_id)+1 FROM PARTY;
    INSERT INTO party(party_id, name) VALUES(@pid, @pname)
    SELECT SCOPE_IDENTITY() as PARTY_ID
END

This has an advantage over SET with SELECT in that you can select expressions in multiple variables in one statement:

SELECT @var1 = exp1, @var2 = expr2 ... etc

answered Oct 5, 2013 at 13:49

Charleh

14k3 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom O · Accepted Answer · 2015-04-21 13:28:19Z

1

declare @total int

select @total = count(*) from news;

select * from news where newsid = @total+2

//**news** table name and **newsid** column name

edited Apr 21, 2015 at 13:28

Tom O

1,8002 gold badges21 silver badges43 bronze badges

answered Apr 21, 2015 at 12:56

Kanhaiya lal Rajora

1091 silver badge4 bronze badges

Comments

Giannis Paraskevopoulos · Accepted Answer · 2013-10-05 13:49:00Z

0

You need to use SET.

Alter PROCEDURE insert_partyco
@pname varchar(200)
AS
BEGIN
    DECLARE @pid varchar(200);

    SET @pid = (SELECT MAX(party_id)+1 FROM PARTY);
    INSERT INTO party(party_id, name) VALUES(@pid, @pname)
    SELECT SCOPE_IDENTITY() as PARTY_ID
END
GO

Alternatively, in your case you could make party_id an autoincremented value, so you wouldn't need to query the table.

answered Oct 5, 2013 at 13:49

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

4 Comments

Charleh Over a year ago

Alternatively you can use SELECT @variable = <expression> as you can then select multiple results into multiple variables in a single statement

Giannis Paraskevopoulos Over a year ago

Yes, i know, it is just the closer to his try.

Kamran Ahmed Over a year ago

@Jyparask! in the alternative case, as you suggested, wouldn't it effect if we want to insert the value by ourserlves somewhere else?

Giannis Paraskevopoulos Over a year ago

@KamranAhmed Since you are selecting SCOPE_IDENTITY you will get the newly inserted id.

Collectives™ on Stack Overflow

How to use SQL Variables inside a query ( SQL Server )?

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related