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
0
votes
1 answer

Rounding issue with sp_executesql on dynamic select clause with calculation

I am facing a problem with output from sp_executesql in a stored procedure in SQL Server. Below is the part of the stored procedure: DECLARE @RET_Amt as NUMERIC(18,2) DECLARE @v_Select nvarchar(200) SET @v_select = 'SELECT …
Rakesh
  • 9
  • 1
0
votes
2 answers

SQL query behaving differently when run with sp_executesql

I have a query in SQL Server 2012 that I first run without using sp_executesql followed by running it using sp_executesql. The strange part is that both these runs give different results, when they should be the same since they are using the same…
Sunil
  • 20,653
  • 28
  • 112
  • 197
0
votes
0 answers

How to execute dynamic stored procedure in sql server with boolean return value?

I have a stored procedure that has boolean result parameter as output. But my project needs to use 3 databases. Basically, there is a main database and 2 others. The other databases using same stored procedure but they just depend on params. If I…
Abdullah Ilgaz
  • 719
  • 1
  • 17
  • 39
0
votes
0 answers

SQL Server cursor, EXEC, and SP_executesql

I have a cursor that does this in it's loop: EXEC dbo.[ProcessHostVendorItems] @ItemId = @ItemId, @VendorNo =... etc Would it be any benefit (likely via caching query plans?) to change this to: DECLARE @SQL = "dbo.[ProcessHostVendorItems] @ItemId =…
Beau D'Amore
  • 3,174
  • 5
  • 24
  • 56
0
votes
0 answers

sp_executesql not returning results in the same way as normal query

I saw in SQL when executing a query using sp_executesql it takes MINUTES to process it, while i run the same query in a normal way declaring the parameters runs in 2 seconds. sp_executesql EXEC Sp_executesql N'SELECT TOP (27)…
Sin
  • 1,836
  • 2
  • 17
  • 24
0
votes
1 answer

How to get return value from EXEC sp_executesql

How do I get the return value from EXEC sp_executesql @OpenQry so I can check if the value exists in IF EXISTS? DECLARE @TableName VARCHAR(25) DECLARE @TD_QUERY NVARCHAR(MAX) DECLARE CUR_QRY CURSOR FOR SELECT TABLENAME FROM dbo.tbl_table OPEN…
angelcake
  • 119
  • 5
  • 7
  • 18
0
votes
1 answer

SP_ExecuteSQL Generic stored_procedure call without output parameters, but catching the output

I'm scratching my head hard on this pb and I would like some help to figure out some solution. Inside some TSQL programmable object (a stored procedure or a query in Management Studio) I have a parameter containing the name of a stored procedure +…
Big Ben Magnus
  • 3
  • 1
0
votes
1 answer

how can I concatenate looped sp_executesql output into a variable

I using the procedure below, I am trying to concatenate the returned itemstr to the variable strlist with commas between the values. I get the individual itemstr values with each iteration of the look, but strlist is always null. alter procedure…
Dean-O
  • 1,143
  • 4
  • 18
  • 35
0
votes
2 answers

sp_executesql date "Conversion failed when converting date and/or time from character string" in specific case

I have something strange: Declare @SQLQuery As nvarchar(Max) Declare @maxdat date Declare @dateColumn nvarchar(10) Set @maxdat = GETDATE() Set @dateColumn = 'ERDAT' Set @SQLQuery = 'SELECT * FROM myTable WHERE @dateColumn <= @maxdat' Execute…
Kiechlus
  • 1,167
  • 12
  • 21
0
votes
2 answers

SQL Server sp_executesql timeout

The following query (without details) ends up with timeout. All columns have nvarchar type. So it is not about this. Query works perfectly without sp_executesql. exec sp_executesql N'declare @exp nvarchar(max) = ''%'' + @name + ''%'' select…
Dmitriy Dokshin
  • 710
  • 5
  • 25
0
votes
1 answer

Dynamic Query does not work

I am trying to do a dynamic query passing to it parameters, but it doesn't work: DECLARE @ASA_EXPORT_TABLE NVARCHAR(MAX) = 'EXPORT_PRODUCT' DECLARE @ASADB_NAME NVARCHAR (MAX) = 'WHITESTUFF_WSSI_7700_ASA' DECLARE @TOP_1 NVARCHAR(MAX) SET @TOP_1 = ' …
Giuseppe Lolli
  • 167
  • 4
  • 15
0
votes
2 answers

How to get sp_executesql to accept parameter values that are not nvarchar

I'm trying to build a dynamic SQL statement that is within a stored procedure. Here is a simplified version of it: CREATE PROC dbo.GetOrders @UserID INT = 2 AS DECLARE @SQLString NVARCHAR(MAX) SET @SQLString = N'( …
volume one
  • 6,800
  • 13
  • 67
  • 146
0
votes
0 answers

SQL Server return table-valued variable from sp_executesql

I need to INSERT into a table-valued variable several rows that come from a dynamically generated SELECT statement, that is INSERT INTO ... SELECT ... I'm trying to do it via sp_executesql. How to return this filled table-valued variable from the…
igorjrr
  • 790
  • 1
  • 11
  • 22
0
votes
3 answers

FileMaker 12 and ExecuteSQL

I have this that works: ExecuteSQL ( "Select email_Full from staffing where Branch = ? and EmployeeID = ? and Title= ?"; " "; " "; "001" ; "33748"; "Supv V" ) but I need the first parameter to be within the range of 001-300; the second parameter…
Jim Shelton
  • 1
0
votes
1 answer

How to pass comma separated list in sp_executesql

I want to use IN opeartor in sp_executesql, but facing the error that Incorrect syntax near '@TagIndexListToAdjust'. This error is due to single quotes at both side of the parameter value '(1,2,3)'. I need to fix it with in only the sp_executesql…
Surender Oati
  • 1
1 2 3
…
15 16