Home / Questions / 4344
Accepted
Juan Manuel Palacios
Asked: 2020-02-18 09:15:10 +0800 CST 2020-02-18 09:15:10 +0800 CST

Insert a record from a query built on a text string

  • 772

Good afternoon, I am trying to insert records from a Cursor into a table in SQL but building the Insert statement in a text type variable.

Within the values ​​I have an integer type field, several texts and a date, apparently I am having some complications in the assembly, suddenly they can shed some light on it.

What I want to build is something like this:

set @queryTexto = 'Insert into <<tabla>> (<<lista de campos>>) 
    Values (@campoNumerico, @campo1, @campo2, @campo3, ''ACT'', @campoFecha)'
exec @queryTexto

I have seen in some examples that you have to convert values, I have also seen the use of CHAR(39). What recommendations could you give me? Thank you very much.

sql

1 Answers

  1. Best Answer

    The best way to do it would be that when executing the text query you will pass the typed parameters to it. You can do this using the stored procedure (this same procedure is often used by data access layers like ADO.NET when they launch parameterized queries)sp_executesql

    Using this procedure for your insert query would look like this:

    declare @queryTexto nvarchar(MAX), @Now datetime

set @queryTexto = 'insert into TestDynamicInsert 
    (IntegerField, DateField, StringField) 
    values (@p1, @p2, @p3)'

SET @Now = getdate()

exec sp_executesql @queryTexto, N'@p1 int,@p2 datetime,@p3 varchar(128)',
     @p1=1, @p2=@Now, @p3='Texto de prueba'

    The function getdate()to obtain the current date does not work executing it in the call to the stored procedure, so it must be saved in a variable

    I have checked this code in SQL Server 2014 , I guess it will work in previous versions as well

    • 2