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*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, May 25 '16 at 15:42

score 1 · Accepted Answer · edited May 23 '17 at 12:24

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 '17 at 12:24

Community

1
1

answered May 25 '16 at 15:52

Codeek

1,624
1
11
20

how to do it for more than one parameter? – Jitendra Pancholi Aug 03 '16 at 13:02
Then in paramdef, add comma separated output variable definitions and similarly pass those variables as comma separated parameters in sp_executesql procedure – Codeek Aug 03 '16 at 13:44

score 1 · Answer 2 · answered May 26 '16 at 04:24

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

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

2 Answers2