How to set dynamic query value to a variable in SQL Server stored procedure with parameters?

Question

I want to set a variable to the value generated using the dynamic query outside the query.

I tried the sp_executesql concept, but this does not help me because I am using the parameter value in the dynamic query.

Are there any possibilities to fix this issue?

CREATE PROCEDURE [dbo].[SP_test_proc] 
    @id int = null, 
    @deptId int = null
As
BEGIN
    DECLARE @Condition VARCHAR(MAX),@Query NVARCHAR(MAX)

    SET @Condition= 'Where Id = '@id + case when @deptid is null then '' else @deptid End

    SET @Query = 'Declare @Name varchar(100)
                  Set @Name = Select name from student '+ @Condition

    SELECT * 
    FROM personal 
    WHERE name = @Name
END

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 25, 2016 at 15:42

Community · Accepted Answer · 2017-05-23 12:24:35Z

1

Use output parameter in dynamic sql as shown here Also see this Try this :

DECLARE @Condition VARCHAR(MAX),@Query NVARCHAR(MAX)

    SET @Condition= 'Where Id = '@id + case when @deptid is null then '' else @deptid End
    Declare @Name varchar(100),@nameval varchar(100),@paramdef varchar(100)

    SET @Query = '
                  Select @Name = name from student '+ @Condition

    set @paramdef=N'@Name varhcar(20) OUTPUT'

    execute sp_executesql @Query,@paramdef,@Name=@nameval Output
    SELECT * 
    FROM personal 
    WHERE name = @nameval

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered May 25, 2016 at 15:52

Codeek

1,6341 gold badge11 silver badges20 bronze badges

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

2 Comments

Jitendra Pancholi Over a year ago

how to do it for more than one parameter?

Codeek Over a year ago

Then in paramdef, add comma separated output variable definitions and similarly pass those variables as comma separated parameters in sp_executesql procedure

Myo Myint Aung · Accepted Answer · 2016-05-26 04:24:13Z

1

Hope this code will work for you.

CREATE PROCEDURE [dbo].[SP_test_proc] 
@id int = null, 
@deptId int = null
As
BEGIN
DECLARE @Condition VARCHAR(MAX),@Query NVARCHAR(MAX)

SET @Condition= 'Where Id = ' + @id + ' And [Deptid] = ' + ISNULL(@deptid,'')

SET @Query = 'SELECT * FROM personal WHERE name IN ( SELECT name FROM student ' +  @Condition + ')'
EXEC @Query
END

[Deptid] is not sure I don't know column name

answered May 26, 2016 at 4:24

Myo Myint Aung

1471 silver badge3 bronze badges

Collectives™ on Stack Overflow

How to set dynamic query value to a variable in SQL Server stored procedure with parameters?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related