Insert a record from a query built in a text string

Question

Insert a record from a query built in a text string

Navigation

#1 by (1 votes)

2

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

Within the values I have a field of integer type, several texts and a date, apparently I am with some complications in the armed, suddenly I can give a light about 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 sql-server

asked by Juan Manuel Palacios 17.02.2016 в 18:15

source

1 answer

getDownloadUrl on Android returns me null Laravel - Auth :: attempt

score 1 · Accepted Answer

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

The use of this procedure for your insert query would be 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

This code has been checked in SQL Server 2014 , I suppose it will also work in previous versions