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
2
votes
2 answers

DynamicSQL using sp_executesql Error

I keep getting an error with the following stored procedure. I had it working correctly using EXEC, then I switched to sp_executesql and I haven't been able to get it to execute. I keep getting the following error: Incorrect syntax near…
Sizzle
  • 47
  • 6
2
votes
2 answers

Execute a SQL String and insert result into table

I have a table DECLARE @Results TABLE(QueryIndex smallint, FieldValue nvarchar(50)) QueryIndex is a loop counter, it will get value from @QueryIndex. FieldValue will get value from another SQL String. For some reason, I need to execute SQL string…
Shinigamae
  • 854
  • 3
  • 18
  • 35
1
vote
2 answers

How can I use SQL BETWEEN to compare two dates in a string to execute to sp_executesql?

I have a line in my SQL Stored Procedure that looks like this (works as intended): HAVING oh.startdate BETWEEN @startDate AND @endDate However, further down I have the line: AND (oh.user IN (@userIDs)) Where @userIDs is a comma delimited string…
tsdexter
  • 2,911
  • 4
  • 36
  • 59
1
vote
1 answer

Fast way to explicitly substitute parameters in an sp_executesql statement?

I am writing a program in C# that runs some select statements using parameters passed to sp_executesql. One issue that I'm running into when testing is that, whether I get the the commands executed from SQL Profiler or from a watch in Visual Studio,…
user1202747
  • 515
  • 4
  • 16
1
vote
4 answers

Execute very long statements in TSQL using sp_executesql

I would like to execute dynamic SQL statements which are about 10,000 characters. When I use sp_executesql as below: DECLARE @stmt varchar(MAX) SET @stmt = 'xxxxxxxx.................' which is about 10,000 characters EXEC sp_executesql @stmt I…
TTCG
  • 8,805
  • 31
  • 93
  • 141
1
vote
2 answers

sp_executesql creating issue when passing a data table from C# to SQL Server

I have been trying to send a DataTable from C# to SQL Server. We have narrowed down the problem to execution of code in SQL Server. Below is the setup for the testing code developed. DROP PROCEDURE [dbo].[yy_StoredProc] GO DROP TYPE…
LuckeyKays22
  • 13
  • 2
1
vote
2 answers

sql server sp_executesql error with sql string

I have a string that looks like this: set @sqlstring = N'select @mindate = min(time), @maxdate = max(time) from ' + @st_churn_active_table; I print it and it looks like this: select @mindate = min(time), @maxdate = max(time) from…
suhprano
  • 1,723
  • 1
  • 16
  • 22
1
vote
1 answer

Search/delete a specific string in a defined column of all tables of all databases

I am looking for a way to search or delete a certain string in a certain column of all tables of all databases of a server. The name of the column in which to search for the specified string starts as CNUM (i.e. columns CNUML, CNUMX, CNUM...) In…
storm97
  • 43
  • 7
1
vote
1 answer

sp_executesql reports: Incorrect syntax near @sequenceName

My requirement is to retrieve into a variable, the next value for a sequence, the name of which is derived from a string and a value. When I run the following as a test using exec sp_executesql ... an error is reported: Incorrect syntax near…
Basil Bear
  • 433
  • 3
  • 15
1
vote
1 answer

Insert results of linked server into temp table from SQL Server 2016 to SQL Server 2019

SQL Server 2016 version: Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) SQL Server…
Danielle Paquette-Harvey
  • 1,691
  • 1
  • 16
  • 31
1
vote
0 answers

Table name as variable in sql server

I am performing stored procedures the input option is as follows: Declare @table varchar (20) Set @query = 'Select * from' + @table + 'Where id = 1' Exec sp_executesql @query The question is can it be done in some other way without the SQL being…
Caleb
  • 11
  • 3
1
vote
1 answer

Dynamically Executing Stored Proc with output variables

I have a script that calls a stored procedure on a different SQL Server,using a Linked Server reference and an OPENQUERY() statement. For demonstration, I have made the following procedure, on the remote server: -- ON SQL_SVR Remote Server CREATE…
High Plains Grifter
  • 1,357
  • 1
  • 12
  • 36
1
vote
0 answers

Trouble Getting Output Parameter from sp_executesql with input Parameters

I am inserting a row into a remote MS SQL server and want to to retrieve the inserted row ID. I am using sp_executesql and passing in input parameters. The row is inserting but the OUTPUT parameter is null. Any idea why output parameter in NULL:…
VIH
  • 11
  • 1
1
vote
2 answers

How do I query a value dynamically in T-SQL?

For whatever reason, I can't seem to get a value out dynamically from SQL. declare @SQL nvarchar(max) declare @FieldName nvarchar(255) declare @FieldValue nvarchar(max) select @SQL = 'SELECT TOP 1 ' + @fieldname +' FROM MyTable WHERE…
user724198
1
vote
1 answer

Dynamic SQL get one value from custom @Column into result variable

I have been trying to accomplish this and nothing I searched on the topic so far worked. I have the following code inside a stored procedure: DECLARE @sql NVARCHAR(500) DECLARE @qty money SET @sql = N'SELECT TOP 1 @qty = @QuantityColumnName FROM…
Elena O
  • 33
  • 5
1 2 3
…
15 16