CODEWITHSUNDEEP
sql
M.C.Rohith
M.C.Rohith

Reputation: 3750

What is the difference between the parameter containing the database column and a direct string containing parameter?

Need the solution to solve taking string from the database and replacing the parameter.

Here are the sample queries I used to understand more:

--Create Table Table1
--(
--Id int primary key identity(1,1),
--Data varchar(max)
--)

--Create Table Table2
--(
--Id int primary key identity(1,1),
--SampleData varchar(max)
--)

Delete From Table1
GO

Delete From Table2
Go

insert into Table2 (SampleData) values ('How r u ''+@i+''. ')

Declare @i int = 100
Declare @TempSampleData varchar(max) = NULL

While(@i > 0)
begin
    Set @TempSampleData = (select SampleData from Table2)
    insert into Table1 (Data ) values (@TempSampleData)
    insert into Table1 (Data) values ('How r u '+Cast(@i as varchar(500))+'.')
    set @i = @i - 1
End

--Output

Id  Data
103 How r u '+@i+'. --Needed output here is How r u 100.
104 How r u 100.
105 How r u '+@i+'. 
106 How r u 99.
107 How r u '+@i+'. 
108 How r u 98.
109 How r u '+@i+'. 
110 How r u 97.
111 How r u '+@i+'.

Upvotes: 0

Views: 121

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753805

The first INSERT places a constant string in the table:

insert into Table2 (SampleData) values ('How r u ''+@i+''. ')

There happens to be an @ in it, and a couple of single quotes (each represented by two single quotes), but the string is just:

How r u '+@i'.

When you later do:

Set @TempSampleData = (select SampleData from Table2)
insert into Table1 (Data ) values (@TempSampleData)

This takes a direct copy of the value from Table2 and copies it into Table1. There is no evaluation done on the string; there shouldn't be any evaluation done on the string. I'm not even sure if there is a way to get the string evaluated - and it would probably need a lot more quotes to produce a valid value if it was possible.

The other INSERT operation is different:

insert into Table1 (Data) values ('How r u '+Cast(@i as varchar(500))+'.')

Here, the string to be inserted is evaluated as the statement is executed. It converts the current value of the variable i to a string and concatenates that with two other string fragments.

Upvotes: 0

Justin Pihony
Justin Pihony

Reputation: 67075

How about something like this in your loop:

INSERT INTO Table1 (Data)
SELECT REPLACE(SampleData, '@i', CAST(@i AS VARCHAR(500)))
FROM Table2

Upvotes: 2

Related Questions