logo
down
shadow

T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql


T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql

By : Savannah Smith
Date : November 20 2020, 03:01 PM
should help you out Here is a fully functional example of how you can do this. Notice this is using a parameterized where clause and quotename around the column name in the dynamic sql to prevent sql injection.
code :
if OBJECT_ID('tempdb..#Agents') is not null
    drop table #Agents

create table  #Agents 
(
    AgentNumber char(10)
    , Level1Agent char(10)
    , Level2Agent char(10)
    , Level3Agent char(10)
    , Level4Agent char(10)
    , Level5Agent char(10)
)

insert #Agents
select '1122334455', '1122334499', '1122334488', '1122334477', '1122334466', '1122334455'

DECLARE @level INT = 3;
DECLARE @agent CHAR(10) = 1122334455;
DECLARE @colname NVARCHAR(11) = CONCAT('Level',@level,'Agent');

declare @agentout char(10)

DECLARE @qry NVARCHAR(300) = 'SELECT @agent_out = ' + quotename(@colname) + ' FROM #Agents WHERE AgentNumber = @agentin';

EXECUTE sp_executesql @qry, N'@agentin char(10), @agent_out char(10) output', @agentin = @agent, @agent_out = @agentout output

select @agentout


Share : facebook icon twitter icon
Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)


By : Sam
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , sp_executesql is more likely to promote query plan reuse. When using sp_executesql, parameters are explicitly identified in the calling signature. This excellent article descibes this process.
The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".
Different results from executing a stored procedure using exec sp_executesql compared to EXEC

Different results from executing a stored procedure using exec sp_executesql compared to EXEC


By : Karthi Keyan
Date : March 29 2020, 07:55 AM
hop of those help? Numbers that are this different make me think that maybe it's using the default values for something? Have you taken out the defaults?
Maybe you could also add the parameters to the select, to make sure you get what you're expecting.
code :
SELECT top 40 
    @searchLatitude,
    @searchLongitude,
    l.Id as Id,
    l.Postcode as Postcode,
    l.latitude as Latitude,
    l.longitude as Longitude,
Regarding EXEC, EXECUTE, SP_EXECUTE and SP_EXECUTESQL

Regarding EXEC, EXECUTE, SP_EXECUTE and SP_EXECUTESQL


By : حسین محمدی
Date : March 29 2020, 07:55 AM
I wish this helpful for you sp_execute is depreciated and is used by OLEDB.
EXEC and EXECUTE are the same thing, one is just shorthand
How can we use EXEC sp_executesql for two dynamic SQL statements?

How can we use EXEC sp_executesql for two dynamic SQL statements?


By : Ashley Brown
Date : March 29 2020, 07:55 AM
this will help tables: , Did you mean:
Why NVarchar(MAX) cannot execute complete dynamic query using SP_ExecuteSQL?

Why NVarchar(MAX) cannot execute complete dynamic query using SP_ExecuteSQL?


By : Portran
Date : March 29 2020, 07:55 AM
will help you Your query definitely exceed the 4000 characters.
change the SET statement to
code :
SET @UpdateRec = CONVERT(NVARCHAR(MAX), '') + N' IF(('
Related Posts Related Posts :
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org