Using variable value in string when executing EXEC in SQL

Question

I want to use a variable value in exec where i don't need to create the query itself. I will have a query stored in a field in my database and i just want to execute that using the parameters in that stored procedure. For Example below i declared two variables @ValueVariable is the parameter of stored procedure and what i declared @QueryString is the one i will read from data base and i want to execute that using the value of @ValueVariable.

DECLARE @ValueVariable  int=0
        @QueryString    VARCHAR(MAX)=
                        'SELECT UserName FROM TableUser WHERE UserId=@ValueVariable'

EXEC(@QueryString)

When i try to execute that i get an error Incorrect syntax near 'SELECT UserName FROM TableUser WHERE UserId=@ValueVariable'

I am aware that i can do it by

@QueryString    VARCHAR(MAX)=
                            'SELECT UserName FROM TableUser WHERE UserId='+@ValueVariable

But i want to use it as stated above. Not making a query in my procedure but using variable value as in string retrieved from DB.

So is there any way i could be able to execute that using the value from the variable in current environment.

If you want parameterised dynamic sql, use sp_executesql

MatBailie
– MatBailie

2012-06-15 14:36:37 +00:00
Commented Jun 15, 2012 at 14:36 — MatBailie
– MatBailie, Commented Jun 15, 2012 at 14:36
@Dems - You should post that as an answer

Lamak
– Lamak

2012-06-15 14:37:38 +00:00
Commented Jun 15, 2012 at 14:37 — Lamak
– Lamak, Commented Jun 15, 2012 at 14:37

MatBailie · Accepted Answer · 2012-06-15 14:42:41Z

3

You can use sp_executesql.

DECLARE
  @IntVariable    int,
  @SQLString      nvarchar(500),
  @ParmDefinition nvarchar(500)
SELECT
  @IntVariable    = 0,
  @SQLString      = N'SELECT UserName FROM TableUser WHERE UserId=@ValueVariable',
  @ParmDefinition = N'@ValueVariable INT'

SP_EXECUTESQL
  @SQLString,
  @ParmDefinition,
  @ValueVariable = @IntVariable;

In essence, it creates a one time stored procedure. The @paramDefinition variable is the parameter signature you'd normally see in a stored procedure, the sql server caches the execution plan, etc, etc.

answered Jun 15, 2012 at 14:42

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

Collectives™ on Stack Overflow

Using variable value in string when executing EXEC in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related