Skip to main content
21 votes
Accepted

How to Dynamically change the database using TSQL

SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL. If the ultimate goal is to execute some other dynamic SQL inside the chosen database, this is easy ...
Aaron Bertrand's user avatar
18 votes
Accepted

How to use function parameters in dynamic SQL with EXECUTE?

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here. CREATE OR REPLACE FUNCTION insert_records_for_notification( _username text ...
Erwin Brandstetter's user avatar
16 votes

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Simple case, static SQL The non-dynamic solution with crosstab() for the simple case: SELECT * FROM crosstab( 'SELECT b.x, f.name, f.x * b.x AS prod FROM foo f, bar b ORDER BY 1, 2' ) AS ...
Erwin Brandstetter's user avatar
16 votes
Accepted

EXEC vs SP_EXECUTESQL Performance

This is mostly a preference due to security and consistency, and has nothing to do with performance (though that may have been more of a concern in ancient versions of SQL Server). Why use EXEC() some ...
Aaron Bertrand's user avatar
15 votes
Accepted

what is causing arithmetic overflow in the query below?

I am struggling to find the reason of the arithmetic overflow. why is it happening? Most likely the metadata is returning some unexpected values that your code cannot handle. For example: -- Example ...
Paul White's user avatar
  • 86.4k
13 votes
Accepted

Dynamic SQL query - how do I add an int to the code?

George's answer does solve your problem, but it leaves you wide open to SQL injection attacks. While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the ...
Erik Darling's user avatar
  • 41.9k
13 votes

How does use of sp_executesql with parameters protect against SQL injection?

double-up To answer your question, you need to experiment with alternatives to using sp_executesql with parameters: Using EXEC (without sp_executesql) Using sp_executesql (without parameters) Both ...
Erik Darling's user avatar
  • 41.9k
12 votes

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

What's the best we can do in PostgreSQL without a "column definition list" to generate that kind of presentation? If you frame this as a presentation problem, you might consider a post-query ...
Daniel Vérité's user avatar
11 votes
Accepted

If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

From an answer by Remus Rusanu: Dynamic SQL runs in the same session as the calling code. The issue is not session, but scope. Your dynamic SQL creates the temporary tables inside the ...
Randi Vertongen's user avatar
11 votes
Accepted

sp_executesql Not Working with Parameters

erland You can't use object names as parameters in dynamic SQL. That's why your first attempt doesn't work If you combine your first and second attempts, you can at least parameterize the @StoreId ...
Erik Darling's user avatar
  • 41.9k
9 votes

Wildcard search using parameters in function with dynamic SQL

Let's suppose for country_name you wanted to do a leading and trailing wildcard search. You don't need dynamic SQL for this. Just: CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text) ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Safe alternative to exec(sql)

For certain types of parameterization, it's not going to matter if you use EXEC() or sp_executesql, because some things can't be parameterized anyway. For example, you expressed in the comments (...
Aaron Bertrand's user avatar
9 votes
Accepted

Performance issue with sp_executesql and VARCHAR parameter

What you're running into is the curse of local variables. The short of it is that when you declare a variable and then use it in a query, SQL can't sniff the value. It sometimes uses magic numbers ...
Erik Darling's user avatar
  • 41.9k
9 votes
Accepted

Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391

In my testing (to another instance on the same server, not on a separate server), this worked if I had the Linked Server option of Enable Promotion of Distributed Transactions for RPC set to "False". ...
Solomon Rutzky's user avatar
8 votes
Accepted

How can I update multiple tables at once in Postgres?

You're actually close.. first create some test data.. CREATE TABLE foo_knex_migrations ( name ) AS VALUES ('test.js'),('test2.js'),('bicycles'); CREATE TABLE bar_knex_migrations AS TABLE ...
Evan Carroll's user avatar
  • 63.7k
8 votes
Accepted

Is it good practice to create tables dynamically?

There isn't any definitive answer for this problem. You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more ...
BradC's user avatar
  • 10k
8 votes

How does use of sp_executesql with parameters protect against SQL injection?

SQL injection is the act of taking the data of parameters that are used to create a query, and injecting them into the query. In other words, instead of using parameters orderid = @oid you are using ...
Charlieface's user avatar
  • 13.3k
7 votes

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

This seems to be an on-going issue Views are messy to handle with Dynamic SQL Earliest Bug was Cannot create VIEWs in prepared statements from 11 years ago. There was a patch put in to address it. ...
RolandoMySQLDBA's user avatar
7 votes
Accepted

Assignment of a column with dynamic column name

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE. And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there ...
Erwin Brandstetter's user avatar
7 votes
Accepted

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

You can easily tell the dynamic SQL execute in a specific database by dynamically building a [database].sys.sp_executesql command: USE your_database; GO CREATE PROCEDURE dbo.DatabaseNameOptional ...
Aaron Bertrand's user avatar
7 votes
Accepted

Stored Procedure error “Must declare the scalar variable”

You're not declaring either @numOfOffset or @numOfRows variables within the context of your Dynamic SQL statement. If you copy the output of your PRINT @qaeryMain statement, you'll notice the ...
John Eisbrener's user avatar
7 votes

exec limited to 4000 characters?

Your problem is with the CONCAT. As documented, the return type depends on the type of the parameters. With a mixture of short nvarchar and varchar literal strings, your CONCAT will return nvarchar ...
Paul White's user avatar
  • 86.4k
6 votes
Accepted

Native dynamic SQL - execute immediate with column and table names

Because a bind variable is a value or value address, not a table or column name. bind variable A placeholder in a SQL statement that must be replaced with a valid value or value address for the ...
Balazs Papp's user avatar
  • 40.8k
6 votes

EXEC vs SP_EXECUTESQL Performance

First of all lets check what both commands mean: sp_executesql: Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL ...
Ahmad Abuhasna's user avatar
6 votes
Accepted

The metadata could not be determined because statement invokes an extended stored procedure

Nothing (that I can see) changed between SQL Server 2012 and 2016 with respect to this issue. Not working with temp tables, multiple result sets, extended stored procedures, CLR objects, etc have all ...
Solomon Rutzky's user avatar
6 votes
Accepted

Special letter behaviour problem?

The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string ...
Aaron Bertrand's user avatar
6 votes
Accepted

metadata could not be determined because stored procedure contains dynamic SQL

The problem is not in your proc but in OPENROWSET/OPENQUERY. Prior to SQL Server 2012 to figure out the resultset metadata set fmtonly was used. Starting with 2012 it was replaced by ...
sepupic's user avatar
  • 11k

Only top scored, non community-wiki answers of a minimum length are eligible