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.

222 questions
45
votes
4 answers

EXEC sp_executesql with multiple parameters

How to pass the parameters to the EXEC sp_executesql statement correctly? This is what I have now, but i'm getting errors: alter PROCEDURE [dbo].[usp_getReceivedCases] -- Add the parameters for the stored procedure here @LabID int, …
Madam Zu Zu
  • 6,025
  • 16
  • 74
  • 122
41
votes
9 answers

Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

Was trying to select...into a temp Table #TempTable in sp_Executedsql. Not its successfully inserted or not but there Messages there written (359 row(s) affected) that mean successful inserted? Script below DECLARE @Sql NVARCHAR(MAX); SET @Sql =…
Worgon
  • 1,407
  • 4
  • 19
  • 27
29
votes
10 answers

Easy way to convert exec sp_executesql to a normal query?

When dealing with debugging queries using Profiler and SSMS, its pretty common for me to copy a query from Profiler and test them in SSMS. Because I use parameterized sql, my queries are all sent as exec sp_executesql queries. exec sp_executesql…
user1228
24
votes
2 answers

Entity Framework 4.2 exec sp_executesql does not use indexes (parameter sniffing)

I'm encountering some major performance problems with simple SQL queries generated by the Entity Framework (4.2) running against SQL Server 2008 R2. In some situations (but not all), EF uses the following syntax: exec sp_executesql…
mindlessgoods
  • 1,075
  • 1
  • 11
  • 20
23
votes
5 answers

Pass a TABLE variable to sp_executesql

I'm trying to pass a TABLE variable to the sp_executesql procedure: DECLARE @params NVARCHAR(MAX) SET @params = '@workingData TABLE ( col1 VARCHAR(20), col2 VARCHAR(50) )' EXEC sp_executesql @sql, @params, @workingData I get the…
Alex
  • 1,903
  • 3
  • 20
  • 26
13
votes
7 answers

Is it possible to issue CREATE statements using sp_executesql with parameters?

I'm trying to dynamically create triggers, but ran into a confusing issue around using sp_executesql and passing parameters into the dynamic SQL. The following simple test case works: DECLARE @tableName sysname = 'MyTable'; DECLARE @sql…
bdukes
  • 137,241
  • 21
  • 139
  • 173
12
votes
2 answers

Is COMMIT required after every EXECUTE IMMEDIATE?

I have multiple EXECUTE IMMEDIATE commands within one oracle procedure. EXECUTE IMMEDIATE 'DELETE FROM tbl1'; EXECUTE IMMEDIATE 'INSERT INTO tbl1...'; COMMIT; EXECUTE IMMEDIATE 'DELETE FROM tbl3'; EXECUTE IMMEDIATE 'INSERT INTO tbl3 ...';…
Angelina
  • 1,963
  • 9
  • 31
  • 66
12
votes
2 answers

Workaround for calling table-valued function remotely in SQL Server has even more issues

I had a query with a set of parameters that needed to be run multiple times with different parameters, so I wrapped it in a table-valued function. That table valued function needed called from a remote server. Unfortunately, the call fails on the…
Triynko
  • 17,370
  • 20
  • 92
  • 154
11
votes
1 answer

Non-optimal execution plan using sp_executesql

I'm having problems with slow performance in a sql select statement with some parameters, for the same query, executing this select using sp_executesql way it takes double time that the inline-way. The problem is that in sp_execute-way sql server is…
Miguel A. Luna
  • 143
  • 8
10
votes
2 answers

Permissions when using "Execute sp_Executesql"

I have a database where all access is controlled by stored procedures. The DBA would like to avoid giving users direct read/write access to the underlying tables, which I can understand. Hence all updating and selecting of data is done via stored…
webworm
  • 9,413
  • 28
  • 96
  • 186
9
votes
3 answers

sp_executesql and table output

I'm writing a stored procedure in SQL Server 2005, at given point I need to execute another stored procedure. This invocation is dynamic, and so i've used sp_executesql command as usual: DECLARE @DBName varchar(255) DECLARE @q varchar(max) DECLARE…
BAD_SEED
  • 4,210
  • 9
  • 42
  • 97
7
votes
2 answers

Why would the exact same SQL query result with a different execution plan when executed via the sp_executeSQL procedure?

As the title states, I don't understand why the sp_executeSQL would generate a completely different execution plan than running the query from Sql Management Studio. My query in question will take 3 seconds when run from SQL management Studio, where…
Nathan Tregillus
  • 5,248
  • 2
  • 43
  • 73
7
votes
1 answer

Using LIKE in sp_executesql

SET @whereCond = @whereCond + ' AND name LIKE ''%'' + @name + ''%''' Is there something wrong here? After I generate where condition, I execute it with sp_executesql, but I did get anything. When I SELECT the same thing without sp, it's ok. How to…
hgulyan
  • 7,639
  • 6
  • 43
  • 72
6
votes
1 answer

Passing user defined table parameter to dynamic sql, sp_executesql

I need help with passing my "user defined table type" parameter to dynamic sql, sp_executesql. Here's my sample code: DECLARE @str as nvarchar(Max) DECLARE @IDLIST AS ListBigintType /* this is my table type, with ItemId column (bigint)*/ INSERT…
rockin'
  • 699
  • 3
  • 7
  • 16
6
votes
2 answers

sp_executesql or exec(@var) is too long. Maximum length is 8000

I have large queries so i cant use linked server in production by rules. i pass a varchar(max) which this has more than 8000 characters. but sp_executesql does not support more than 8000 characters then how can i execute my string?
angel
  • 3,986
  • 10
  • 52
  • 82
1
2 3
…
14 15