CODEWITHSUNDEEP
sqlsql-server
tosorro
tosorro

Reputation: 115

Must declare the scalar variable

When I try to exec this query I'll get the error:

Must declare the scalar variable

What am I doing wrong?

    @username varchar(50),
    @routenaam varchar(50),
    @van varchar(50),
    @naar varchar(50),
    @bezoekadres varchar(50),
    @geredenroute varchar(50),
    @karakterrit varchar(50),
    @toelichting varchar(50)
    as
    begin
    DECLARE @sql NVARCHAR(MAX) = 'insert into '+@username+'routeinformatie'+' values(
    @routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting)'


    EXEC sp_executeSQL @sql,N',
    @username varchar(50),
    @routenaam varchar(50),
    @van varchar(50),
    @naar varchar(50),
    @bezoekadres varchar(50),
    @geredenroute varchar(50),
    @karakterrit varchar(50),
    @toelichting varchar(50)
    ',@username@routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting
    end

Upvotes: 1

Views: 1618

Answers (4)

tosorro
tosorro

Reputation: 115

    create proc insertRoute
    @username varchar(50),
    @routenaam varchar(50),
    @van varchar(50),
    @naar varchar(50),
    @bezoekadres varchar(50),
    @geredenroute varchar(50),
    @karakterrit varchar(50),
    @toelichting varchar(50)
    as
    begin
    DECLARE @sql NVARCHAR(MAX) 
    SET @sql = N'insert into '+@username+'routeinformatie'+' values(
    @routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting)'


    EXEC sp_executeSQL @sql,N',
    @username varchar(50),
    @routenaam varchar(50),
    @van varchar(50),
    @naar varchar(50),
    @bezoekadres varchar(50),
    @geredenroute varchar(50),
    @karakterrit varchar(50),
    @toelichting varchar(50)
    ',@username@routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting
    end

Making stored procedure. When I want to make it now it says: "Must declare the scalar variable "@username@routenaam"."

Upvotes: 1

veljasije
veljasije

Reputation: 7092

You forgot starting DECLARE clause, and in last line you omitted comma between first two variables:

DECLARE   
    @username varchar(50),
    @routenaam varchar(50),
    @van varchar(50),
    @naar varchar(50),
    @bezoekadres varchar(50),
    @geredenroute varchar(50),
    @karakterrit varchar(50),
    @toelichting varchar(50)
    as
    begin
    DECLARE @sql NVARCHAR(MAX) = 'insert into '+@username+'routeinformatie'+' values(
    @routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting)'


    EXEC sp_executeSQL @sql,N',
    @username varchar(50),
    @routenaam varchar(50),
    @van varchar(50),
    @naar varchar(50),
    @bezoekadres varchar(50),
    @geredenroute varchar(50),
    @karakterrit varchar(50),
    @toelichting varchar(50)
    ',@username, @routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting
    end

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172588

You need to set the value of the variable like this:

DECLARE @sql NVARCHAR(MAX) 
SET @sql = N'insert into '+@username+ N'routeinformatie'+ N' values(
    @routenaam,@van,@naar,@bezoekadres,@geredenroute,@karakterrit,@toelichting)'

Also if this is complete query then you need to add declare at the starting

DECLARE @username varchar(50),
   @routenaam varchar(50),
   @van varchar(50),
   @naar varchar(50),
   @bezoekadres varchar(50),
   @geredenroute varchar(50),
   @karakterrit varchar(50),
   @toelichting varchar(50)

Upvotes: 2

Chanom First
Chanom First

Reputation: 1136

You must use DECLARE statement with All variable like this

DECLARE @username varchar(50),
   @routenaam varchar(50),
   @van varchar(50),
   @naar varchar(50),
   @bezoekadres varchar(50),
   @geredenroute varchar(50),
   @karakterrit varchar(50),
   @toelichting varchar(50)

Upvotes: 0

Related Questions