CODEWITHSUNDEEP
sqlsql-server
Sean Long
Sean Long

Reputation: 2301

Using a variable in a stored procedure variable

I'm using a variable to store a count and am trying to fire off a stored procedure a number of times, iterating the number each time. However, I'm having problems with the syntax I need to use. Here's what I have so far:

declare @count INT
declare @total INT
declare @p1 nvarchar(255)

set @count = 1
set @total = 50
    if @count <= @total
    begin
        set @p1=NULL
        exec USP_DATAFORM_ADDNEW_b9c5ae3e_1e40_4e33_9682_18fb0bb40ff2 @ID=@p1 output,@ROLENAME='Load Test Role ' + @count,@DESCRIPTION=N'Role used for automated load test.',@COPYUSERS=0,@CHANGEAGENTID='023C133B-D753-41E9-BCC6-1E33A4ACD600',@SYSTEMROLEID=N'3a33d7a7-c3b3-4a34-a4d7-99ef1af78fb8'
        select @p1
        set @count = @count + 1
    end

The problem is that @ROLENAME='Load Test Role ' + @count part. What's the right syntax to use? Should I use dynamic SQL here and define the entire thing in an exec_sql statement?

For reference, I'm in SQL Server 2008 R2. The error I get is Incorrect syntax near '+'.

Upvotes: 0

Views: 74

Answers (2)

Anonymouse
Anonymouse

Reputation: 11

Why dont you iterate it within the code? Might be easier.

declare @count INT = 1,
        @total INT = 50,
        @p1 nvarchar(255)

    while @count <= @total
    begin
        set @p1=NULL
        exec USP_DATAFORM_ADDNEW_b9c5ae3e_1e40_4e33_9682_18fb0bb40ff2 @ID=@p1 output,@ROLENAME='Load Test Role ' + @count,@DESCRIPTION=N'Role used for automated load test.',@COPYUSERS=0,@CHANGEAGENTID='023C133B-D753-41E9-BCC6-1E33A4ACD600',@SYSTEMROLEID=N'3a33d7a7-c3b3-4a34-a4d7-99ef1af78fb8'
        select @p1
        set @count = @count + CAST(@count AS varchar(2))
    end

Upvotes: 1

Bill Hall
Bill Hall

Reputation: 586

Cast the int var as a nvarchar:

@ROLENAME='Load Test Role ' + CAST(@count AS nvarchar)

Upvotes: 3

Related Questions