Dynamic SQL is merely where the query has been built on the fly - with some vendors, you can build up the text of the dynamic query within one stored procedure, and then execute the generated SQL.
Here's a few articles: Introduction to Dynamic SQL Dynamic SQL Beginner's Guide From Introduction to Dynamic SQL: Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexible and powerful tool. You can use dynamic SQL to accomplish tasks such as adding where clauses to a search based on ...
What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using EXEC (@SQL) versus EXEC SP_EXECUTESQL @SQL ?
On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don't need to update the values in the table itself. So from the code you posted you could use this approach for @TSku but not for @RelPro Example syntax below.
The way you do this is with dynamically generated SQL which is run through the sp_executesql () stored procedure. In general you pass in your required table name to your master procedure, build an ncharvar string of the SQL you want to execute, and pass that to sp_executesql. The curse and blessing of Dynamic SQL is about the best page I have seen for describing all the in's and out's. One of ...
I want to build custom Where condition based on stored procedure inputs, if not null then I will use them in the statement, else I will not use them. if @Vendor_Name is not null begin set @
You would also need to declare the table variable as a statement inside the @sql variable, and execute your declare table and inserts together, or use a local/global temporary table.
If the in statement for column B contains 1145 rows, using a cursor to create indidivudal statements and execute them as dynamic SQL is far faster than using the in clause.
The key is to have table variable declared inside and outside dynamic query. At the end of dynamic query just select from table variable and insert resultset into outside table variable:
