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 to write stored procedure passing select statement as input parameter?

I have a stored procedure that outputs a select statement as you see below: select case when count(*) > 10 then ''A1'' else ''B1'' end as id, name, address from database.dbo.student Now I want to write a stored procedure that takes such a select…
Ayanabh Chakraborty
  • 31
  • 4
0
votes
2 answers

EXEC sp_executesql is slow

I have a stored procedure like this: declare @where nvarchar(max) set @where=' where 1=1 ' if(@IsSimNha is not null) set @where+=' and s.IsSimNha=@IsSimNha' if(@IsSearchSimPSC is not null) set @where+=' and…
Dan LE
  • 27
  • 1
  • 7
0
votes
1 answer

How to use sp_execute passing table parameter

I need to pass a table var to sp_executesql statement. Do you know how can I pass the table variable to sp_executesql? Here is how I pass the regular variable (not table variable) to sp_executesql EXEC sp_executesql @statement, N'@Status…
Prime
  • 69
  • 2
  • 12
0
votes
2 answers

Function that removes characters that can cause SQL injection

I need to use dynamic SQL in a stored procedure. That dynamic SQL will create SQL object, therefore I cannot parameterize it and execute it with sp_executesql. Is there some SQL function which will check the stored procedure parameter variable and…
Muflix
  • 6,192
  • 17
  • 77
  • 153
0
votes
1 answer

sp_executesql throws error but simple EXEC() works

I need to create a user defined type in dynamic way but The exec() way: -- This works DECLARE @column NVARCHAR(MAX) = 'Id INT' EXEC ('CREATE TYPE dbo.MyDataType AS TABLE ('+ @column +')') The sp_executesql way: -- This does not work DECLARE…
Muflix
  • 6,192
  • 17
  • 77
  • 153
0
votes
1 answer

sp_execute for multiple dynamic T-SQL statements ak a batch

Ideally I'd like to execute several sql statements as a single exec sp_executesql statement. An example would be where I use one IF EXISTS to determine if a second statement is run: drop proc ai_ImportDataAddListPosn go create proc…
user2711068
  • 3
  • 1
  • 6
0
votes
1 answer

Using a variable for a table in a SELECT statement which is in a IF statement

I already searched for a solution, but the answers weren't specific enough to solve my problem. So I have this Statement: IF NOT EXISTS (SELECT InternalRepositoryId FROM Tfs_GitInterface.dbo.tbl_GitRepository WHERE Name = @repositoryName) BEGIN …
Jordan Zapf
  • 65
  • 1
  • 11
0
votes
1 answer

Stored procedure fails in Execute SQL task, but succeeds manually

I'm developing and testing an SSIS package in SQL 2012 to import the rows from a text file into a staging table(that is truncated before each execution) using a Script Task. Then an Execute SQL task is used to run a stored procedure, which then…
Pete Gossett
  • 21
  • 5
0
votes
3 answers

For dynamic StoredProcedure with custom user defined datatype error: Must declare the scalar variable "@myTableType2"

I have to insert bulk data into database and table is also created dynamically. First have created UserDefined DataTypes -- This is my user defined type CREATE TYPE [dbo].[Custom_block] AS TABLE( [Name] [nvarchar](200) NULL, [population]…
Satinder singh
  • 10,100
  • 16
  • 60
  • 102
0
votes
0 answers

sp_executesql from asp classic page

DECLARE @B INT, @Definition NVARCHAR(MAX) SELECT @Definition = N'SELECT @B = ' + OBJECT_DEFINITION (default_object_id) from sys.columns where name='COLUMN' AND OBJECT_ID= OBJECT_ID('TABLENAME') EXEC SYS.SP_EXECUTESQL @Definition,…
Drafter
  • 13
  • 4
0
votes
1 answer

How do I use text values as variables in dynamic SQL using sp_executesql

I have the following table: CREATE TABLE Wages (EmpID INT, Amount MONEY) INSERT INTO Wages VALUES (1,25000), (2,30000), (3,35000), (4, 40000) SELECT * FROM Wages I want to create a procedure that uses sp_executesql where I can change, not only the…
SteelyDanFan
  • 79
  • 4
  • 12
0
votes
1 answer

Dynamic SQL parameter error, Incorrect syntax near '@myparametername'

I'm building a fun stored procedure that will use dynamic SQL, sp_executesql with parameters, to allow some alter statements for a column in all database tables if the column name exists ( As you can see I used a cursor for loop all the tables on…
Eduardo Chávez
  • 48
  • 7
0
votes
1 answer

Dynamic Query inside TSQL function not able to do

I tried to make sql server function that can generate query based on tablename. However, i am getting this error: Msg 557, Level 16, State 2, Line 1 Only functions and some extended stored procedures can be executed from within a function. Code…
Cookie
  • 25
  • 1
  • 10
0
votes
0 answers

Adding file to filegroup with Dynamic SQL

hope you can help me, I'm trying to add a file with the name of the corresponding filegroup, here's my code: DECLARE @SQL1 nvarchar(1024), @SQL2 nvarchar(1024), @name varchar(255); SET @SQL1 = 'DECLARE vend_cursor CURSOR GLOBAL …
Yeinor
  • 21
  • 3
  • 7
0
votes
3 answers

Adding Quoted Datetimes from Paramaters in Dynamic SQL String for sp_executesql

I am currently working on an SQL Server 2005, and trying to structure a dynamic query as follows: DECLARE @GETDATE AS NVARCHAR(12); DECLARE @GETDATE2 AS NVARCHAR(12); SET @GETDATE = ...; SET @GETDATE2 = ...; SET @SQL = 'CREATE TABLE [dbo].['…
3BK
  • 1,338
  • 1
  • 8
  • 11
1 2 3
…
15 16