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
1
vote
2 answers

How to store a multiple or a list of values returned from sp_executesql?

UPDATE : This is what I did - set @dyn_sql = ' select @UserName=UserName from ( select …
Vishal
  • 12,133
  • 17
  • 82
  • 128
1
vote
1 answer

sp_executesql does not validate the query string

Today I faced the worst situation. There is a stored procedure in database, I had to alter it, using inline query, Everything went fine by executing this query: DECLARE @script AS NVARCHAR(MAX) SET @script = 'ALTER PROCEDURE GetALL …
DareDevil
  • 5,249
  • 6
  • 50
  • 88
1
vote
1 answer

sp_executesql not setting a variable correctly in a dynamic sql query in SQL Server 2012

In query below, I am trying to set the value of @productsExist using a dynamic query that is executed by sp_executesql in SQL Server 2012. The problem is that even though the table @tableName exists and contains records, the value of productsExist…
Sunil
  • 20,653
  • 28
  • 112
  • 197
1
vote
1 answer

Incorrect Syntax Near '0' - when using sp_executeSQL to execute stored procedure with output parameter

Actual Stored Procedure is ALTER Procedure [dbo].[ApplyList] @oldlist int, @username varchar(50), @newlist int, @errormessage varchar(2000) output AS BEGIN SET NOCOUNT ON; SET @errormessage = ''; END Here is the SQL code generated…
manu97
  • 7,237
  • 2
  • 17
  • 21
1
vote
2 answers

Is It possible to add same constraint to multiple tables?

I have generated some code to do this in multiple tables. Error I get doing this is: Variables are not allowed in the ALTER TABLE statement. I understand that. The code I've generated : DECLARE @tableName VARCHAR(50) = 'myTable', @sql…
Carlos Andres
  • 53
  • 6
1
vote
1 answer

Get multiple results from sp_executesql

Please have a look at the following statement: CREATE TABLE TableA (A INT) CREATE TABLE TableB (B INT, C INT) DECLARE @stmt NVARCHAR(MAX) = 'SELECT * FROM TableA; SELECT * FROM TableB' EXEC sp_executesql @stmt The statement generates an output of…
Andreas
  • 1,997
  • 21
  • 35
1
vote
1 answer

Passing parameters into sp_executesql

I have this simple dynamic SQL code: declare @cmd nvarchar(100) declare @paramDef nvarchar(100) = N'@p nvarchar(20)' declare @p nvarchar(10) = N'SubTotal' set @cmd = N'select @p from Sales.SalesOrderHeader' exec sp_executesql @cmd, @paramDef,…
LynnXe
  • 57
  • 7
1
vote
1 answer

SQL server 2012 - return variable value from a dynamic script

I'm trying to run a dynamic script to then return the variable so I can pass in to the rest of my script. I've a couple of ways with the help of Google, but I think I still haven't got the syntax correct, therefore getting error or null value…
HL8
  • 1,369
  • 17
  • 35
  • 49
1
vote
1 answer

Why is exec sp_executesql much slower than inline sql?

I have test this query in management studio and this execute very fast(less than a second) declare @p_Message_0 varchar(3) = 'whh' declare @p_CreatedTime_0 datetime = '2015-06-01' SELECT count(1) FROM (SELECT * FROM [Logs](nolock) WHERE…
Chocoboboy
  • 100
  • 8
1
vote
1 answer

sp_executesql not showing desired result

I am working on SQL in SQL Server and come across this problem where, set @sqlString=N'select @max=MAX('+@columnName+') from @temp'; exec sp_executesql @sqlString, N'@temp as Table_Type readonly, @max nvarchar(max)', @temp ,@max; I…
Sarthak
  • 51
  • 3
  • 10
1
vote
2 answers

TSQL sp_executesql

I have a simple table: CREATE TABLE [dbo].[test] ( [eins] [varchar](50) NOT NULL, [zwei] [varchar](50) NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([eins] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =…
Oliver Neis
  • 11
  • 1
1
vote
3 answers

tsql - sp_executesql difference

I have a problem about the way of my orm tool execute procedures. This is how i execute: exec PostViewProc @Id='18767F6A-FF7A-47E9-AF09-6DB8A2F3B20E',@AuthorId='5455D9B9-B25A-41BD-BD2C-C9CBAE87D629' Returns 1 row as expected.. And this is how the…
nick cubric
  • 193
  • 1
  • 9
1
vote
0 answers

sp_executesql select * is much faster than select column fields?

I encounter an issue where when I use sp_executesql to select * (6 seconds) is much faster than select column fields (2min). (using sql server 2005) I can't understand why the performance is so much different as I thought select by column field…
Eddie
  • 11
  • 1
1
vote
0 answers

How get average recording time to the database by javascript

I can get time of one operation insert to database, but I have problem to get time for 1000 operations. I get end time in the function to call when the request is sent successfully. var startTime = 0; var endTime = 0; function func(){ startTime =…
user2889383
  • 89
  • 1
  • 2
  • 8
1
vote
4 answers

how to convert row value from sql server to string in c#

I have a method like this private string AccountCreation() { string accountId=""; using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand(); …
sindhu jampani
  • 504
  • 3
  • 10
  • 30
1 2 3
…
15 16