i want to Write And Execute Dynamic SQL Query In SQL Server. 

please help me how to do it.

1 Answers

  1. Aditya Kumar Patel
    Write And Execute Dynamic SQL Query In SQL Server arya mercy
    9/8/2018 4:25:59 PM

    Here I am going to give you a simple example, how to write and execute dynamic SQL Query in SQL Server. You can execute the Query using EXEC sql command and sp_executesql commands in SQL Server.

     

    Example:1 (using EXEC SQL command)

     

    /* Variable Declaration */
    DECLARE @EmpID AS SMALLINT
    DECLARE @SQLQuery AS NVARCHAR(500)
     
    /* set the parameter value */
    SET @EmpID = 5
     
    /* Build Transact-SQL String with parameter value */
    SET @SQLQuery = 'SELECT * FROM Customer WHERE ID = ' + CAST(@EmpID AS NVARCHAR(10))
     
    /* Execute Transact-SQL String */
    EXECUTE(@SQLQuery)

    Example:2 (using sp_executesql commands)

    /* Variable Declaration */
    DECLARE @custID AS SMALLINT
    DECLARE @SQLQuery AS NVARCHAR(500)
    DECLARE @ParameterDefinition AS NVARCHAR(100)
     
    /* set the parameter value */
    SET @custID = 01
     
    /* Build Transact-SQL String by including the parameter */
    SET @SQLQuery = 'SELECT * FROM Customer WHERE ID  = @custID'
     
    /* Specify Parameter Format */
    SET @ParameterDefinition =  '@custID SMALLINT'
     
    /* Execute Transact-SQL String */
    EXECUTE
    sp_executesql @SQLQuery, @ParameterDefinition,@custID

     

    I hope it will help to you.


Leave a answer

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like