Stack Overflow
Stack Overflow

Questions tagged [sp-executesql]

sp_executesql is a system stored procedure in Microsoft's SQL Server product range (2005 onwards) for running dynamic SQL.

sp_executesql is a system stored procedure in Microsoft's SQL Server product range (2005 onwards).

It is used for running dynamic T-SQL Statements or batches, including support for embedded parameters.

It is also what the .NET framework uses for ad-hoc queries when parameters are added to a SqlCommand.

Articles

235 questions
5
votes
1 answer

Using EXEC() or SP_EXECUTESQL with SQL Common Table Expressions

How do I use EXEC(@SQL) or EXEC SP_EXECUTESQL(@SQL) with Common Table Expressions? Below does not work. WITH CTE_Customer (ID, Name) AS ( EXEC (@strSqlCommand) )
Deepfreezed
  • 567
  • 2
  • 10
  • 18
5
votes
3 answers

where is SP_EXECUTESQL defined?

The 'SP_' prefix by convention suggests a system stored procedure. But nowhere in my SQL Server instance could I find the sp_executesql stored procedure. I know it is there because it is used by one of my workplace's legacy code. But where is it?…
datps
  • 768
  • 1
  • 6
  • 16
5
votes
1 answer

named parameters in sp_executesql

is there any way that you can call sp_executesql with parameters that don't depend in the order they are defined in the store? the same query with exec works well, and if you have the same order it also works well, but it's a pain having to match…
Jokin
  • 4,188
  • 2
  • 31
  • 30
5
votes
2 answers

Dynamic Column Name in SQL in Update statement

DECLARE @sql NVARCHAR(max) DECLARE @ParmDefinition NVARCHAR(500) SET @sql = 'UPDATE [Table1] SET [Table1].[@columnName] = TEST'; SET @ParmDefinition = N'@columnName NVARCHAR(50)'; EXEC sp_executesql @sql, @ParmDefinition, @columnName =…
Chris Dargis
  • 5,891
  • 4
  • 39
  • 63
5
votes
2 answers

How do I truncate a table via linked server using a synonym for the table name?

I know I can do the following: EXEC Server_Name.DBName.sys.sp_executesql N'TRUNCATE TABLE dbo.table_name' But what if I want to use a synonym for the table? I'm on SERVER1 and I want to truncate a table on SERVER2 using a synonym for the table…
JJ.
  • 9,580
  • 37
  • 116
  • 189
4
votes
1 answer

Is there a speed difference between using EXEC sp_executesql and direct SQL

If it was a big query would it be faster on the second stored procedure ? CREATE PROCEDURE Customers_GetCustomer @CustId CHAR(5), @type int, @search nvarchar AS BEGIN DECLARE @SQL NVARCHAR(2000) SET @SQL = 'SELECT…
Frédéric Fect
  • 213
  • 3
  • 10
4
votes
3 answers

EXEC sp_executesql is really really slow when used with INSERT INTO :(

When I try and insert some results from an sp_executesql into a variable table, I get really bad perf. First, the query just as a simple Select. EXEC sp_executesql N'SELECT [a].[ListingId] FROM [dbo].[Listings] [a] LEFT OUTER JOIN …
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
4
votes
1 answer

Pass temp table to EXEC sp_executesql

How can I pass temp table (@table) to EXEC sp_executesql @query set @query = 'SELECT GsName, ' + @cols + ' from ( select GSName, [THour], NumOfTransactions from @table ) x pivot ( …
Ran
  • 65
  • 1
  • 7
4
votes
5 answers

sp_executesql Group by "must contain at least one column that is not an outer reference"

In a simplified version I'm trying to do something like that : Set @datepart = 'DATEPART(year, myDate)' Set @SQLQuery = 'SELECT @datepart AS TIME FROM someTable GROUP BY @datepart' Execute sp_executesql…
Kiechlus
  • 1,167
  • 12
  • 21
4
votes
2 answers

INSERT INTO temporary table from sp_executsql

Generally, I am bulding dynamic SQL statement that is executing using sp_executsql like this: EXEC sp_executesql @TempSQLStatement I need to insert the return result row set in something (table variable or temporary table), but I am getting the…
gotqn
  • 42,737
  • 46
  • 157
  • 243
4
votes
2 answers

Bulk insert fails as dynamic sql

I have this code in an SP that sets up a bulk insert: begin try declare @sentFile nvarchar(255) declare @bulk_cmd nvarchar(1000) = '' declare @loadDate nvarchar(8) = Convert(nvarchar(8),@p_loadDate) -- @p_loadDate is char(8) set…
RichJohnstone
  • 467
  • 1
  • 3
  • 10
3
votes
3 answers

Error Handling - Determine whether sp_executesql was the source

When an error message is returned by sp_executesql, is there any built-in error handling method/mechanism that can be used to identify that this error was returned by this procedure vs. directly from the containing script? In the case of the below,…
Ben Gribaudo
  • 5,057
  • 1
  • 40
  • 75
3
votes
2 answers

SQL SERVER sp_executesql incorrect syntax near ')'

I have this problem when I am trying to execute sp_ExecuteSql in sql server 2014 there is my stored procedure : alter proc search @Name nvarchar as declare @SQL nvarchar declare @Params nvarchar begin set @SQL = N' Select * from Table_1 ,…
mohammad.pro
  • 53
  • 5
3
votes
1 answer

Performance differences calling sp_executesql with dynamic SQL vs parameters

Given: CREATE PROCEDURE [dbo].[my_storedproc] @param1 int, @param2 varchar(100) AS <> GO Are there known performance differences between these different execution methods?: -- Method #1: declare @param1 int = 1 declare @param2…
tbone
  • 5,715
  • 20
  • 87
  • 134
3
votes
3 answers

sp_executesql with 'IN' statement

I am trying to use sp_executesql to prevent SQL injection in SQL 2005, I have a simple query like this: SELECT * from table WHERE RegionCode in ('X101', 'B202') However, when I use sp_executesql to execute the following, it doesn't return anything.…
userb00
  • 589
  • 1
  • 8
  • 16
1
2
3
…
15 16