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

How do I execute a dynamic SQL with over 8000 Characters?

I'm trying to execute a stored procedure that merges SQL with MDX data by using this code: OPENROWSET('MSOLAP',..)-Function. I do this by building a string inside the stored procedure and executing it like this EXEC sp_executesql @sqlQuery When I…
Viktor Pagels
  • 271
  • 4
  • 15
0
votes
1 answer

sp_executesql with OUTPUT Parameter on SET statement

I want to use sp_executesql to set @email_body for sp_send_dbmail Why? Previously I was concatenating parameter directly to the @email_body which is prone to SQL Injection. like this SET @email_body = 'some html' + @id + 'some html' + @name + 'some…
Vidhi Shah
  • 23
  • 7
0
votes
0 answers

MS SQL problems with dynamic sql and sp_executesql with type

I am a newbie within tablevalued types in MS SQL. But until now I have searched a lot to find a solution. Maybe I don't know how to use them :-( I have a problem using them and the error says (translated into english) SqlVariantTable is not…
Michael Eriksen
  • 167
  • 1
  • 3
  • 13
0
votes
0 answers

VB.net parameterized query uses sp_executesql but loses UTF8 characters

I have an odd scenario that I'm looking for some clarification on. I currently have an VB.net application that is sending the parameterized query below to an SQL Server 2019 database using ADO.net. To make the database support UTF8, we are using…
dj.canuk
  • 13
  • 4
0
votes
2 answers

SSIS Execute SQL Task contain Insert Into and Select

I am attempting to read file names then input each name in a row with another similar cell value to another table column. Within the For Each Loop, then I have the Execute SQL Task. currently the Task is set to Direct Input. This is the problem…
Samuel
  • 7
  • 3
0
votes
2 answers

EXEC sp_executesql with a datetime parameter?

I'm using EXEC sp_executesql for a dynamic query in SQL Server 2016, and am stumbling on when a user wants to pass in a year. I have a datetime field called tkemdate and it is stored as a datetime field in SQL. Although SQL stores it as datetime,…
Susan T.
  • 21
  • 1
  • 8
0
votes
1 answer

EXEC sp_executesql will work with Integers but not VarChars

I'm using EXEC sp_executesql for a dynamic query in SQL Server 2017. I've tried various testing scenarios, and I can get results in my query (for other parameters) as long as the values passed in are Integers. So, that means, Location and…
Susan T.
  • 21
  • 1
  • 8
0
votes
1 answer

Select from another table in WHILE loop based on results

Essentially I'm trying to calculate the total amount of hours a user can have based on their shift and number of days entered. Example: Start shift on 13th September 2020. I know this is Week 1, Sunday (based on other calculations). So I need to…
Dereck
  • 703
  • 1
  • 7
  • 10
0
votes
1 answer

Execute dynamic query with IF-ELSE statements and sp_executesql command

I have created the following table using the SQL Server CREATE TABLE test ( id_number NVARCHAR(50) NOT NULL, number_of_products NVARCHAR(50) NOT NULL ); INSERT INTO test (id_number, number_of_products) VALUES (1000077004, 3), …
NikSp
  • 1,262
  • 2
  • 19
  • 42
0
votes
1 answer

SSIS OLE DB Command error: Could not deduce type for parameter in position '1' for remote call to module 'sp_executesql'

I have an OLE DB Command task in an SSIS package that received parameters, inserts them into a table in a linked-server, and returns the ID that was created on the linked server. When I run the query in SSMS it works, but within SSIS I get the error…
Ethan1701
  • 193
  • 1
  • 10
0
votes
2 answers

Pass multiple parameters using sp_executesql

I am using SQL Server 2016 to write a stored procedure with dynamic SQL and sp_executesql with multiple parameters. CREATE PROCEDURE [dbo].[testsp] @ProductName nvarchar(250), @ProductDescription nvarchar(250) AS DECLARE @query NVARCHAR(MAX) …
pbj
  • 663
  • 1
  • 8
  • 19
0
votes
1 answer

SQL feeding an EXEC stored procedure another stored procedure with parameters with apostrophes

I am following this guide to create a heatmap. http://sqljason.com/2012/03/heat-maps-for-ssrs-using-map-control.html One of the steps is to feed a stored procedure another stored procedure that generated my data. Mine looks something like…
nathan
  • 83
  • 7
0
votes
1 answer

Getting OUTPUT from Nested Dynamic SQL with a Remote Server

I am trying to execute some logic on a remote server using dynamic SQL, but when I set the dynamic SQL and try to call it nothing happens.. If I call the execution without making the execution dynamic it works without issue. Not sure if what I am…
iWROTEitANDdidITlive
  • 1
  • 2
0
votes
1 answer

result of sp_executesql in a variable

How can I get the output of the below query in a variable without temp table? DECLARE @Q1 NVARCHAR(300) DECLARE @Q2 NVARCHAR(300) DECLARE @Q3 NVARCHAR(300) SET @Q1 = 'SELECT ' +' ' + @formfieldpath SET @Q2 = 'FROM [TestDatabase].[details] WHERE id…
Andrew
  • 183
  • 2
  • 14
0
votes
2 answers

Conversion failed when converting the varchar value ' WHILE (' to data type int

I have the below transaction: DECLARE @strsql1 NVARCHAR(MAX); DECLARE @rows INT, @count INT; DECLARE @MySource NVARCHAR(30); DECLARE @Myfield NVARCHAR(30); SET @rows = 1; SET @count = 0; SELECT @strsql1 = 'WHILE ('+@rows+')> 0 BEGIN BEGIN…
Fatiso
  • 59
  • 8
1 2 3
…
15 16