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

How to store the result of dynamic SQL in a variable?

I have the following theoretical statement which I would like to implement using dynamic SQL (on SQL Server 2016) and store the single output value in the variable (@output). @numericvar, @columnname, @tablename should be input parameters. Any help…
Kanarinox
  • 23
  • 7
2
votes
1 answer

Complex sp_executesql - vulnerable to injection?

Just started a new job, and I have found an insanely complex call to sp_executesql (SQL Server 2008 R2). I can't decide whether it is vulnerable to SQL injection or not. The following is defined as a single C# string (so no additional…
Hyjrt6534
  • 21
  • 1
2
votes
3 answers

What is the advantage of using @ParmDefinition in sp_executesql

DECLARE @id int DECLARE @name nvarchar(20) SET @id = 5 SET @name = 'Paul' What is the difference between these two options: Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = ' + @id + ' AND NAME = ''' + @name + '''' Execute sp_Executesql…
Kiechlus
  • 1,167
  • 12
  • 21
2
votes
2 answers

How to use sp_executesql to avoid SQL Injection

In the below sample code, Table Name is an input parameter. In this case, how can I avoid SQL injection using sp_executesql. Below is the sample code, I am trying to use sp_executesqlto avoid it but it doesn't work. Can anyone tell me how to correct…
Suj
  • 416
  • 1
  • 5
  • 11
2
votes
2 answers

SQL Server : update records in dynamically generated tables using parameters in stored procedure

I have to create a stored procedure where I will pass tableName, columnName, id as parameters. The task is to select records from the passed table where columnName has passed id. If record is found update records with some fixed data. Also implement…
sarojanand
  • 607
  • 1
  • 11
  • 30
2
votes
2 answers

sp_executesql to update a temp table

I commonly use, and can easily find documentation on the internet for inserting into a temp table from an sp_executesql command: insert into #temp ( column1, column2, column3 ) exec sp_executesql @myQueryString However, what I am…
dthree
  • 19,847
  • 14
  • 77
  • 106
2
votes
1 answer

How do you use OPENQUERY within sp_executesql?

I'm trying to verify a job number exists on a linked server and get back a variable (@JobExists) indicating whether it does or not (1 for yes, 0 for no). To do this I'm trying to use OPENQUERY along with sp_executesql as I have to pass in a…
sr28
  • 4,728
  • 5
  • 36
  • 67
2
votes
1 answer

How to pass a table variable using sp_executesql

I'm trying to create a table using sp_executesql but I keep getting an error that says "Incorrect syntax near '@_TableName'. Any idea what I'm doing wrong here? Here's the code that I'm using: DECLARE @SQLString NVARCHAR(MAX), …
Zain Rizvi
  • 23,586
  • 22
  • 91
  • 133
2
votes
1 answer

Error "Declare Scalar" when Dynamic Exec Update inside two Cursor loops

Basically I'm looking to loop through a temp table which lists certain table names which need updated, I take each table name use it to populate another temporary table of all the ID's which are to be updated.. I can select the data in each table…
Gerry Kelly
  • 23
  • 4
2
votes
1 answer

EXEC sp_executesql with variable numbers of parameters

I am trying to implement a user customizable search and finally I got to the SQL part. The user can select a variable number of criteria and I send everything to the stored procedure in a table-valued parameter. I figured out the logic on how to…
Oana Marina
  • 277
  • 3
  • 19
2
votes
1 answer

Specify the parameter values part of sp_executeSQL as a parameter

I am using SQL Server 2008 R2. I need to use a parameter for the last part of T-SQL below. In this part, the values of parameters to sp_executesql are specified, but I want to supply it dynamically. EXECUTE sp_executesql @sql,N'@startIndex1…
Sunil
  • 20,653
  • 28
  • 112
  • 197
2
votes
0 answers

How to get Excel to reliably execute sp_executesql from a query table on a worksheet?

In MS Excel, if you create a QueryTable with Microsoft Query, and your SQL query cannot be visually presented by Microsoft Query, then you are not allowed to provide parameters for that query. Which is a shame, so there is this awesome technique…
GSerg
  • 76,472
  • 17
  • 159
  • 346
2
votes
1 answer

sp_executesql inside Try & Catch

Begin Try Declare @SQL NVarchar(Max)='Exec [MyLinkedServer].master.dbo.sp_executesql N''Drop Table [tempdb].dbo.[T1]'''; Print @SQL; Exec master.dbo.sp_executesql @SQL; End Try Begin Catch Print Error_Message() End Catch The above script fails…
Geri Reshef
  • 397
  • 1
  • 6
  • 17
2
votes
2 answers

Returning NULL data values while using an SP_ExecuteSQL

I'm using an sp_executesql so I can be flexible with what I choose to return. I'm encountering a problem when attempted to return data where there is none. Say for instance I'm attempting to find all rows where there is no owner assigned.…
user2191477
2
votes
1 answer

SP_ExecuteSQL with Parameters doesn't like Contains clause

I've been tasked with modifying a stored procedure so that it goes from looking like this: DECLARE @ID nvarchar(10) SET @ID = '0000000001' DECLARE @SQL nvarchar(200) SET @SQL = 'SELECT AppN FROM Apps WHERE CONTAINS(ID, ''"*'' + @ID +…
sparrow
  • 177
  • 2
  • 9
1 2 3
…
15 16