CODEWITHSUNDEEP
sqlsql-servervariablesstored-procedures
Kamran Ahmed
Kamran Ahmed

Reputation: 12440

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

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?

Upvotes: 3

Views: 87797

Answers (3)

Kanhaiya lal Rajora
Kanhaiya lal Rajora

Reputation: 109

declare @total int

select @total = count(*) from news;

select * from news where newsid = @total+2

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

Upvotes: 1

Charleh
Charleh

Reputation: 14002

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

Upvotes: 5

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

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.

Upvotes: 0

Related Questions